This import automation code resolved my issue of running Excel vba from
Access. It's interesting that on my export routine (not shown here) I had to
use a messagebox between the first automation routine and the second one to
avoid a "-2147417851" automation error. It may have something to do with
synchronous vs async mode and COM?
Dim oApp As Object
Dim oWb As Object
On Error Resume Next
Set oApp = CreateObject("Excel.Application")
Set oWb = oApp.Workbooks.Open(vFullPath)
If oWb Is Nothing Then
MsgBox "Workbook Not Found or Error! Exiting.", vbCritical, "Error"
Set oWb = Nothing
Set oApp = Nothing
Exit Sub
End If
On Error GoTo err_
oApp.Visible = True
oApp.Application.windows(vFile).Visible = True
If resType = "Res" Then
oWb.Application.Run "cycleRes"
ElseIf resType = "Nres" Then
oWb.Application.Run "cycleNRes"
End If
oWb.Close False
Set oWb = Nothing
oApp.Quit
Set oApp = Nothing
'=============================================
DoCmd.SetWarnings False
DoCmd.OpenQuery "10Flush_tmpNewInoivce"
DoCmd.OpenQuery "11Flush_tmpNewHeader"
DoCmd.SetWarnings True
'overwrites tmpNewInvoice and tmpNewHeader
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, vTableNameData,
vFullPath, True, vRangeData
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
vTableNameHeader, vFullPath, True, vRangeHeader
'-----
Dim oApp2 As Object
Dim oWb2 As Object
On Error Resume Next
Set oApp2 = CreateObject("Excel.Application")
Set oWb2 = oApp2.Workbooks.Open(vFullPath)
If oWb2 Is Nothing Then
Set oWb2 = Nothing
Set oApp2 = Nothing
MsgBox "Workbook Not Found or Error. Exiting.", vbCritical, "Error"
Exit Sub
End If
On Error GoTo err_
oApp2.Visible = True
oApp2.Application.windows(vFile).Visible = True
'-----
If resType = "Res" Then
oWb2.Application.Run "REVERSEcycleRes"
ElseIf resType = "Nres" Then
oWb2.Application.Run "REVERSEcycleNRes"
End If
oWb2.Close False
Set oWb2 = Nothing
oApp2.Quit
Set oApp2 = Nothing