run time error 1004 in opening two different workbooks

Question

I am trying to open two different workbooks for transferring data. The complete location of workbooks are in two cells of the current workbook. First workbook opens correctly but there is error in opening other workbook. It says:

run time error 1004. File can't be found.

However, if i use path of workbook directly in the code, then it works fine. Anybody please tell me what I am doing wrong.

Sub ProcessReport()


Dim MainWb As Workbook
Dim DestWb As Workbook


' Load source and destination files
Set DestWb = Workbooks.Open(Range("E10").Value)
Set MainWb = Workbooks.Open(Range("E6").Value)

' Code for manipulation


End Sub

You did look at some of the Excel runtime error 1004 questions in the Related list to the right before posting this question, right? Also, what are the source and destination filenames you're putting in the cells? — Ken White
have not tested, but my guess would be the 'Range("E6")' is pulling from the DestWB instead of the original one running the VB code because when you open the file it is made the active file/sheet. — Alan Waage
Source and destination file names are absolute. e.g - C:\Users\SANTOSH\Desktop\file.xls — Vivek

Chinwobble Chinwobble · Accepted Answer · 2016-06-05T11:57:50

In your original code the second workbooks.open command is reading the cell "E6" from the workbook "DestWb" because that is the activeWorkbook at the time that command is executed, rather than the workbook where the macro is saved. You can fix this by changing:

Set DestWb = Workbooks.Open(Range("E10").Value)
Set MainWb = Workbooks.Open(Range("E6").Value)

To this:

Set Ws = ThisWorkbook.Sheets("Sheet1")
Set DestWb = Workbooks.Open(Ws.Range("E10").Value)
Set MainWb = Workbooks.Open(Ws.Range("E6").Value)

This will save "Sheet1" from the workbook where the macro is running as an object reference so that your macro tries to use the filepaths in "E10" and "E16" from the workbook where the macro is saved. Range("E6").Value is now qualified with the worksheet ws.
You can change "Sheet1" to whatever the tab is where the filepaths are in your macro workbook.

run time error 1004 in opening two different workbooks

1 Answers