{"id":65163,"date":"2007-04-03T20:45:00","date_gmt":"2007-04-03T20:45:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2007\/04\/03\/how-can-i-save-a-table-in-an-access-database-as-a-spreadsheet\/"},"modified":"2007-04-03T20:45:00","modified_gmt":"2007-04-03T20:45:00","slug":"how-can-i-save-a-table-in-an-access-database-as-a-spreadsheet","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-save-a-table-in-an-access-database-as-a-spreadsheet\/","title":{"rendered":"How Can I Save a Table in an Access Database as a Spreadsheet?"},"content":{"rendered":"

\"Hey, \n

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

— OT<\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

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<\/B><\/A> 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:<\/P>\n

\u201cSure is boring around here, isn\u2019t it?\u201d<\/P>\n

\u201cYep.\u201d<\/P>\n

\u201cI mean, really<\/I> boring.\u201d<\/P>\n

\u201cYep.\u201d<\/P>\n

\u201cWould you agree that it\u2019s pretty boring this week?\u201d<\/P>\n

\u201cYep.\u201d<\/P>\n

In fact, the highlight of the entire week \u2013 at least for Scripting Guy Dean Tsaltas \u2013 occurred when the Microsoft cafeteria served broccoli with cheese sauce two days in a row<\/I>!<\/P>\n

We told you that it was a long week.<\/P>\n

That\u2019s why we could have used an interesting question like this one last<\/I> 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\u2019t really need your question anymore. Sorry.<\/P>\n

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

Never mind; turns out that it won\u2019t take very long after all:<\/P>

Const acExport = 1\nConst acSpreadsheetTypeExcel9 = 8<\/p>\n
Set objAccess = CreateObject(“Access.Application”)\nobjAccess.OpenCurrentDatabase “C:\\Scripts\\Test.mdb”<\/p>\n
objAccess.DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _\n    “Employees”, “C:\\Scripts\\Employees.xls”, True\n<\/PRE>\n
Yes, we know: it\u2019s 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<\/I> what are we supposed to do?<\/P>\n
Explain how the script works? Hmmm, interesting idea; maybe we\u2019ll give that a try.<\/P>\n
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<\/I> data? As a matter of fact it does, and in tomorrow\u2019s column<\/B><\/A> we\u2019ll show you how to do that very thing. (Now we\u2019re trying to give you<\/I> something to look forward to!) After that we define a second constant \u2013 acSpreadsheetTypeExcel9 \u2013 and set the value to 8; this tells the script what kind<\/I> 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:<\/P>\n\n<\/THEAD>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
\n
Constant<\/B><\/P><\/TD>\n
\n
Value<\/B><\/P><\/TD><\/TR>\n
\n
acSpreadsheetTypeExcel12<\/P><\/TD>\n
\n
9<\/P><\/TD><\/TR>\n
\n
acSpreadsheetTypeExcel12Xml<\/P><\/TD>\n
\n
10<\/P><\/TD><\/TR>\n
\n
acSpreadsheetTypeExcel3<\/P><\/TD>\n
\n
0<\/P><\/TD><\/TR>\n
\n
acSpreadsheetTypeExcel4<\/P><\/TD>\n
\n
6<\/P><\/TD><\/TR>\n
\n
acSpreadsheetTypeExcel5<\/P><\/TD>\n
\n
5<\/P><\/TD><\/TR>\n
\n
acSpreadsheetTypeExcel7<\/P><\/TD>\n
\n
5<\/P><\/TD><\/TR>\n
\n
acSpreadsheetTypeExcel8<\/P><\/TD>\n
\n
8<\/P><\/TD><\/TR>\n
\n
acSpreadsheetTypeExcel9<\/P><\/TD>\n
\n
8<\/P><\/TD><\/TR>\n
\n
acSpreadsheetTypeLotusWJ2<\/P><\/TD>\n
\n
4<\/P><\/TD><\/TR>\n
\n
acSpreadsheetTypeLotusWK1<\/P><\/TD>\n
\n
2<\/P><\/TD><\/TR>\n
\n
acSpreadsheetTypeLotusWK3<\/P><\/TD>\n
\n
3<\/P><\/TD><\/TR>\n
\n
acSpreadsheetTypeLotusWK4<\/P><\/TD>\n
\n
7<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
That was all pretty easy, wasn\u2019t it? And, as you\u2019re about to see, it doesn\u2019t get much harder, either. After defining our constants we next use these two lines of code to create an instance of the Access.Application<\/B> object and then open the file C:\\Scripts\\Test.mdb (a feat we accomplish by calling the OpenCurrentDatabase<\/B> method):<\/P>
Set objAccess = CreateObject(“Access.Application”)\nobjAccess.OpenCurrentDatabase “C:\\Scripts\\Test.mdb”\n<\/PRE>\n
At this point we\u2019re just one command away from exporting our data as an Excel spreadsheet. In fact, now that we mention it, we\u2019re just this<\/I> command away from exporting our data as an Excel spreadsheet:<\/P>
objAccess.DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _\n    “Employees”, “C:\\Scripts\\Employees.xls”, True\n<\/PRE>\n
What we\u2019re doing here is using the DoCmd<\/B> object (a child object of Access.Application) to call the TransferSpreadsheet<\/B> 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:<\/P>\n\n\n\n\n\n\n\n
\u2022<\/TD>\n\n
acExport<\/B>, the constant that tells the script that we want to export data rather than import it.<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
acSpreadsheetTypeExcel9<\/B>, the constant that specifies the file format for the exported data.<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
\u201cEmployees\u201d<\/B>, the name of the table in Test.mdb to be exported.<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
\u201cC:\\Scripts\\Employees.xls\u201d<\/B>, the full path to the new Excel file being created.<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
True<\/B>, the HasFieldNames parameter, which tells the script to make the field names in the table the first row in the spreadsheet file.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
Believe it or not, that\u2019s 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.<\/P>\n
OK, true; it\u2019s not broccoli with cheese sauce. But it\u2019s close.<\/P>\n
We hope that helps, OT. And while we appreciate the offer, there\u2019s 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<\/I> than enough excitement for the Scripting Guys.<\/P>\n
\"Top<\/A>Top of page<\/A><\/DIV><\/A>\n
Saving an Access Table as a Spreadsheet Using Windows PowerShell<\/H2>\n
Just for the heck of it, we\u2019ve added a new feature to Hey, Scripting Guy!<\/I>: whenever possible, we\u2019ll provide a Windows PowerShell solution as well as our typical VBScript solution. In general we won\u2019t be able to provide much explanation for the PowerShell version, but at least there will be some sample code for you to look at.<\/P>\n
You know, sample code like this: <\/P>
$acExport = 1\n$acSpreadsheetTypeExcel9 = 8<\/p>\n
$a = New-Object -Comobject Access.Application\n$a.OpenCurrentDatabase(“C:\\Scripts\\Test.mdb”)<\/p>\n
$a.DoCmd.TransferSpreadsheet($acExport, $acSpreadsheetTypeExcel9, “Employees”, `\n    “C:\\Scripts\\Employees.xls”, $True)<\/p>\n
$a.Quit()<\/PRE><\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[710,54,48,49,3,5],"class_list":["post-65163","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-excel-spreadsheet","tag-microsoft-access","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
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 […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/65163","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=65163"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/65163\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=65163"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=65163"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=65163"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}