Workbooks as Variables / Closing Other Workbooks Through VBA

[mctopher]

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:

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:

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!

 

[carden2]

try this

dim dataSouce as workbook

set dataSource = ActiveWorkbook

dataSource.Close

 

[mctopher]

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]

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.

 

[mctopher]

That's exactly what I needed, thank you!