Loading data from Excel file with Analysis for Office in Access

[naverty01]

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

 

[Ranman256]

you dont need any of this code.
attach the xl book as a table and run an import query.
or
in a macro , use Transferspreadsheet, to import to the target table.

 

[static]

According to this...
http://stackoverflow.com/questions/213375/loading-addins-when-excel-is-instantiated-programmatically

you could try this...

For Each objAddIn In objXLS.COMAddIns
	If objAddIn.ProgId = "SapExcelAddIn" Then
		If objAddIn.Installed Then
			objAddIn.Installed = False
			objAddIn.Installed = True
		End If
		If objAddIn.Connect = False Then objAddIn.Connect = True
	End If
Next