Copy from closed workbook

  • Thread starter Thread starter Gaurav
  • Start date Start date
G

Gaurav

I have used a macro which first clear the contents from the current
sheet, thereafter opens another workbook, copies data from that file
and paste into active workbook-

Sub Macro()
Dim currentWorkbook As Workbook
Dim wbcopy As Workbook
Dim CalcMode As Long
Dim SaveDriveDir As String, MyPath As String
Dim fn As Variant

' Set various application properties.
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With

Set currentWorkbook = ActiveWorkbook
Cells.Select
Selection.ClearContents
Range("A1").Select

SaveDriveDir = CurDir
MyPath = "C:\"
ChDrive MyPath
ChDir MyPath
fn = Application.GetOpenFilename("Excel-files,*.xls", _
1, "Select One File To Open", , False)

Set wbcopy = Workbooks.Open(fn)
Range("A1:Z200").Select
Selection.Copy
currentWorkbook.Activate
ActiveSheet.Paste
wbcopy.Close , False

ChDrive SaveDriveDir
ChDir SaveDriveDir
' Restore the application properties.

With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = CalcMode
End With

End Sub

But, currently, it paste only the values and not the formats. Can
anyone please help me with pasting the formats along with the values.
Secondly, it also shows a message that large amount of information is
placed on the clipboard.
How to stop that message from appearing?

Can anyone please help me with this?

Thanks,
Gaurav
 
Hi,

Try either of these:

Application.DisplayAlerts = False
Application.CutCopyMode = False

DisplayAlerts is usually put befor the command that will trigger the alert
and then
Application.DisplayAlerts = True
after it.

The CutCopyMode=False would be put before you close the file.

Try the PasteSpecial command to make sure the formats come along.
 
I have used a macro which first clear the contents from the current
sheet, thereafter opens another workbook, copies data from that file
and paste into active workbook-

Sub Macro()
Dim currentWorkbook As Workbook
Dim wbcopy As Workbook
Dim CalcMode As Long
Dim SaveDriveDir As String, MyPath As String
Dim fn As Variant

  ' Set various application properties.
    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .EnableEvents = False
    End With

Set currentWorkbook = ActiveWorkbook
    Cells.Select
    Selection.ClearContents
    Range("A1").Select

    SaveDriveDir = CurDir
    MyPath = "C:\"
    ChDrive MyPath
    ChDir MyPath
    fn = Application.GetOpenFilename("Excel-files,*.xls", _
        1, "Select One File To Open", , False)

   Set wbcopy = Workbooks.Open(fn)
    Range("A1:Z200").Select
    Selection.Copy
    currentWorkbook.Activate
    ActiveSheet.Paste
    wbcopy.Close , False

    ChDrive SaveDriveDir
    ChDir SaveDriveDir
   ' Restore the application properties.

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = CalcMode
    End With

End Sub

But, currently, it paste only the values and not the formats. Can
anyone please help me with pasting the formats along with the values.
Secondly, it also shows a message that large amount of information is
placed on the clipboard.
How to stop that message from appearing?

Can anyone please help me with this?

Thanks,
Gaurav
Click to expand...

Hi Gaurav,

Best way to pick up the code for this is to use the Macro recorder
whilst using Edit > Paste Special to paste in the formats and the
values.

Anthony
http://www.excel-ant.co.uk
 
Back
Top