Hi,
I want to load data from an Excel file into Access. The Excel file links with SAP via Analysis for Office.
This COM addin is loaded when the Excel file is opened manually: an extra Analysis tab is displayed with commands to display a prompt to define the query variables and to refresh the data.
I want to open the Excel file from Access VBA with Analysis for Office loaded so that the prompt is displayed to the user and the data can be refreshed. VBA opens Excel file, a test indicates that Analysis for Office has been loaded but I do not see any additional Analysis tab in the Excel file and I get an error when running the Analysis for Office command to display the prompt.
What am I doing wrong? Is there another approach possible?
This is my code:</SPAN>
Private Sub cmdTest_Click()
Dim objXLS As Object
Dim objWBK As Object
Dim objSHT As Object
Dim objAddIn As Object
Dim strFilePath As String
Dim blnResult As Boolean
strFilePath = "xxxxx"
Set objXLS = CreateObject("Excel.Application")
Set objWBK = objXLS.Workbooks.Open(filename:=strFilePath)
objXLS.Visible = True
‘objAddIn.Connect gives always True !
For Each objAddIn In objXLS.COMAddIns
If objAddIn.ProgId = "SapExcelAddIn" Then
If objAddIn.Connect = False Then objAddIn.Connect = True
End If
Next
With objXLS
blnResult = .Run("SAPExecuteCommand", "ShowPrompts", "DS_1")
End With
objWBK.Close
Set objWBK = Nothing
objXLS.Quit
Set objXLS = Nothing
End Sub
I want to load data from an Excel file into Access. The Excel file links with SAP via Analysis for Office.
This COM addin is loaded when the Excel file is opened manually: an extra Analysis tab is displayed with commands to display a prompt to define the query variables and to refresh the data.
I want to open the Excel file from Access VBA with Analysis for Office loaded so that the prompt is displayed to the user and the data can be refreshed. VBA opens Excel file, a test indicates that Analysis for Office has been loaded but I do not see any additional Analysis tab in the Excel file and I get an error when running the Analysis for Office command to display the prompt.
What am I doing wrong? Is there another approach possible?
This is my code:</SPAN>
Private Sub cmdTest_Click()
Dim objXLS As Object
Dim objWBK As Object
Dim objSHT As Object
Dim objAddIn As Object
Dim strFilePath As String
Dim blnResult As Boolean
strFilePath = "xxxxx"
Set objXLS = CreateObject("Excel.Application")
Set objWBK = objXLS.Workbooks.Open(filename:=strFilePath)
objXLS.Visible = True
‘objAddIn.Connect gives always True !
For Each objAddIn In objXLS.COMAddIns
If objAddIn.ProgId = "SapExcelAddIn" Then
If objAddIn.Connect = False Then objAddIn.Connect = True
End If
Next
With objXLS
blnResult = .Run("SAPExecuteCommand", "ShowPrompts", "DS_1")
End With
objWBK.Close
Set objWBK = Nothing
objXLS.Quit
Set objXLS = Nothing
End Sub
