Excelling

August 30, 2008

Opening A Workbook

Filed under: VBA — fatrunner @ 2:58 pm
Tags: ,

Last week I talked about adding a new workbook using VBA.  This week I want to discuss opening a workbook via VBA. This is one that I use every day and find useful. In my case I use this when I dump data out of my accounting software and want to add it to an existing spreadsheet that I use to analyize and accumulate that data. It is one simple line of code:

Workbooks.Open("C:\SomeFolder\SomeSubFolder\YourFile.xls")

It doesn’t get much simpler than that. But it is not unusual for spreadsheets to have more than one worksheet.  If you saved “YourFile.xls” when you were viewing sheet2, then it will open on sheet2.  But what if the data you want to view and/or manipulate is on sheet1?  Well unless you specifically tell VBA to manipulate sheet1 it will execute the code on whatever sheet it opens to. So it is always a good practice to tell VBA to select the sheet you want. You would do so by using the following code:

Worksheets(1).Activate

Now although that is pretty straight forward, it does require a little further explanation. When you tell VBA to activate worksheets(1) what you are specifically saying is that you want to activate the first worksheet in the workbook regardless of what it is named.  It could be named sheet4, but if it is the sheet furthest to the left it is worksheets(1) to VBA. So what do you do if you want to make sure it actually selects sheet1 or maybe a sheet that you’ve named Data?

Then you would change the above line of code to this:

Worksheets("Sheet1").Activate

Instead of using the index number (1) you simply put the name of the sheet in quotes inside the parenthesis.

Another tweak to this that I have not personally used is that instead of using the word Worksheets you can use the word Sheets. The difference between the two is that Sheets includes all sheets in the workbook which would include any chart sheets or dialog sheets.  Conversely if you were to use either:

Charts(1).Activate
DialogSheets(1).Activate

It would function the same as Worksheets(1).Activate above.  It would select the very first chart sheet or dialog sheet in the workbook regardless of its name.  So if you wanted to make sure you selected the correct chart when you opened up your YourFile.xls you would want to follow the same principle and write it this way:

Charts("Chart1").Activate

Once you’ve gotten this, you are well on your way to adding data to an existing spreadsheet or retreiving data from an existing spreadsheet and using VBA to do the heavy lifting for you.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: