{"id":65153,"date":"2007-04-04T20:49:00","date_gmt":"2007-04-04T20:49:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2007\/04\/04\/how-can-i-import-an-excel-spreadsheet-into-an-access-database\/"},"modified":"2007-04-04T20:49:00","modified_gmt":"2007-04-04T20:49:00","slug":"how-can-i-import-an-excel-spreadsheet-into-an-access-database","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-import-an-excel-spreadsheet-into-an-access-database\/","title":{"rendered":"How Can I Import an Excel Spreadsheet Into an Access Database?"},"content":{"rendered":"

\"Hey, \n

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

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

Hey, IE. Wait! Don\u2019t tell us; let us guess. Just a moment \u2026 we\u2019re picking up the vibrations now \u2026 the sensation is getting stronger and more distinct \u2026 OK, got it: you\u2019d like to know how to import an Excel spreadsheet into an Access database, wouldn\u2019t you? Are we right? That\u2019s what we thought.<\/P>\n

Granted, some of you don\u2019t seem too impressed by that. \u201cBig deal,\u201d you say. \u201cAnyone<\/I> could \u2018predict\u2019 what question IE was going to ask; all you have to do is read the question at the start of the column.\u201d Oh ye of little faith. Sure, it\u2019s true that we could<\/I> have simply read IE\u2019s question ahead of time and then made a \u201cprediction.\u201d However, how do you explain this<\/I>: in yesterday\u2019s<\/I><\/B> column<\/B><\/A> we predicted that today we would cover importing an Excel spreadsheet into an Access database. It\u2019s true; just take a look at this quote from the April 3rd<\/SUP> column:<\/P>\n

\u201cDoes 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 we\u2019ll show you how to do that very thing.\u201d<\/P>\n

That\u2019s OK, no need to apologize; we\u2019re used to dealing with skeptics. And for those of you wondering how<\/I> we were able to make such an uncanny prediction, well, to tell you the truth, we don\u2019t know; it\u2019s just a gift. But don\u2019t worry: the Scripting Guys vow to use this gift only for good, never for evil.<\/P>\n

Well, maybe a little evil. But not much.<\/P>\n

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\u2019s 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:<\/P>

Const acImport = 0\nConst acSpreadsheetTypeExcel9 = 8<\/p>\n
Set objAccess = CreateObject(“Access.Application”)\nobjAccess.OpenCurrentDatabase “C:\\Scripts\\Test.mdb”<\/p>\n
objAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _\n    “Employees”, “C:\\Scripts\\Employees.xls”, True\n<\/PRE>\n
Guess what: we knew<\/I> you were going to take one look at this and think \u201cWow; that\u2019s easy.\u201d And you\u2019re 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\u2019re planning to import an Excel 2003 worksheet. Are there other spreadsheet formats that we can import into Access? You bet there are:<\/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
After we define our constants we then use these two lines of code to create an instance of the Access.Application<\/B> object and open the database C:\\Scripts\\Test.mdb (something we do by calling the OpenCurrentDatabase<\/B> method):<\/P>
Set objAccess = CreateObject(“Access.Application”)\nobjAccess.OpenCurrentDatabase “C:\\Scripts\\Test.mdb”\n<\/PRE>\n
At that point all we have to do is invoke the TransferSpreadsheet<\/B> method and import the spreadsheet data into the database:<\/P>
objAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _\n    “Employees”, “C:\\Scripts\\Employees.xls”, True\n<\/PRE>\n
Ah, yes, we had a feeling you were going to ask that; here\u2019s what the parameters passed to the TransferSpreadsheet method represent:<\/P>\n\n\n\n\n\n\n\n
\u2022<\/TD>\n\n
acImport<\/B>, the constant that tells the script that we want to import data rather than export it.<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
acSpreadsheetTypeExcel9<\/B>, the constant that specifies the file format for the imported data.<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
\u201cEmployees\u201d<\/B>, the name of the table in Test.mdb we want to add the spreadsheet data to. Suppose the database doesn\u2019t contain a table named Employees; what then? Well, in that case the script will automatically create a new table (named Employees, of course) for you.<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
\u201cC:\\Scripts\\Employees.xls\u201d<\/B>, the full path to the Excel spreadsheet we want to import.<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
True<\/B>, the HasFieldNames parameter, which tells the script that the first row in the spreadsheet file consists of field names. As you might expect, the import is more likely to work if the field names in your database match the field names in your spreadsheet. But what if they don\u2019t match? Well, in that case you might simply import the data to a new table. Alternatively, you can set the HasFieldNames parameter to False; when you do that the script will simply match the first column of data in the spreadsheet with the first field in the database table, then match the second column with the second field, etc. That works just fine, but you do need to make sure that the field order in the database is the same as the column order in the spreadsheet. If your database table has fields named FirstName, LastName, and Address, in that order, then your spreadsheet needs to have columns listing first names, last names, and addresses, in that same order.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
We should also add that it\u2019s important that the data types match as well. If you have a database field named EmployeeID, a field that only accepts numeric data, don\u2019t try importing spreadsheet data that includes non-numeric data (e.g., an employee ID of 1234ABC). That\u2019s not going to work.<\/P>\n
Oh, and here\u2019s another thing we should add: there\u2019s 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<\/B> in Excel-speak). In turn, that means you can import just those 25 records by using this command:<\/P>
objAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _\n    “Employees”, “C:\\Scripts\\Employees.xls”, True, “A1:F25”\n<\/PRE>\n
We\u2019ve already thought of that: suppose you would<\/I> 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\u2019re Nostradamus. But if you\u2019re the Scripting Guys, well, then it\u2019s a different story:<\/P>
Const acImport = 0\nConst acSpreadsheetTypeExcel9 = 8<\/p>\n
Set objAccess = CreateObject(“Access.Application”)\nobjAccess.NewCurrentDatabase “C:\\Scripts\\New.mdb”<\/p>\n
objAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _\n    “Employees”, “C:\\Scripts\\Employees.xls”, True\n<\/PRE>\n
You\u2019re right: this does<\/I> look a lot like the first script we showed you, doesn\u2019t it? In fact, there\u2019s only one difference: this time we don\u2019t use the OpenCurrentDatabase method in order to open an existing database file. Instead, we use the NewCurrentDatabase<\/B> method to create a brand-new database (C:\\Scripts\\New.mdb):<\/P>
objAccess.NewCurrentDatabase “C:\\Scripts\\New.mdb”\n<\/PRE>\n
At this point in time it\u2019s 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<\/B><\/A> by, among other things, correctly foreseeing a Florida victory over Ohio State. (Sorry; over the<\/I> Ohio State University. Sheesh.) Furthermore, he already has a prediction for next year\u2019s championship game: the University of Washington 78, North Carolina 65. Remember, you read it here first.<\/P>\n
Unless it turns out that we\u2019re wrong. In that case, you didn\u2019t read it here at all, and we have no idea what you\u2019re talking about.<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! How can I import an Excel spreadsheet into an Access database?— IE Hey, IE. Wait! Don\u2019t tell us; let us guess. Just a moment \u2026 we\u2019re picking up the vibrations now \u2026 the sensation is getting stronger and more distinct \u2026 OK, got it: you\u2019d like to know how to import an […]<\/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-65153","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 import an Excel spreadsheet into an Access database?— IE Hey, IE. Wait! Don\u2019t tell us; let us guess. Just a moment \u2026 we\u2019re picking up the vibrations now \u2026 the sensation is getting stronger and more distinct \u2026 OK, got it: you\u2019d like to know how to import an […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/65153","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=65153"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/65153\/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=65153"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=65153"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=65153"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}