Hey, Scripting Guy! How can I save a table in an Access database as an Excel spreadsheet?

— OT

Hey, OT. You know, we appreciate the question, but where were you last week, when we really needed you? The truth is, last week was excruciatingly dull, at least for two of the Scripting Guys. While Scripting Guy Jean Ross was busy battling ninja warriors in San Diego and Scripting Guy Peter Costantini amused himself by climbing volcanoes in Nicaragua, the remaining Scripting Guys sat around the office all week having conversations like this:

“Sure is boring around here, isn’t it?”

“Yep.”

“I mean, really boring.”

“Yep.”

“Would you agree that it’s pretty boring this week?”

“Yep.”

In fact, the highlight of the entire week – at least for Scripting Guy Dean Tsaltas – occurred when the Microsoft cafeteria served broccoli with cheese sauce two days in a row!

We told you that it was a long week.

That’s why we could have used an interesting question like this one last week; that would have added a little excitement and pizzazz to our long and lonely days. Now that Jean is back we have all the excitement and pizzazz we could ever ask for; we don’t really need your question anymore. Sorry.

On the other hand, we can’t really say that this week is turning out to be any more exciting than last week did; in addition, Jean doesn’t seem to be anywhere near as interesting as we thought she was. So what the heck, OT: let’s see if we can figure out how to save an Access table as an Excel spreadsheet. Just be patient; this could take awhile.

Never mind; turns out that it won’t take very long after all:

Const acExport = 1
Const acSpreadsheetTypeExcel9 = 8

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

Yes, we know: it’s great that this task can be accomplished by using just a few lines of code. However, we were hoping to kill a bunch of time by working on this one script; now what are we supposed to do?

Explain how the script works? Hmmm, interesting idea; maybe we’ll give that a try.

As you can see, we start out by defining a constant named acExport and setting the value to 1; that tells the script that we want to export data. 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. (Now we’re trying to give you something to look forward to!) After that we define a second constant – acSpreadsheetTypeExcel9 – and set the value to 8; this tells the script what kind of spreadsheet we want to create. The constant acSpreadsheetTypeExcel9 creates an Excel 2003 spreadsheet; alternatively, you might want to save your data in a different format. If so then use one of the following constants and their values:

That was all pretty easy, wasn’t it? And, as you’re about to see, it doesn’t get much harder, either. After defining our constants we next use these two lines of code to create an instance of the Access.Application object and then open the file C:\Scripts\Test.mdb (a feat we accomplish by calling the OpenCurrentDatabase method):

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

At this point we’re just one command away from exporting our data as an Excel spreadsheet. In fact, now that we mention it, we’re just this command away from exporting our data as an Excel spreadsheet:

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

What we’re doing here is using the DoCmd object (a child object of Access.Application) to call the TransferSpreadsheet method; this method enables you to export data to or import data from a spreadsheet. As you can see, we simply call the method, passing five parameters:

Believe it or not, that’s all we have to do; if you run this script and then take a look at the folder C:\Scripts you should see a new spreadsheet file named Employees.xls.

OK, true; it’s not broccoli with cheese sauce. But it’s close.

We hope that helps, OT. And while we appreciate the offer, there’s no need to provide us with another exciting question for tomorrow. After all, tomorrow is Taco Salad Wednesday in the cafeteria, and that alone is more than enough excitement for the Scripting Guys.

Top of page

Saving an Access Table as a Spreadsheet Using Windows PowerShell

Just for the heck of it, we’ve added a new feature to Hey, Scripting Guy!: whenever possible, we’ll provide a Windows PowerShell solution as well as our typical VBScript solution. In general we won’t be able to provide much explanation for the PowerShell version, but at least there will be some sample code for you to look at.

You know, sample code like this:

$acExport = 1
$acSpreadsheetTypeExcel9 = 8

$a = New-Object -Comobject Access.Application
$a.OpenCurrentDatabase(“C:\Scripts\Test.mdb”)
$a.DoCmd.TransferSpreadsheet($acExport, $acSpreadsheetTypeExcel9, “Employees”, `
    “C:\Scripts\Employees.xls”, $True)
$a.Quit()