Transfer Data to and From Workbooks

[uk747]

Hi

I tried transferring data to a new workbook using code from Here

I got an error with the line below. The file is there because it passed If Fname = "" Then Exit Sub

With Workbooks.Open(Fname, UpdateLinks:=0)

Says Run time error 1004 Sorry we couldn't find test.xlsx. Is it possible it was moved renamed or deleted

If it did work, Can this be made quicker or more efficient by
* checking if open or exists
* transfer data to closed workbook last row
* Copy back updated data to thisworkbook.

Not sure of quickest/best way to do this
Either by copy data to closed workbook and copy back the updated data or
Transfer data Copy and then Data > Get Data from File Workbook using vba

 

[Zot]

It is most likely because the FName did not include complete path to the file test.xlsx.

The FName statement before If Fname = "" Then Exit Sub is just defining the path and filename, not verifying the existence of the file. So, then it was trying to open the file you get the error because the file is not found. Check the complete path if anything is missing.

 

[uk747]

It is most likely because the FName did not include complete path to the file test.xlsx.

The FName statement before If Fname = "" Then Exit Sub is just defining the path and filename, not verifying the existence of the file. So, then it was trying to open the file you get the error because the file is not found. Check the complete path if anything is missing.

Path is fine as it passes first part of the code. If I change code to test2 it exits sub

Sub Open_test()
Dim Fname As String

Fname = Dir(ThisWorkbook.path & "\test.xlsx")
If Fname = "" Then Exit Sub
With Workbooks.Open(Fname, UpdateLinks:=0)

End Sub

 

[Zot]

Instead of
Fname = Dir(ThisWorkbook.path & "\test.xlsx")

use
Fname = ThisWorkbook.path & "\test.xlsx")

 

[uk747]

Instead of
Fname = Dir(ThisWorkbook.path & "\test.xlsx")

use
Fname = ThisWorkbook.path & "\test.xlsx")

Thanks I'll try later. Not sure why fluff used dir

Also if it exists and is open what's the best way to transfer data to and then grab the updated data from a sheet and paste into this workbook

Or is it better to use VBA to

Transfer data Copy and then Data > Get Data from File Workbook using vba

Not sure how to do this part

 

[Zot]

Thanks I'll try later. Not sure why fluff used dir

Also if it exists and is open what's the best way to transfer data to and then grab the updated data from a sheet and paste into this workbook

Or is it better to use VBA to

Transfer data Copy and then Data > Get Data from File Workbook using vba

Not sure how to do this part

I did not look at the fluff code. I did not declare the Fname as String, thus the default is Variant. Not sure if that makes a difference.

Using the code you provided, it found the file but not captured as path string. So, after passing that line the Fname is just the file name without path, causing it could not find the existed file when I debug it.

For repetitious routine, I'd just use VBA. No careless mistake. I would just declare both workbook as variables. Say:
Set wb1 = ActiveWorkbook (the book with macro)
Set wb2 = Path & theotherworkbookname (if you already have the file opened. Otherwise use the workbook.Open method like in your code)

Faster execution if you declare everything including worksheet.