Hey, Scripting Guy! I’m trying to import a spreadsheet into an Access database. The problem is that the spreadsheet has multiple worksheets. I can import the first worksheet without any problem, but I can’t figure out how to import all the other worksheets in the file. How can I import multiple worksheets into an Access database?

— EA

Hey, EA. Before we launch into today’s column we’d like to apologize to our readers from Oregon. Well, actually, we’d like to apologize, but we’re not convinced anyone from Oregon can read.

Ha-ha.

Sorry; that’s exactly the thing we wanted to apologize for. To be perfectly honest, the Scripting Guy who writes this column has nothing against Oregon or Oregonians; all in all it seems like a nice place filled with nice people.

Of course, having said that, it’s also true that much of the fun of being a sports fan is picking teams that you like (the University of Washington) and picking teams that you – um, that you perhaps … don’t like quite as well (the University of Oregon). Despite what some people (many of whom work at Microsoft) will tell you, sports are no fun if “everyone is a winner.” Sports are only fun if there’s a winner. And if there’s a winner, well, there pretty much has to be a loser. For every action there’s an equal and opposite reaction. Yin and yang. Winners and losers. Dogs and cats. Scripting Guys and people who actually know what they’re doing.

At any rate, the point of all this is simply to explain why the Scripting Guy who writes this column is in a good mood today. Last night (this column having been written on Friday, January 18^th) the University of Washington whipped the University of Oregon in a Pac-10 Conference basketball game. Oregon actually played pretty well; the Huskies, who’ve struggled a little bit trying to find their identity, just played better.

So does this mean that the Scripting Guy who writes this column is sitting around enjoying the suffering and misfortune that the University of Oregon and its fans underwent? Heavens no, he – oh, wait, that’s not right: of course he’s sitting around enjoying the suffering and misfortune that the University of Oregon and its fans underwent. After all, isn’t that what sports is all about?

So just how good does the Scripting Guy who writes this column feel today? Good enough to write a new version of the Scriptomatic? Well, not that good; after all, it wasn’t that big of a win. But good enough to show you how to put together a script that can important multiple Microsoft Excel worksheets into a Microsoft Access database:

Const acImport = 0
Const acSpreadsheetTypeExcel9 = 8

Set objAccess = CreateObject("Access.Application")
objAccess.OpenCurrentDatabase "C:\Scripts\Personnel.mdb"

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

strFileName = "C:\Scripts\ImportData.xls"

Set objWorkbook = objExcel.Workbooks.Open(strFileName)
Set colWorksheets = objWorkbook.Worksheets

For Each objWorksheet in colWorksheets 
    Set objRange = objWorksheet.UsedRange 
    strWorksheetName = objWorksheet.Name & "!" & objRange.Address(False, False) 
    objAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
        "Employees", strFileName, True, strWorksheetName
Next

OK, so how does this thing work? Well, to begin with, we define a pair of constants, acImport and acSpreadsheetTypeExcel9:

Const acImport = 0
Const acSpreadsheetTypeExcel9 = 8

The constant acImport tells the script that we want to import data into the database (as opposed to exporting data from the database); acSpreadsheetTypeExcel9 tells the script that the data we’re importing comes from an Excel 2003 spreadsheet.

After defining the constants, we then use these two lines of code to create an instance of the Access.Application object, and to open the database C:\Scripts\Personnel.mdb:

Set objAccess = CreateObject("Access.Application")
objAccess.OpenCurrentDatabase "C:\Scripts\Personnel.mdb"

As it turns out, Access has a TransferSpreadsheet method that enables it to import data from Excel. For better or worse, however, TransferSpreadsheet can import data from only a single worksheet at a time. (By default, that’s the first worksheet in the Excel file.) Fortunately, it’s possible to important other worksheets in the file, provided that you know the names of all those worksheets. So how can you determine the names of all the worksheets in an Excel spreadsheet? As far as we know, there’s only one way to do that: your script has to open the spreadsheet file and take a peek at all the worksheet names.

That explains why, in our next two lines of code, we create an instance of the Excel.Application object and then set the Visible property to True; that gives us a running instance of Excel that we can see onscreen. After assigning the name of the spreadsheet file (C:\Scripts\ImportData.xls) to a variable named strFileName, we use these two lines of code to open the file and return a collection of all the worksheets in that file:

Set objWorkbook = objExcel.Workbooks.Open(strFileName)
Set colWorksheets = objWorkbook.Worksheets

Once we have that collection we set up a For Each loop to loop through all the worksheets in ImportData.xls:

For Each objWorksheet in colWorksheets

And what happens after we’ve done that? Well, now we’re ready to do some serious scripting.

