VBA How to copy data in a closed workbook?

[Adar123]

Hi everyone,

I am trying to copy the data from an active sheet to another file. The destination file contains other data which should not be affected, so I am pointing it the location of paste at the first empty row.

For some reason, I get error "object variable or with block variable not set" when reach the point of this line:

Set wkBk = myApp.Workbooks.Open("DestFile_" & dTod & "." & "xls")

What am I missing?

Extract of VBA code responsible for the action:

dTod = Format(Date, "yyyymmdd")

Set wkBk = ActiveWorkbook
lastRow = Range("A" & Rows.Count).End(xlUp).Row
wkBk.Sheets("NewData").Range("A1:E" & lastRow).Copy

Set wkBk = myApp.Workbooks.Open("DestFile_" & dTod & "." & "xls")
Set wkSht = wkBk.Sheets("NewData")
wkSht.Activate

lastRow = wkBk.Sheets("NewData").Range("A" & Rows.Count).End(xlUp).Row

Range("A" & lastRow).Select
wkSht.Paste

wkBk.SaveAs "DestFile_" & dTod & "." & "xls"
wkBk.Close

 

[Yongle]

The path to the file is missing
Try something like this, replacing path string below with the correct path

Dim fPath as string
fPath = "C:\Test\Folder\Subfolder"
Set wkBk = myApp.Workbooks.Open(fPath & "\" & "DestFile_" & dTod & "." & "xls")

 

[Adar123]

My original code has a full path, the error persists.

 

[Yongle]

Set wkBk = myApp.Workbooks.Open(fpath & "\" & "DestFile_" & dTod & "." & "xls")

The syntax of the above line is correct. It works for me

How is variable myApp declared and initiated in your code?

 

[Adar123]

Thank you,

I missed that previously set

 Set myApp = Nothing

and haven't reset it as

Set myApp = CreateObject("Excel.Application")

It works now...but the location for pasting data is incorrect: the data is pasted to A1 and I receive message that "Data on the Clipboard is not the same size and shape as the selected area. Do you want to paste anyway." I added commentary next to individual lines of code hoping this can help a third person to see the issue...any thoughts?

dTod = Format(Date, "yyyymmdd")
lastRow = Range("A" & Rows.Count).End(xlUp).Row
wkBk.Sheets("NewData").Range("A1:E" & lastRow).Copy

Set wkBk = myApp.Workbooks.Open("\\path\DestFile_" & dTod & "." & "xls") 'order to open the existing file
Set wkSht = wkBk.Sheets("NewData") 'order to activate sheet "NewData" within DestFile_" & dTod & "." & "xls" file
wkSht.Activate

lastRow = wkBk.Sheets("NewData").Range("A" & Rows.Count).End(xlUp).Row 'counts number of rows in the sheetNewData e.g. existing file has 2000 rows of data

Range("A" & lastRow).Select 'selects the location where to paste e.g. row 2000
wkSht.Paste 'pastes data

 

[Yongle]

Why are you using Set myApp = CreateObject("Excel.Application") ?
(I already know that you want to open another workbook )

The file extension in your pseudo code is xls
- is the workbook in the old format or should the extension be xlsx ?
(I am asking because Rows.Count yields different results)

Do you want to overwrite the last row of data in sheet NewData ?
- I would expect the data to be pasted to the row below the last row

 

[Adar123]

Why are you using Set myApp = CreateObject("Excel.Application") ?
(I already know that you want to open another workbook )

Yes, I want to open another workbook.

The file extension in your pseudo code is xls
- is the workbook in the old format or should the extension be xlsx ?
(I am asking because Rows.Count yields different results)
It is xls...we will have to stick to that.

Do you want to overwrite the last row of data in sheet NewData ?
- I would expect the data to be pasted to the row below the last row
That is a good point, it should be one row below.

 

[Yongle]

Yes, I want to open another workbook.

If there is no other reason, then variable myApp is not required
It is unlikely to be the cause of your paste problem, but try amending the line as indicated below

Set wkBk =Workbooks.Open(path to your file)

 

[Yongle]

It is usually safer to refer to objects rather than trying to select everything
One minor amendment in the code can cause the wrong object to be active - and then it is difficult to work out why the code is not working

UNTESTED, try something along these lines
If you cannot get it to work then post your whole code from sub to end sub

'2 additional variables used
    Dim Rng As Range, Cel As Range

    dTod = Format(Date, "yyyymmdd")
    With wkBk.Sheets("NewData")
        lastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        'range to copy
        Set Rng = .Sheets("NewData").Range("A1:E" & lastRow)
    End With

'other workbook
    Set wkBk = Workbooks.Open("path to file")
    Set wkSht = wkBk.Sheets("NewData")
    lastRow = wkSht.Range("A" & wkSht.Rows.Count).End(xlUp).Row
    'where to paste
    Set Cel = wkSht.Range("A" & lastRow + 1)
'copy and paste
    Rng.Copy Destination:=Cel

 

[Adar123]

Thanks,

Unfortunately I am getting Run Time Error 438: Object doesn't support this property or method when reach this line of code:

      Set Rng = .Sheets("NewData").Range("A1:E" & lastRow)