type mismatch when running Access.Application.DoCmd through Powershell

0

I'm having an issue with my code. I am hoping to use PowerShell to open an Access file, then export a table to an Excel file. so far, this is my code.

$MsAccess = New-object -com Access.Application
$MsAccess.OpenCurrentDatabase('<Filepath>',$false)
$MsAccess.Application.DoCmd.OpenTable("<TableName>")
$MsAccess.Application.DoCmd.OutputTo('acOutputTable, "<TableName>" , acFormatXLS , "OutputName.xls", true')
$MsAccess.CloseCurrentDatabase()
$MsAccess.Quit()

It will always error at:

$MsAccess.Application.DoCmd.OutputTo('acOutputTable, "TableName" , acFormatXLS , "OutputName.xls", true')

with an error stating:

 $MsAccess.Application.DoCmd.OutputTo('acOutputTable, "TableName" , acFormatXLS , "OutputName.xls", true')
Exception calling "OutputTo" with "1" argument(s): "Type mismatch. (Exception 
from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))"

I have been attempting to export a table to excel for a bit of time, but i cant seem to find much documentation about using excel through PowerShell. Would anyone have any suggestions on how to resolve this issue?

note: placeholders such as TableName, filepath, etc are not actual names, just replacements
excel
vba
powershell
ms-access
com
asked on Stack Overflow Jun 12, 2017 by tattmoney76 • edited Jun 12, 2017 by YowE3K

2 Answers

0

Try this:

$MsAccess.Application.DoCmd.OutputTo(0, "<TableName>" , 0, "OutputName.xls", $true)
answered on Stack Overflow Jun 12, 2017 by Kostas K.
0

I ended up using the docmd.transfer spreadsheet command in order to resolve this issue. I guess powershell doesn't like outputto.

answered on Stack Overflow Jun 13, 2017 by tattmoney76

User contributions licensed under CC BY-SA 3.0