In order to import data from multiple worksheets we’ll need to put together some string values that combine the worksheet names (e.g., Sheet1) with the range of data to be imported (for example, cells A1 through B50). Those string values will look something like this:

Sheet1!A1:B50
Sheet2!A1:B34
Sheet3!A1:B87

How do we build these string values? Well, to begin with, we determine the range of data to be imported by using this line of code:

Set objRange = objWorksheet.UsedRange

What we’re doing here is creating an instance of Excel’s Range object; in addition, we’re configuring the Range to encompass the entire worksheet. (Or at least the portion of the worksheet that actually contains data; that’s what the UsedRange property represents.) Once we’ve configured the Range object we can then use this line of code to build our first string value:

strWorksheetName = objWorksheet.Name & "!" & objRange.Address(False, False)

As you can see, all we’re doing here is combining three elements, then assigning the resulting value to a variable named strWorksheetName. And what exactly are those three elements? These:

• objWorksheet.Name. This is simply the name of the worksheet (e.g., Sheet1).

• !. This is, well, it’s an exclamation mark. Now admittedly, you might wonder why we need an exclamation mark; after all, this script isn’t that exciting. (Although now that we’ve looked at the code a little more closely, well …) As it turns out, however, the exclamation mark isn’t used to convey excitement; instead, it’s used to separate the worksheet data from the data range.

• Address(False, False). The Address property gives us the starting and ending points for our range (e.g., A1:B50). By setting the RowAbsolute and ColumnAbsolute parameters to False (that’s what the (False, False) part is for), we get back the starting and ending points in the format A1:B50. Which is just precisely the format we need to import data from the worksheet.

The net result is a string value similar to this: Sheet1!A1:B50. By passing this value to the TransferSpreadsheet method TransferSpreadsheet will know that it needs to look at Sheet1 and import the values in cells A1 through B50.

Speaking of which, it’s now time to go ahead and call the TransferSpreadsheet method:

objAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
    "Employees", strFileName, True, strWorksheetName

If you look at the object reference (objAccess) you’ll see that we’ve switched back to Access; that’s because the TransferSpreadsheet method belongs to Access’ DoCmd object, an object that enables you to run Access commands from a script. As you can see, we pass TransferSpreadsheet six parameters:

• acImport. The constant that tells the script that we want to import data rather than export it.

• acSpreadsheetTypeExcel9. The constant that tells the script what kind of data we’re importing.

• “Employees”. The name of the Access table that we want to import the data into.

• strFileName. The path to the spreadsheet file (C:\Scripts\ImportData.xls).

• True. A Boolean value that tells the script that the first row in our worksheet contains field names. What if the first row in your worksheet doesn’t contain field names; that is, what if you have actual data in that first row? Then set this value to False.

  Of course, having said that, we strongly recommend that you put field names in the first row of each worksheet. That helps you keep track of which data is which; it also helps ensure that the data gets imported correctly. As long as you use field names (e.g., Name, Rank, SerialNumber) in the first row, the columns in your spreadsheet do not have to be in the same order as the fields in the database (Rank, SerialNumber, Name). If you don’t use field names in the first row then your spreadsheet columns and database fields must be in the exact same order.

• strWorksheetName. This is the string value we just created. (Remember? Sheet1!A1:B50.) In turn, this tells the script exactly which data, from which worksheet, should be imported.

And that’s it. After the first worksheet gets imported we pop back to the beginning of the loop and repeat the process with the next worksheet in the collection. When all is said and done, the data in each and every worksheet in ImportData.xls will have been added to the Employees table in the database.

That should do the trick, EA; if not, let us know. In the meantime, the Scripting Guy who writes this column is feeling a little guilty for gloating over the fact that the University of Washington defeated the University of Oregon in basketball. After all, that’s not very sportsmanlike, is it? So tell you what, people of Oregon, how about this? As you know, the 2008 Winter Scripting Games are fast approaching. For residents of the great state of Oregon, and for graduates of the University of Oregon, the Scripting Guy who writes this column has decided to give you a discount on registration fees for the Games. Regardless of what the other Scripting Guys might say, he’s going to charge Oregonians just $250 – no, wait, make that $150 for their Scripting Games registration fee. That’s right: a special offer available only to Oregonians and graduates of the University of Oregon. Keep in mind, though, that this is a limited-time offer: it expires on February 15^th (the day the Games begin) or the moment that the Scripting Editor finds out about it. Whichever comes first.(Editor’s Note: The Scripting Editor found out. No one is being charged a registration fee for the Scripting Games – not even Oregon Ducks.)

Hey, no need to thank him. After all, isn’t that what an Oregonian would expect from a Washington Husky?