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.OpenCurrentDataBase("C:\fso\Test.mdb")
$Access.DoCmd.TransferSpreadSheet($Import, $SpreadSheetType, $TableName, $FileName,$HasFieldNames,$Range)
$Access.Quit()

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()
1273012407

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

$Access.OpenCurrentDataBase("C:\fso\Test.mdb")

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

AddMenu

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

ApplyFilter

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

Beep

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

CancelEvent

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

ClearMacroError

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

Close

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

CloseDatabase

Closes the current database.

CopyDatabaseFile

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

CopyObject

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

DeleteObject

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

DoMenuItem

Displays the appropriate menu or toolbar command for Microsoft Access.

Echo

Carries out the Echo action in Visual Basic.

FindNext

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

FindRecord

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

GoToControl

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

GoToPage

Carries out the GoToPage action in Visual Basic.

GoToRecord

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

Hourglass

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

LockNavigationPane

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

Maximize

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

Minimize

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

MoveSize

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

NavigateTo

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

OpenDataAccessPage

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

OpenDiagram

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

OpenForm

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

OpenFunction

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

OpenModule

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

OpenQuery

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

OpenReport

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

OpenStoredProcedure

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

OpenTable

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

OpenView

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

OutputTo

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

Printout

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

Quit

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

Rename

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

RepaintObject

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

Requery

Carries out the Requery action in Visual Basic.

Restore

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

RunCommand

The RunCommand method runs a built-in command.

RunMacro

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

RunSavedImportExport

Runs a saved import or export specification.

RunSQL

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

Save

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

SearchForRecord

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

SelectObject

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

SendObject

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

SetDisplayedCategories

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

SetMenuItem

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

SetProperty

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

SetWarnings

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

ShowAllRecords

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

ShowToolbar

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

SingleStep

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

StartNewWorkFlow

Displays the Start New Workflow dialog box.

TransferDatabase

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

TransferSharePointList

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

TransferSpreadsheet

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

TransferSQLDatabase

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

TransferText

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

WorkflowTasks

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 *

TransferType

AcDataTransferType

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

SpreadsheetType

AcSpreadSheetType

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

TableName

Variant

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.

FileName

Variant

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

HasFieldNames

Variant

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.

Range

Variant

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.

UseOA

Variant

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 *

acExport

1

The data is exported.

acImport

0

(Default) The data is imported.

acLink

2

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 *

acSpreadsheetTypeExcel3

0

Microsoft Excel 3.0 format

acSpreadsheetTypeExcel4

6

Microsoft Excel 4.0 format

acSpreadsheetTypeExcel5

5

Microsoft Excel 5.0 format

acSpreadsheetTypeExcel7

5

Microsoft Excel 95 format

acSpreadsheetTypeExcel8

8

Microsoft Excel 97 format

acSpreadsheetTypeExcel9

8

Microsoft Excel 2000 format

acSpreadsheetTypeLotusWJ2

4

Japanese version only

acSpreadsheetTypeLotusWK1

2

Lotus 1-2-3 WK1 format

acSpreadsheetTypeLotusWK3

3

Lotus 1-2-3 WK3 format

acSpreadsheetTypeLotusWK4

7

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:

$Access.DoCmd.TransferSpreadSheet($AcDataTransferType::acImport,
$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:

$Access.Quit()

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