Opening two workbooks using VBA

[kashflo]

Morning,

I have a spreadsheet that I need to open two other workbooks to copy and paste data from.

The name of the two workbooks changes each day, and the location because they are filed in subfolders.

I have the following code, which works to open the first sheet but then comes up with the following error when trying to open the second sheet.
"Run-time error '1004:
Excel cannot access 'Hub PVA'. The document may be read only or encrypted".

The folder is not encrypted because when I was only opening the second document with the same code, it worked.

The code I am using is:
'DECLARATION
On Error GoTo ERR1:
ChDir "G:\GENERAL\TRANSPORT\DAILYPLANS"
Workbooks.Open Filename:="G:\GENERAL\TRANSPORT\DAILYPLANS"
nam1 = ActiveWorkbook.Name
ERR1:
If IsEmpty(nam1) Then
Filename = Application.GetOpenFilename
If Filename = "False" Then End
Application.DisplayAlerts = False
Workbooks.Open Filename:=Filename
nam1 = ActiveWorkbook.Name
Else
End If


'SCHEDULE SUMMARY DATA
Windows(nam1).Activate
Worksheets("SUMMARY").Select
Range("A1:U65").Select
Selection.Copy
ThisWorkbook.Activate
Worksheets("TUE SCHD SUM").Select
Range("A1").PasteSpecial Paste:=xlPasteValues


'CLOSE SCHEDULE WINDOW
Windows(nam1).Activate
ActiveWindow.Close False


'GET TRT INFORMATION
On Error GoTo ERR2:
ChDir "G:\GENERAL\Hub PVA"
Workbooks.Open Filename:="G:\GENERAL\Hub PVA"
nam2 = ActiveWorkbook.Name
ERR2:
If IsEmpty(nam2) Then
Filename = Application.GetOpenFilename
If Filename = "False" Then End
Application.DisplayAlerts = False
Workbooks.Open Filename:=Filename
nam1 = ActiveWorkbook.Name
Else
End If

 

[GTO]

Hi there,

At least for me, I stopped at: Workbooks.Open Filename:="G:\GENERAL\TRANSPORT\DAILYPLANS"

The workbook's name is DAILYPLANS?

I didn't test, but I would think the extension would be required (Sorry I'm not checking to confirm). Regardless, you really don't want to work with windows (and their captions) when trying to control what we are doing with a workbook. Might I suggest you layout in plain English, what we are wanting to do?

Mark

 

[kashflo]

Mark,

The workbooks name isn't DAILYPLANS.

That is the folder where the subfolders are located.

I basically need to open two different workbooks and copy and paste information from them.

The workbooks are named according to date.

The macro works to open the first folder location and then the user manually finds the correct folder and file but it comes up with the runtime error when trying to go to the second location in the same way.

Thanks

Sadie

 

[kashflo]

Managed to sort this issue out using a file name cell reference