Hey, Scripting Guy! How Can I Import Data into Office Access from Office Excel?

Hey, Scripting Guy! How Can I Import Data into Office Access from Office Excel?

  • Comments 1
  • Likes

Hey, Scripting Guy! Question

Hey, Scripting Guy! I need to do hardware inventory on all our workstations and on all our servers. I know System Center Configuration Manager can do this and a whole lot more. We actually have it budgeted for deployment next year. However, that does not help me now. We had a summer intern that went around and typed a lot of information such as make, model, and serial number into an Office Excel spreadsheet. In fact that is about all he did was go around typing information into an Office Excel spreadsheet, and it has been a big help to us. But I would like more information, and I do not have the time or the patience to type a bunch of information into a spreadsheet. What I would really like is to have the information in a database instead of an Office Excel spreadsheet. Can you figure out a way to import the data from Office Excel into Office Access?

- JS

SpacerHey, Scripting Guy! Answer

Hi JS,

When the trumpet call split the air with the message that the minister of state was within sight and that Leonore had saved her husband, the audience of Fidelio readily appreciated they had just witnessed one of the great scenes of opera. It is a defining moment when one has arrived at perfect clarity, unity of purpose, and symmetry as Beethoven did when he wrote his only opera.

This week we will be talking about scripting Microsoft Office Access. The Databases hub is a great jumping-off point because it highlights the available Office Access scripts we have in the TechNet Script Center. There are also links to the Office Access scripts we have in the Script Center Script Repository and on the Community-Submitted Scripts Center. When taken as a whole, there are tons of examples for you to review. For information about downloading and installing Windows PowerShell, you can go to the Windows PowerShell Scripting Hub.

JS, you have had such a defining moment in boiling down the essence of your needs. We will be glad to be your Leonore and save you from spreadsheet prison.

The ImportSpreadSheetToAccess.ps1 script is seen here.

$Import = 0
$SpreadSheetType = 8
$TableName = "MyTest"
$FileName = "C:\Fso\Test.xls"
$HasFieldNames = $True
$Range = "A1:C29"
$access = New-Object -ComObject Access.Application
$Access.DoCmd.TransferSpreadSheet($Import, $SpreadSheetType, $TableName, $FileName,$HasFieldNames,$Range)

In the ImportSpreadSheetToAccess.ps1 script, we import an Office Excel spreadsheet containing the data, such as seen here:

Image of the imported Office Excel spreadsheet


The first thing we do in this script is define a few variables and assign the appropriate values to them. These values will all be used when we get to the DoCmd.TransferSpreadSheet method. For now, we are simply assigning various strings and integers to some variables. In Windows PowerShell, all variables begin with a dollar sign. We will talk about these variable values in just a little bit.

$Import = 0
$SpreadSheetType = 8
$TableName = "MyTest"
$FileName = "C:\Fso\Test.xls"
$HasFieldNames = $True
$Range = "A1:C29"

We next create an instance of the Access.Application object. This is the main object we use when working with the Access automation model. The Access.Application object is documented on MSDN. To create the Access.Application object, we use the New-Object cmdlet and the ComObject parameter. The ComObject parameter tells the New-Object cmdlet that we are creating a COM object. If you are using the same kind of object you used in an old VBScript script, it is a COM object. The code that creates this object is seen here:

$access = New-Object -ComObject Access.Application

If you are not sure whether you have a COM object and do not want to see an error, use the ComObject parameter. If you actually have a .NET Framework class, the ComObject parameter will be ignored. Pretty cool, huh? You want an example? Okay. System.Random is a .NET Framework class, but as you can see here, we use New-Object and the ComObject parameter and pipe the results to Get-Member. We see the members of the System.Random class. We next assign the object to the variable $a and call the Next method, generating the next random number. This proves that Windows PowerShell ignores the ComObject parameter when working with a .NET Framework class:

PS C:\> New-Object -ComObject System.Random | Get-Member

   TypeName: System.Random

