{"id":54353,"date":"2009-02-19T21:15:00","date_gmt":"2009-02-19T21:15:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2009\/02\/19\/hey-scripting-guy-how-can-i-import-data-into-office-access-from-office-excel\/"},"modified":"2009-02-19T21:15:00","modified_gmt":"2009-02-19T21:15:00","slug":"hey-scripting-guy-how-can-i-import-data-into-office-access-from-office-excel","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/hey-scripting-guy-how-can-i-import-data-into-office-access-from-office-excel\/","title":{"rendered":"Hey, Scripting Guy! How Can I Import Data into Office Access from Office Excel?"},"content":{"rendered":"

\"Hey, <\/h2>\n

Hey, Scripting Guy! I need to do hardware inventory on all our workstations and on all our servers. I know System Center Configuration Manager<\/a> 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?<\/p>\n

– JS<\/p>\n

\"Spacer\"\"Hey,<\/p>\n

Hi JS,<\/p>\n

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<\/a> 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<\/a> did when he wrote his only opera. <\/p>\n

var sortedCol = new Array(); var sortFactor = new Array(); var initialSort = new Array(); function rowData(index, value) { this.index = index; this.value = value; } var compareFactor; function compareValues(a, b) { if (a.value b.value) return compareFactor; return 0; } function sortByColumn(tableIndex, tdName, order) { var td = document.getElementById(tdName); var cols = td.parentElement.children; var col = -1; for (var i=0; i < cols.length; i++) if (cols[i] == td) col = i; if (col == -1) return; if (td != sortedCol[tableIndex] && sortedCol[tableIndex] != null) { sortedCol[tableIndex].className = "sortable"; sortedCol[tableIndex].getElementsByTagName("IMG")[0].src="http:\/\/blogs.technet.com\/library\/gallery\/templates\/MNP2.GenericArticle\/..\/MNP2.Common\/images\/sortNone.gif"; } if (td == sortedCol[tableIndex]) { sortFactor[tableIndex] = -sortFactor[tableIndex]; } else { sortedCol[tableIndex] = td; if (!initialSort) sortFactor[tableIndex] = 1; td.className = "selectedSort"; } if (sortFactor[tableIndex] == 1) td.getElementsByTagName("IMG")[0].src="http:\/\/blogs.technet.com\/library\/gallery\/templates\/MNP2.GenericArticle\/..\/MNP2.Common\/images\/sortAscending.gif"; else td.getElementsByTagName("IMG")[0].src="http:\/\/blogs.technet.com\/library\/gallery\/templates\/MNP2.GenericArticle\/..\/MNP2.Common\/images\/sortDescending.gif"; initialSort = false; var thead = td.parentElement.parentElement; var tbody = thead.nextSibling; var table = thead.parentElement; var rows = tbody.getElementsByTagName("TR"); var data = new Array(); var hasEvenRowShading = false; for (var i=0; i < rows.length; i++) if (rows[i].parentElement == tbody) { if (rows[i].className == "evenRecord") hasEvenRowShading = true; var cell = rows[i].children[col]; var value = cell.getAttribute("value"); if (value == null) value = cell.innerText; if (order == 'numerical') { var filterChars = new Array("$", "\u00a2", "\u00a3", "\u00a4", "\u00a5", "₣", "₤", "₧", "₪", "₫", "€", ",", "."); for (var j=0; j < filterChars.length; j++) value = value.replace(filterChars[j], ""); value = parseFloat(value); if (isNaN(value)) value = Number.NEGATIVE_INFINITY; } if (order == 'chronological') value = Date.parse(value); data[i] = new rowData(i, value); } compareFactor = sortFactor[tableIndex]; data = data.sort(compareValues); var tbody2 = document.createElement("TBODY"); for (var i=0; i < data.length; i++) if (data[i]) { var row = rows[data[i].index].cloneNode(true); if ((i % 2 == 0) || !hasEvenRowShading) row.className = "record"; else row.className = "evenRecord"; tbody2.appendChild(row); } table.replaceChild(tbody2, tbody); } <\/p>\n\n<\/thead>\n\n\n
\n

This week we will be talking about scripting Microsoft Office Access. The Databases hub<\/a> 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<\/a> and on the Community-Submitted Scripts Center<\/a>. 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<\/a>.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

<\/div>\n

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.<\/p>\n

The ImportSpreadSheetToAccess.ps1<\/b> script is seen here.<\/p>\n

$Import = 0\n$SpreadSheetType = 8\n$TableName = \"MyTest\"\n$FileName = \"C:\\Fso\\Test.xls\"\n$HasFieldNames = $True\n$Range = \"A1:C29\"\n$access = New-Object -ComObject Access.Application\n$Access.OpenCurrentDataBase(\"C:\\fso\\Test.mdb\")\n$Access.DoCmd.TransferSpreadSheet($Import, $SpreadSheetType, $TableName, $FileName,$HasFieldNames,$Range)\n$Access.Quit()\n<\/pre>\n
In the ImportSpreadSheetToAccess.ps1<\/b> script, we import an Office Excel spreadsheet containing the data, such as seen here:<\/p>\n
\"Image<\/p>\n
 <\/p>\n
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<\/b> 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.<\/p>\n
$Import = 0\n$SpreadSheetType = 8\n$TableName = \"MyTest\"\n$FileName = \"C:\\Fso\\Test.xls\"\n$HasFieldNames = $True\n$Range = \"A1:C29\"\n<\/pre>\n
We next create an instance of the Access.Application<\/b> object. This is the main object we use when working with the Access automation model. The Access.Application<\/b> object is documented on MSDN<\/a>. To create the Access.Application<\/b> object, we use the New-Object<\/b> cmdlet and the ComObject<\/b> parameter. The ComObject<\/b> parameter tells the New-Object<\/b> 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:<\/p>\n
$access = New-Object -ComObject Access.Application<\/pre>\n
If you are not sure whether you have a COM object and do not want to see an error, use the ComObject<\/b> parameter. If you actually have a .NET Framework class, the ComObject<\/b> parameter will be ignored. Pretty cool, huh? You want an example? Okay. System.Random<\/b> is a .NET Framework class, but as you can see here, we use New-Object<\/b> and the ComObject<\/b> parameter and pipe the results to Get-Member<\/b>. We see the members of the System.Random<\/b> class. We next assign the object to the variable $a<\/b> and call the Next<\/b> method, generating the next random number. This proves that Windows PowerShell ignores the ComObject<\/b> parameter when working with a .NET Framework class:<\/p>\n
PS C:\\> New-Object -ComObject System.Random | Get-Member\n   TypeName: System.Random\nName        MemberType Definition\n----        ---------- ----------\nEquals      Method     System.Boolean Equals(Object obj)\nGetHashCode Method     System.Int32 GetHashCode()\nGetType     Method     System.Type GetType()\nNext        Method     System.Int32 Next(), System.Int32 Next(Int32 minValue, Int32 maxValue), System.Int32 Next(Int...\nNextBytes   Method     System.Void NextBytes(Byte[] buffer)\nNextDouble  Method     System.Double NextDouble()\nToString    Method     System.String ToString()\nPS C:\\> $a = New-Object -ComObject System.Random\nPS C:\\> $a.Next()\n1273012407\n<\/pre>\n
After have created the Access.Application<\/b> object, we use the OpenCurrentDataBase<\/b> method to open the database (no surprise there):<\/p>\n
$Access.OpenCurrentDataBase(\"C:\\fso\\Test.mdb\")<\/pre>\n
After we have opened the database, we use the DoCmd<\/b> object. The DoCmd<\/b> object is a remarkable object that has the ability to perform a large number of different commands. The methods are seen in Table 1<\/b>. More information about the DoCmd<\/b> object can be found on MSDN<\/a>.<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Table 1 Access Application object DoCmd methods<\/td>\n<\/tr>\n
Name<\/td>\nDescription<\/td>\n<\/tr>\n<\/thead>\n
\n
AddMenu<\/p>\n<\/td>\n
\n
The AddMenu<\/b> method carries out the AddMenu<\/b> action in Visual Basic.<\/p>\n<\/td>\n<\/tr>\n
\n
ApplyFilter<\/p>\n<\/td>\n
\n
The ApplyFilter<\/b> method carries out the ApplyFilter<\/b> action in Visual Basic.<\/p>\n<\/td>\n<\/tr>\n
\n
Beep<\/p>\n<\/td>\n
\n
The Beep<\/b> method carries out the Beep<\/b> action in Visual Basic.<\/p>\n<\/td>\n<\/tr>\n
\n
CancelEvent<\/p>\n<\/td>\n
\n
The CancelEvent<\/b> method carries out the CancelEvent<\/b> action in Visual Basic.<\/p>\n<\/td>\n<\/tr>\n
\n
ClearMacroError<\/p>\n<\/td>\n
\n
Removes information about an error that is stored in the MacroError<\/b> object.<\/p>\n<\/td>\n<\/tr>\n
\n
Close<\/p>\n<\/td>\n
\n
The Close<\/b> method carries out the Close<\/b> action in Visual Basic.<\/p>\n<\/td>\n<\/tr>\n
\n
CloseDatabase<\/p>\n<\/td>\n
\n
Closes the current database.<\/p>\n<\/td>\n<\/tr>\n
\n
CopyDatabaseFile<\/p>\n<\/td>\n
\n
Copies the database connected to the current project to a Microsoft SQL Server database file for export.<\/p>\n<\/td>\n<\/tr>\n
\n
CopyObject<\/p>\n<\/td>\n
\n
The CopyObject<\/b> method carries out the CopyObject<\/b> action in Visual Basic.<\/p>\n<\/td>\n<\/tr>\n
\n
DeleteObject<\/p>\n<\/td>\n
\n
The DeleteObject<\/b> method carries out the DeleteObject<\/b> action in Visual Basic.<\/p>\n<\/td>\n<\/tr>\n
\n
DoMenuItem<\/p>\n<\/td>\n
\n
Displays the appropriate menu or toolbar command for Microsoft Access.<\/p>\n<\/td>\n<\/tr>\n
\n
Echo<\/p>\n<\/td>\n
\n
Carries out the Echo<\/b> action in Visual Basic.<\/p>\n<\/td>\n<\/tr>\n
\n
FindNext<\/p>\n<\/td>\n
\n
The FindNext<\/b> method carries out the FindNext<\/b> action in Visual Basic.<\/p>\n<\/td>\n<\/tr>\n
\n
FindRecord<\/p>\n<\/td>\n
\n
The FindRecord<\/b> method carries out the FindRecord<\/b> action in Visual Basic.<\/p>\n<\/td>\n<\/tr>\n
\n
GoToControl<\/p>\n<\/td>\n
\n
The GoToControl<\/b> method carries out the GoToControl<\/b> action action in Visual Basic.<\/p>\n<\/td>\n<\/tr>\n
\n
GoToPage<\/p>\n<\/td>\n
\n
Carries out the GoToPage<\/b> action in Visual Basic.<\/p>\n<\/td>\n<\/tr>\n
\n
GoToRecord<\/p>\n<\/td>\n
\n
The GoToRecord<\/b> method carries out the GoToRecord<\/b> action in Visual Basic.<\/p>\n<\/td>\n<\/tr>\n
\n
Hourglass<\/p>\n<\/td>\n
\n
The Hourglass<\/b> method carries out the Hourglass<\/b> action in Visual Basic.<\/p>\n<\/td>\n<\/tr>\n
\n
LockNavigationPane<\/p>\n<\/td>\n
\n
You can use the LockNavigationPane<\/b> action to prevent users from deleting database objects that are displayed in the Navigation Pane.<\/p>\n<\/td>\n<\/tr>\n
\n
Maximize<\/p>\n<\/td>\n
\n
The Maximize<\/b> method carries out the Maximize<\/b> action in Visual Basic.<\/p>\n<\/td>\n<\/tr>\n
\n
Minimize<\/p>\n<\/td>\n
\n
The Minimize<\/b> method carries out the Minimize<\/b> action in Visual Basic.<\/p>\n<\/td>\n<\/tr>\n
\n
MoveSize<\/p>\n<\/td>\n
\n
The MoveSize<\/b> method carries out the MoveSize<\/b> action in Visual Basic.<\/p>\n<\/td>\n<\/tr>\n
\n
NavigateTo<\/p>\n<\/td>\n
\n
You can use the NavigateTo<\/b> method to control the display of database objects in the Navigation Pane.<\/p>\n<\/td>\n<\/tr>\n
\n
OpenDataAccessPage<\/p>\n<\/td>\n
\n
The OpenDataAccessPage<\/b> method carries out the OpenDataAccessPage<\/b> action in Visual Basic.<\/p>\n<\/td>\n<\/tr>\n
\n
OpenDiagram<\/p>\n<\/td>\n
\n
The OpenDiagram<\/b> method carries out the OpenDiagram<\/b> action in Visual Basic.<\/p>\n<\/td>\n<\/tr>\n
\n
OpenForm<\/p>\n<\/td>\n
\n
The OpenForm<\/b> method carries out the OpenForm<\/b> action in Visual Basic.<\/p>\n<\/td>\n<\/tr>\n
\n
OpenFunction<\/p>\n<\/td>\n
\n
Opens a user-defined function in a Microsoft SQL Server database for viewing in Microsoft Office Access.<\/p>\n<\/td>\n<\/tr>\n
\n
OpenModule<\/p>\n<\/td>\n
\n
The OpenModule<\/b> method carries out the OpenModule<\/b> action in Visual Basic.<\/p>\n<\/td>\n<\/tr>\n
\n
OpenQuery<\/p>\n<\/td>\n
\n
The OpenQuery<\/b> method carries out the OpenQuery<\/b> action in Visual Basic.<\/p>\n<\/td>\n<\/tr>\n
\n
OpenReport<\/p>\n<\/td>\n
\n
The OpenReport<\/b> method carries out the OpenReport<\/b> action in Visual Basic.<\/p>\n<\/td>\n<\/tr>\n
\n
OpenStoredProcedure<\/p>\n<\/td>\n
\n
The OpenStoredProcedure<\/b> method carries out the OpenStoredProcedure<\/b> action in Visual Basic.<\/p>\n<\/td>\n<\/tr>\n
\n
OpenTable<\/p>\n<\/td>\n
\n
The OpenTable<\/b> method carries out the OpenTable<\/b> action in Visual Basic.<\/p>\n<\/td>\n<\/tr>\n
\n
OpenView<\/p>\n<\/td>\n
\n
The OpenView<\/b> method carries out the OpenView<\/b> action in Visual Basic.<\/p>\n<\/td>\n<\/tr>\n
\n
OutputTo<\/p>\n<\/td>\n
\n
The OutputTo<\/b> method carries out the OutputTo<\/b> action in Visual Basic.<\/p>\n<\/td>\n<\/tr>\n
\n
Printout<\/p>\n<\/td>\n
\n
The PrintOut<\/b> method carries out the PrintOut<\/b> action in Visual Basic.<\/p>\n<\/td>\n<\/tr>\n
\n
Quit<\/p>\n<\/td>\n
\n
The Quit<\/b> method quits Microsoft Access. You can select one of several options for saving a database object before quitting.<\/p>\n<\/td>\n<\/tr>\n
\n
Rename<\/p>\n<\/td>\n
\n
The Rename<\/b> method carries out the Rename<\/b> action in Visual Basic.<\/p>\n<\/td>\n<\/tr>\n
\n
RepaintObject<\/p>\n<\/td>\n
\n
The RepaintObject<\/b> method carries out the RepaintObject<\/b> action in Visual Basic.<\/p>\n<\/td>\n<\/tr>\n
\n
Requery<\/p>\n<\/td>\n
\n
Carries out the Requery<\/b> action in Visual Basic.<\/p>\n<\/td>\n<\/tr>\n
\n
Restore<\/p>\n<\/td>\n
\n
The Restore<\/b> method carries out the Restore<\/b> action in Visual Basic.<\/p>\n<\/td>\n<\/tr>\n
\n
RunCommand<\/p>\n<\/td>\n
\n
The RunCommand<\/b> method runs a built-in command.<\/p>\n<\/td>\n<\/tr>\n
\n
RunMacro<\/p>\n<\/td>\n
\n
The RunMacro<\/b> method carries out the RunMacro<\/b> action in Visual Basic.<\/p>\n<\/td>\n<\/tr>\n
\n
RunSavedImportExport<\/p>\n<\/td>\n
\n
Runs a saved import or export specification.<\/p>\n<\/td>\n<\/tr>\n
\n
RunSQL<\/p>\n<\/td>\n
\n
The RunSQL<\/b> method carries out the RunSQL<\/b> action in Visual Basic.<\/p>\n<\/td>\n<\/tr>\n
\n
Save<\/p>\n<\/td>\n
\n
The Save<\/b> method carries out the Save<\/b> action in Visual Basic.<\/p>\n<\/td>\n<\/tr>\n
\n
SearchForRecord<\/p>\n<\/td>\n
\n
You can use the SearchForRecord<\/b> method to search for a specific record in a table, query, form, or report.<\/p>\n<\/td>\n<\/tr>\n
\n
SelectObject<\/p>\n<\/td>\n
\n
The SelectObject<\/b> method carries out the SelectObject<\/b> action in Visual Basic.<\/p>\n<\/td>\n<\/tr>\n
\n
SendObject<\/p>\n<\/td>\n
\n
The SendObject<\/b> method carries out the SendObject<\/b> action in Visual Basic.<\/p>\n<\/td>\n<\/tr>\n
\n
SetDisplayedCategories<\/p>\n<\/td>\n
\n
Specifies which categories are displayed under Navigate to Category in the title bar of the Navigation Pane.<\/p>\n<\/td>\n<\/tr>\n
\n
SetMenuItem<\/p>\n<\/td>\n
\n
The SetMenuItem<\/b> method carries out the SetMenuItem<\/b> action in Visual Basic.<\/p>\n<\/td>\n<\/tr>\n
\n
SetProperty<\/p>\n<\/td>\n
\n
The SetProperty<\/b> method carries out the SetProperty<\/b> action in Visual Basic.<\/p>\n<\/td>\n<\/tr>\n
\n
SetWarnings<\/p>\n<\/td>\n
\n
The SetWarnings<\/b> method carries out the SetWarnings<\/b> action in Visual Basic.<\/p>\n<\/td>\n<\/tr>\n
\n
ShowAllRecords<\/p>\n<\/td>\n
\n
The ShowAllRecords<\/b> method carries out the ShowAllRecords<\/b> action in Visual Basic.<\/p>\n<\/td>\n<\/tr>\n
\n
ShowToolbar<\/p>\n<\/td>\n
\n
The ShowToolbar<\/b> method carries out the ShowToolbar<\/b> action in Visual Basic.<\/p>\n<\/td>\n<\/tr>\n
\n
SingleStep<\/p>\n<\/td>\n
\n
Pauses macro execution and opens the Macro Single Step dialog box.<\/p>\n<\/td>\n<\/tr>\n
\n
StartNewWorkFlow<\/p>\n<\/td>\n
\n
Displays the Start New Workflow dialog box.<\/p>\n<\/td>\n<\/tr>\n
\n
TransferDatabase<\/p>\n<\/td>\n
\n
The TransferDatabase<\/b> method carries out the TransferDatabase<\/b> action in Visual Basic.<\/p>\n<\/td>\n<\/tr>\n
\n
TransferSharePointList<\/p>\n<\/td>\n
\n
You can use the TransferSharePointList<\/b> method to import or link data from a 1st_WSS_3 site.<\/p>\n<\/td>\n<\/tr>\n
\n
TransferSpreadsheet<\/p>\n<\/td>\n
\n
The TransferSpreadsheet<\/b> method carries out the TransferSpreadsheet<\/b> action in Visual Basic.<\/p>\n<\/td>\n<\/tr>\n
\n
TransferSQLDatabase<\/p>\n<\/td>\n
\n
Transfers the entire specified Microsoft SQL Server database to another SQL Server database.<\/p>\n<\/td>\n<\/tr>\n
\n
TransferText<\/p>\n<\/td>\n
\n
The TransferText<\/b> method carries out the TransferText<\/b> action in Visual Basic.<\/p>\n<\/td>\n<\/tr>\n
\n
WorkflowTasks<\/p>\n<\/td>\n
\n
Displays the Workflow Tasks dialog box.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/div>\n
The TransferSpreadSheet<\/b> method from the DoCmd<\/b> object has a signature that accepts seven optional arguments. The argument names, data types, and descriptions are seen in Table 2<\/b>.<\/p>\n
        var tableId = “EIEAE”;        sortedCol[tableId] = null;        sortFactor[tableId] = 1;        initialSort[tableId] = true;      <\/p>\n\n\n\n\n
Table 2 TransferSpreadSheet Method arguments<\/td>\n<\/tr>\n
<\/p>\n\n\n\n
Name<\/td>\n\"*\"<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/a><\/td>\n
<\/p>\n\n\n\n
Data Type<\/td>\n\"*\"<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/a><\/td>\n
<\/p>\n\n\n\n\n\n\n\n\n\n\n\n
Description<\/td>\n\"*\"<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/a><\/td>\n<\/tr>\n<\/thead>\n
\n
TransferType<\/p>\n<\/td>\n
\n
AcDataTransferType<\/p>\n<\/td>\n
\n
The type of transfer you want to make. The default value is acImport.<\/p>\n<\/td>\n<\/tr>\n
\n
SpreadsheetType<\/p>\n<\/td>\n
\n
AcSpreadSheetType<\/p>\n<\/td>\n
\n
The type of spreadsheet to import from, export to, or link to.<\/p>\n<\/td>\n<\/tr>\n
\n
TableName<\/p>\n<\/td>\n
\n
Variant<\/p>\n<\/td>\n
\n
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.<\/p>\n<\/td>\n<\/tr>\n
\n
FileName<\/p>\n<\/td>\n
\n
Variant<\/p>\n<\/td>\n
\n
A string expression that’s the file name and path of the spreadsheet you want to import from, export to, or link to.<\/p>\n<\/td>\n<\/tr>\n
\n
HasFieldNames<\/p>\n<\/td>\n
\n
Variant<\/p>\n<\/td>\n
\n
Use True (\u20131) 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.<\/p>\n<\/td>\n<\/tr>\n
\n
Range<\/p>\n<\/td>\n
\n
Variant<\/p>\n<\/td>\n
\n
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.<\/p>\n<\/td>\n<\/tr>\n
\n
UseOA<\/p>\n<\/td>\n
\n
Variant<\/p>\n<\/td>\n
\n
This argument is not supported.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/div>\n
The TransferSpreadSheet<\/b> method signature is seen here:<\/p>\n
expression.TransferSpreadsheet(TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA)<\/pre>\n
The first argument is the TransferType<\/b> argument, which can take three potential values. These values need to be an instance of the AcDataTransferType<\/b> enumeration. These enumeration values are seen in Table 3<\/b>. In our script, we use the variable named $import<\/b> and gave it the value of 1. From Table 3<\/b> we can tell that a value of 1 means the data is exported.<\/p>\n
        var tableId = “EBHAE”;        sortedCol[tableId] = null;        sortFactor[tableId] = 1;        initialSort[tableId] = true;      <\/p>\n\n\n\n\n
Table 3 TransferSpreadSheet method TransferType enumeration values<\/td>\n<\/tr>\n
<\/p>\n\n\n\n
Name<\/td>\n\"*\"<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/a><\/td>\n
<\/p>\n\n\n\n
Value<\/td>\n\"*\"<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/a><\/td>\n
<\/p>\n\n\n\n\n\n\n\n
Description<\/td>\n\"*\"<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/a><\/td>\n<\/tr>\n<\/thead>\n
\n
acExport<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n
\n
The data is exported.<\/p>\n<\/td>\n<\/tr>\n
\n
acImport<\/p>\n<\/td>\n
\n
0<\/p>\n<\/td>\n
\n
(Default) The data is imported.<\/p>\n<\/td>\n<\/tr>\n
\n
acLink<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n
\n
The database is linked to the specified data source.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/div>\n
The second argument is the Spreadsheet<\/b> argument. There are 10 different enumerations listed. but only 8 different values. You will also notice from Table 4<\/b> 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.<\/p>\n
        var tableId = “EPIAE”;        sortedCol[tableId] = null;        sortFactor[tableId] = 1;        initialSort[tableId] = true;      <\/p>\n\n\n\n\n
Table 4 TransferSpreadSheet method SpreadsheetType enumeration values<\/td>\n<\/tr>\n
<\/p>\n\n\n\n
Name<\/td>\n\"*\"<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/a><\/td>\n
<\/p>\n\n\n\n
Value<\/td>\n\"*\"<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/a><\/td>\n
<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Description<\/td>\n\"*\"<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/a><\/td>\n<\/tr>\n<\/thead>\n
\n
acSpreadsheetTypeExcel3<\/p>\n<\/td>\n
\n
0<\/p>\n<\/td>\n
\n
Microsoft Excel 3.0 format<\/p>\n<\/td>\n<\/tr>\n
\n
acSpreadsheetTypeExcel4<\/p>\n<\/td>\n
\n
6<\/p>\n<\/td>\n
\n
Microsoft Excel 4.0 format<\/p>\n<\/td>\n<\/tr>\n
\n
acSpreadsheetTypeExcel5<\/p>\n<\/td>\n
\n
5<\/p>\n<\/td>\n
\n
Microsoft Excel 5.0 format<\/p>\n<\/td>\n<\/tr>\n
\n
acSpreadsheetTypeExcel7<\/p>\n<\/td>\n
\n
5<\/p>\n<\/td>\n
\n
Microsoft Excel 95 format<\/p>\n<\/td>\n<\/tr>\n
\n
acSpreadsheetTypeExcel8<\/p>\n<\/td>\n
\n
8<\/p>\n<\/td>\n
\n
Microsoft Excel 97 format<\/p>\n<\/td>\n<\/tr>\n
\n
acSpreadsheetTypeExcel9<\/p>\n<\/td>\n
\n
8<\/p>\n<\/td>\n
\n
Microsoft Excel 2000 format<\/p>\n<\/td>\n<\/tr>\n
\n
acSpreadsheetTypeLotusWJ2<\/p>\n<\/td>\n
\n
4<\/p>\n<\/td>\n
\n
Japanese version only<\/p>\n<\/td>\n<\/tr>\n
\n
acSpreadsheetTypeLotusWK1<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n
\n
Lotus 1-2-3 WK1 format<\/p>\n<\/td>\n<\/tr>\n
\n
acSpreadsheetTypeLotusWK3<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n
\n
Lotus 1-2-3 WK3 format<\/p>\n<\/td>\n<\/tr>\n
\n
acSpreadsheetTypeLotusWK4<\/p>\n<\/td>\n
\n
7<\/p>\n<\/td>\n
\n
Lotus 1-2-3 WK4 format<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/div>\n
We could have used the Enumerations<\/b> directly by first loading the Access Interop<\/b> assembly. To do this in Windows PowerShell 1.0, we can use the LoadWithPartialName<\/b> static method from the Reflection.Assembly<\/b> .NET Framework class. After we have loaded the Interop<\/b> assembly, we can create the two enumeration classes as seen here:<\/p>\n
[Reflection.Assembly]::LoadWithPartialName(\"Microsoft.Office.Interop.Access\") | Out-Null\n$AcDataTransferType = \"Microsoft.Office.Interop.Access.AcDataTransferType\" -as [type]\n$AcSpreadSheetType = \"Microsoft.Office.Interop.Access.AcSpreadSheetType\" -as [type]\n<\/pre>\n
If we had done that, we could then call the TransferSpreadSheet<\/b> method and used the enumeration values directly; the TransferSpreadSheet method call would have looked like the following:<\/p>\n
$Access.DoCmd.TransferSpreadSheet($AcDataTransferType::acImport,\n$AcSpreadSheetType::AcSpreadSheetTypeExcel9, $TableName, $FileName,$HasFieldNames,$Range)\n<\/pre>\n
Instead, for simplicity\u2019s sake, we used the methodology more in keeping with the VBScript version of this script<\/a>. The TableName<\/b> is the table name in the Office Access database, and the FileName<\/b> argument is the path and name of the Office Excel spreadsheet to import. The HasFieldNames<\/b> 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<\/b> call is shown here:<\/p>\n
$Access.DoCmd.TransferSpreadSheet($Import, $SpreadSheetType, $TableName, $FileName,$HasFieldNames,$Range)<\/pre>\n
If you have an autonumber<\/b> 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. <\/p>\n
After we have imported the spreadsheet, we use the Quit<\/b> method to close out Access.Application<\/b> object. This is a very important step that will prevent multiple copies of Office Access from running:<\/p>\n
$Access.Quit()<\/pre>\n
The completed Office Access database with the newly imported data is seen here:<\/p>\n
\"Image<\/p>\n
 <\/p>\n
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. <\/p>\n
Ed Wilson and Craig Liebendorfer, Scripting Guys<\/b><\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/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":[717,710,54,48,49,3,45],"class_list":["post-54353","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-access-application","tag-excel-spreadsheet","tag-microsoft-access","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-windows-powershell"],"acf":[],"blog_post_summary":"
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 […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/54353","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=54353"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/54353\/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=54353"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=54353"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=54353"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}