I'm trying to develop a Excel macro with SAS Add-in v7.1, the macro works fine if I follow the official guideline to have both row of below:
Dim SAS As SASExcelAddIn
Set SAS = Application.COMAddIns.Item("SAS.Exceladdin").Object
However, the macro includes some functions where SAS add-in is not required and it's possible that people are using it without SAS add-in, therefore I tried late binding with only the 2nd sentence is specified (as below), but in this case, when I use the same code to do data insert from local machine, an error message 'Run-time error 13: Type mismatch' shows, and the dataset won't be inserted to the Excel workbook.
Set SAS = Application.COMAddIns.Item("SAS.Exceladdin").Object
BTW, with or without the 1st sentence, the SAS.HelloWorld works always. It seems that without the 1st sentence, there are somethings different but not all of them... but I don't know what it is or what the solution could be...
I've pasted the complete code as below. Could anyone help on this topic?
Sub test()
Dim SAS As SASExcelAddIn
Set SAS = Application.COMAddIns.Item("SAS.Exceladdin").Object
ThisWorkbook.Sheets.Add after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
Set sh = ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
Call SAS.InsertDataFromLocalMachine(Name_of_A_File, sh.Range("A1"), 1, True, , , False)
End Sub
