Workbooks as Variables / Closing Other Workbooks Through VBA

mctopher

Board Regular
Joined
Jun 23, 2011
Messages
192
I'm struggling trying to close other workbooks using VBA. I have a template that opens another file, copies elements of that workbook and pastes it into the current template.

The primary problem I have is setting the workbook name of the data file as a variable. I've tried to set a variable with the file name after the data file has been activated with the following code:

HTML:
Dim DataSource As Workbook
DataSource = ActiveWorkbook.Name

When I try that I get an error stating "Run-time error '91': Object variable or With block variable not set".

Consequently I'm not able to get to a point where I can say:
HTML:
DataSource.Close Savechanges:=False

Additionally I'm not able to use a variable name for the current file, so as I switch between the data source and the template I have to hard code the file name. I'd like to have this as a variable value so each saved version doesn't require recoding.

Any insight would be appreciated. Thanks!
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

carden2

New Member
Joined
Jun 28, 2012
Messages
14
try this

dim dataSouce as workbook

set dataSource = ActiveWorkbook

dataSource.Close
 

mctopher

Board Regular
Joined
Jun 23, 2011
Messages
192
Thank you! That seemed to get me most of the way there. The one issue I'm having now is changing between windows based on the workbook variable names. For example:

Windows(DataSource).Activate
- this produces an error message and will not change windows

Windows("Master Data Source File v20120625.xlsx").Activate
- This works correctly, however I want to get away from the hard coded file name.

Any further suggestions?
 

carden2

New Member
Joined
Jun 28, 2012
Messages
14
try something where you define both workbooks or all the workbooks you want to switch between for instance

you have datasource set as one workbook then set datasource2 to another workbook

you can use like

set wkbkA = workbook("C:\"open workbook name") <- the workbook your working from
set wkbkB = open.workbook("C:\filename") <-this will open the workbook for you so it will be closed

you can also set worksheets to specific sheets

dim awksht as Worksheet

set awksht = awkbk.WorkSheet("Sheet1")

then you can activate like so

awkbk.activate

or

bwkbk.activate


this makes it alot easier to switch it assigns each workbook a name.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,214
Messages
5,594,882
Members
413,947
Latest member
gizmolucy

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