Opening two workbooks using VBA

kashflo

New Member
Joined
Apr 24, 2013
Messages
15
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
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
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

New Member
Joined
Apr 24, 2013
Messages
15
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,158
Messages
5,600,052
Members
414,357
Latest member
Gemma_R

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top