Submitted By: Kent Finkle

Imports a spreadsheet into an Access database, creating a new table in the database.

PowerShell
Edit|Remove
$comments = @'
Script name: Import-ExcelToAccess.ps1
Created on: Monday, May 07, 2007
Author: Kent Finkle
Purpose: How can I use Windows Powershell to Import an Excel Spreadsheet Into an Access Database?
'@

# -----------------------------------------------------
function Release-Ref ($ref) {
([System.Runtime.InteropServices.Marshal]::ReleaseComObject(
[System.__ComObject]$ref) -gt 0)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
}
# -----------------------------------------------------

$acImport = 0
$acSpreadsheetTypeExcel9 = 8

$a = New-Object -Comobject Access.Application
$a.OpenCurrentDatabase("C:\Scripts\Test.mdb")

[void] $a.DoCmd.TransferSpreadsheet($acImport, `
   $acSpreadsheetTypeExcel9, `
   "Employees", "C:\Scripts\Employees.xls", $True)

$b = Release-Ref($a