Name        MemberType Definition
----        ---------- ----------
Equals      Method     System.Boolean Equals(Object obj)
GetHashCode Method     System.Int32 GetHashCode()
GetType     Method     System.Type GetType()
Next        Method     System.Int32 Next(), System.Int32 Next(Int32 minValue, Int32 maxValue), System.Int32 Next(Int...
NextBytes   Method     System.Void NextBytes(Byte[] buffer)
NextDouble  Method     System.Double NextDouble()
ToString    Method     System.String ToString()

PS C:\> $a = New-Object -ComObject System.Random
PS C:\> $a.Next()

After have created the Access.Application object, we use the OpenCurrentDataBase method to open the database (no surprise there):


After we have opened the database, we use the DoCmd object. The DoCmd object is a remarkable object that has the ability to perform a large number of different commands. The methods are seen in Table 1. More information about the DoCmd object can be found on MSDN.

Table 1 Access Application object DoCmd methods
Name Description


The AddMenu method carries out the AddMenu action in Visual Basic.


The ApplyFilter method carries out the ApplyFilter action in Visual Basic.


The Beep method carries out the Beep action in Visual Basic.


The CancelEvent method carries out the CancelEvent action in Visual Basic.


Removes information about an error that is stored in the MacroError object.


The Close method carries out the Close action in Visual Basic.


Closes the current database.


Copies the database connected to the current project to a Microsoft SQL Server database file for export.


The CopyObject method carries out the CopyObject action in Visual Basic.


The DeleteObject method carries out the DeleteObject action in Visual Basic.


Displays the appropriate menu or toolbar command for Microsoft Access.


Carries out the Echo action in Visual Basic.


The FindNext method carries out the FindNext action in Visual Basic.


The FindRecord method carries out the FindRecord action in Visual Basic.


The GoToControl method carries out the GoToControl action action in Visual Basic.


Carries out the GoToPage action in Visual Basic.


The GoToRecord method carries out the GoToRecord action in Visual Basic.


The Hourglass method carries out the Hourglass action in Visual Basic.


You can use the LockNavigationPane action to prevent users from deleting database objects that are displayed in the Navigation Pane.


The Maximize method carries out the Maximize action in Visual Basic.


The Minimize method carries out the Minimize action in Visual Basic.


The MoveSize method carries out the MoveSize action in Visual Basic.


You can use the NavigateTo method to control the display of database objects in the Navigation Pane.


The OpenDataAccessPage method carries out the OpenDataAccessPage action in Visual Basic.


The OpenDiagram method carries out the OpenDiagram action in Visual Basic.


The OpenForm method carries out the OpenForm action in Visual Basic.


Opens a user-defined function in a Microsoft SQL Server database for viewing in Microsoft Office Access.


The OpenModule method carries out the OpenModule action in Visual Basic.


The OpenQuery method carries out the OpenQuery action in Visual Basic.


The OpenReport method carries out the OpenReport action in Visual Basic.


The OpenStoredProcedure method carries out the OpenStoredProcedure action in Visual Basic.


The OpenTable method carries out the OpenTable action in Visual Basic.


The OpenView method carries out the OpenView action in Visual Basic.


The OutputTo method carries out the OutputTo action in Visual Basic.


The PrintOut method carries out the PrintOut action in Visual Basic.


The Quit method quits Microsoft Access. You can select one of several options for saving a database object before quitting.


The Rename method carries out the Rename action in Visual Basic.


The RepaintObject method carries out the RepaintObject action in Visual Basic.


Carries out the Requery action in Visual Basic.


The Restore method carries out the Restore action in Visual Basic.


The RunCommand method runs a built-in command.


The RunMacro method carries out the RunMacro action in Visual Basic.


Runs a saved import or export specification.


The RunSQL method carries out the RunSQL action in Visual Basic.


The Save method carries out the Save action in Visual Basic.


You can use the SearchForRecord method to search for a specific record in a table, query, form, or report.


The SelectObject method carries out the SelectObject action in Visual Basic.


The SendObject method carries out the SendObject action in Visual Basic.


Specifies which categories are displayed under Navigate to Category in the title bar of the Navigation Pane.


The SetMenuItem method carries out the SetMenuItem action in Visual Basic.


The SetProperty method carries out the SetProperty action in Visual Basic.


The SetWarnings method carries out the SetWarnings action in Visual Basic.


The ShowAllRecords method carries out the ShowAllRecords action in Visual Basic.


The ShowToolbar method carries out the ShowToolbar action in Visual Basic.


Pauses macro execution and opens the Macro Single Step dialog box.


Displays the Start New Workflow dialog box.


The TransferDatabase method carries out the TransferDatabase action in Visual Basic.


You can use the TransferSharePointList method to import or link data from a 1st_WSS_3 site.


The TransferSpreadsheet method carries out the TransferSpreadsheet action in Visual Basic.


Transfers the entire specified Microsoft SQL Server database to another SQL Server database.


The TransferText method carries out the TransferText action in Visual Basic.


Displays the Workflow Tasks dialog box.

The TransferSpreadSheet method from the DoCmd object has a signature that accepts seven optional arguments. The argument names, data types, and descriptions are seen in Table 2.

Table 2 TransferSpreadSheet Method arguments
Name *
Data Type *
Description *



The type of transfer you want to make. The default value is acImport.



The type of spreadsheet to import from, export to, or link to.



A string expression that is the name of the Microsoft Office Access table you want to import spreadsheet data into, export spreadsheet data from, or link spreadsheet data to, or the Office Access select query whose results you want to export to a spreadsheet.



A string expression that's the file name and path of the spreadsheet you want to import from, export to, or link to.



Use True (–1) to use the first row of the spreadsheet as field names when importing or linking. Use False (0) to treat the first row of the spreadsheet as normal data. If you leave this argument blank, the default (False) is assumed. When you export Access table or select query data to a spreadsheet, the field names are inserted into the first row of the spreadsheet, no matter what you enter for this argument.



A string expression that's a valid range of cells or the name of a range in the spreadsheet. This argument applies only to importing. Leave this argument blank to import the entire spreadsheet. When you export to a spreadsheet, you must leave this argument blank. If you enter a range, the export will fail.



This argument is not supported.

The TransferSpreadSheet method signature is seen here:

expression.TransferSpreadsheet(TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA)

The first argument is the TransferType argument, which can take three potential values. These values need to be an instance of the AcDataTransferType enumeration. These enumeration values are seen in Table 3. In our script, we use the variable named $import and gave it the value of 1. From Table 3 we can tell that a value of 1 means the data is exported.

Table 3 TransferSpreadSheet method TransferType enumeration values
Name *
Value *
Description *



The data is exported.



(Default) The data is imported.



The database is linked to the specified data source.

The second argument is the Spreadsheet argument. There are 10 different enumerations listed. but only 8 different values. You will also notice from Table 4 that the Office 2007 formatted spreadsheet is not a supported format. This is why our spreadsheet is an .xls file and was saved in Office Excel 2000 format.

Table 4 TransferSpreadSheet method SpreadsheetType enumeration values
Name *
Value *
Description *



Microsoft Excel 3.0 format



Microsoft Excel 4.0 format



Microsoft Excel 5.0 format



Microsoft Excel 95 format



Microsoft Excel 97 format



Microsoft Excel 2000 format



Japanese version only



Lotus 1-2-3 WK1 format



Lotus 1-2-3 WK3 format



Lotus 1-2-3 WK4 format

We could have used the Enumerations directly by first loading the Access Interop assembly. To do this in Windows PowerShell 1.0, we can use the LoadWithPartialName static method from the Reflection.Assembly .NET Framework class. After we have loaded the Interop assembly, we can create the two enumeration classes as seen here:

[Reflection.Assembly]::LoadWithPartialName("Microsoft.Office.Interop.Access") | Out-Null
$AcDataTransferType = "Microsoft.Office.Interop.Access.AcDataTransferType" -as [type]
$AcSpreadSheetType = "Microsoft.Office.Interop.Access.AcSpreadSheetType" -as [type]

If we had done that, we could then call the TransferSpreadSheet method and used the enumeration values directly; the TransferSpreadSheet method call would have looked like the following:

$AcSpreadSheetType::AcSpreadSheetTypeExcel9, $TableName, $FileName,$HasFieldNames,$Range)

