How Can I Import an Excel Spreadsheet Into an Access Database?

ScriptingGuy1

Hey, Scripting Guy! How can I import an Excel spreadsheet into an Access database?

— IE

Hey, IE. Wait! Don’t tell us; let us guess. Just a moment … we’re picking up the vibrations now … the sensation is getting stronger and more distinct … OK, got it: you’d like to know how to import an Excel spreadsheet into an Access database, wouldn’t you? Are we right? That’s what we thought.

Granted, some of you don’t seem too impressed by that. “Big deal,” you say. “Anyone could ‘predict’ what question IE was going to ask; all you have to do is read the question at the start of the column.” Oh ye of little faith. Sure, it’s true that we could have simply read IE’s question ahead of time and then made a “prediction.” However, how do you explain this: in yesterday’s column we predicted that today we would cover importing an Excel spreadsheet into an Access database. It’s true; just take a look at this quote from the April 3^rd column:

“Does that mean that we could use a similar script to import data? As a matter of fact it does, and in tomorrow’s column we’ll show you how to do that very thing.”

That’s OK, no need to apologize; we’re used to dealing with skeptics. And for those of you wondering how we were able to make such an uncanny prediction, well, to tell you the truth, we don’t know; it’s just a gift. But don’t worry: the Scripting Guys vow to use this gift only for good, never for evil.

Well, maybe a little evil. But not much.

Of course, throughout the ages seers and psychics have been a dime-a-dozen; does the name Nostradamus ring a bell with anyone? But there’s one thing that separates the Scripting Guys from their fellow prophets and soothsayers: as near as we can tell, Nostradamus never wrote a script that imports an Excel spreadsheet into an Access database. Score one for the Scripting Guys:

Const acImport = 0
Const acSpreadsheetTypeExcel9 = 8

Set objAccess = CreateObject(“Access.Application”)
objAccess.OpenCurrentDatabase “C:\Scripts\Test.mdb”
objAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
    “Employees”, “C:\Scripts\Employees.xls”, True

Guess what: we knew you were going to take one look at this and think “Wow; that’s easy.” And you’re right: it is. As you can see, we start out by defining a constant name acImport and setting the value to 0; this tells the script that we want to import data (as opposed to exporting it). We then define a second constant, this one named acSpreadsheetTypeExcel9, and set the value to 8; this tells the script that we’re planning to import an Excel 2003 worksheet. Are there other spreadsheet formats that we can import into Access? You bet there are:

After we define our constants we then use these two lines of code to create an instance of the Access.Application object and open the database C:\Scripts\Test.mdb (something we do by calling the OpenCurrentDatabase method):

Set objAccess = CreateObject(“Access.Application”)
objAccess.OpenCurrentDatabase “C:\Scripts\Test.mdb”

At that point all we have to do is invoke the TransferSpreadsheet method and import the spreadsheet data into the database:

objAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
    “Employees”, “C:\Scripts\Employees.xls”, True

Ah, yes, we had a feeling you were going to ask that; here’s what the parameters passed to the TransferSpreadsheet method represent:

We should also add that it’s important that the data types match as well. If you have a database field named EmployeeID, a field that only accepts numeric data, don’t try importing spreadsheet data that includes non-numeric data (e.g., an employee ID of 1234ABC). That’s not going to work.

Oh, and here’s another thing we should add: there’s actually an optional sixth parameter (following HasFieldNames) that enables you to limit your import to a specified range of data. For example, suppose your spreadsheet has 6 columns of data (columns A through F) and you only want to import the first 25 rows in that spreadsheet. Okey-doke. That means that the range of data to be imported runs from cells A1 through cell F25 (A1:F25 in Excel-speak). In turn, that means you can import just those 25 records by using this command:

objAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
    “Employees”, “C:\Scripts\Employees.xls”, True, “A1:F25”

We’ve already thought of that: suppose you would like to import the spreadsheet into a brand-new database (as opposed to importing the data into an existing database). Is that possible? Not if you’re Nostradamus. But if you’re the Scripting Guys, well, then it’s a different story:

Const acImport = 0
Const acSpreadsheetTypeExcel9 = 8

Set objAccess = CreateObject(“Access.Application”)
objAccess.NewCurrentDatabase “C:\Scripts\New.mdb”
objAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
    “Employees”, “C:\Scripts\Employees.xls”, True

You’re right: this does look a lot like the first script we showed you, doesn’t it? In fact, there’s only one difference: this time we don’t use the OpenCurrentDatabase method in order to open an existing database file. Instead, we use the NewCurrentDatabase method to create a brand-new database (C:\Scripts\New.mdb):

objAccess.NewCurrentDatabase “C:\Scripts\New.mdb”

At this point in time it’s hard to believe that anyone could possibly doubt the Scripting Guys and their power of clairvoyance. Just in case, however, we might note that the Scripting Guy who writes this column won the annual NCAA college basketball challenge by, among other things, correctly foreseeing a Florida victory over Ohio State. (Sorry; over the Ohio State University. Sheesh.) Furthermore, he already has a prediction for next year’s championship game: the University of Washington 78, North Carolina 65. Remember, you read it here first.

Unless it turns out that we’re wrong. In that case, you didn’t read it here at all, and we have no idea what you’re talking about.

ScriptingGuy1

Follow