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

Create a free website or blog at WordPress.com.