Instead, for simplicity’s sake, we used the methodology more in keeping with the VBScript version of this script. The TableName is the table name in the Office Access database, and the FileName argument is the path and name of the Office Excel spreadsheet to import. The HasFieldNames argument is a Boolean value that indicates whether or not the first row of the Office Excel spreadsheet contains the field names. The range is used to specify the range in the Office Excel spreadsheet to import. The first number indicates the upper left cell reference, and the second number is the lower right reference. The method call is shown here:

$Access.DoCmd.TransferSpreadSheet($Import, $SpreadSheetType, $TableName, $FileName,$HasFieldNames,$Range)

If you have an autonumber field in your database, make sure you do not try to import a row from the Office Excel spreadsheet with the same field name or you will get an error. Also pay attention to the data types expected by the fields in the Office Access database, and make sure that your import data matches up. For instance, you will get an error trying to import a string into a field that expects a date type.

After we have imported the spreadsheet, we use the Quit method to close out Access.Application object. This is a very important step that will prevent multiple copies of Office Access from running:


The completed Office Access database with the newly imported data is seen here:

Image of the completed Office Access database with newly imported data


Well, JS, this concludes writing to an Office Access database. It also concludes our Office Access Database Week articles. Join us tomorrow for Quick-Hits Friday. 

Ed Wilson and Craig Liebendorfer, Scripting Guys

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment