How to use Excel 2000 Addin With VB
Posted by lyiva chung on August 02, 2001 1:27 AM
Dear Sir,
I have a Excel file which use ExcelAddin Function.
When I manually open the Excel file, it's ok. But if I want to use VB program to open the Excel, it doesn't work. So I just add some command as bleow.
Dim objExcel As New Excel.Application
Dim inWS As Excel.Workbook
Dim outWS As Excel.Workbook
Dim objATPack As Excel.AddIn
Dim N As Integer
Dim I As Integer
strSource = "C:\DSS\PROGRAM\Input"
strTarget = "C:\DSS\PROGRAM\Output"
N = 0
ReDim Files(N)
Files(N) = Dir(strSource & "\*.*")
While Files(N) <> ""
N = N + 1
ReDim Preserve Files(N)
Files(N) = Dir
Wend
If N = 0 And Files(N) = "" Then
MsgBox "Sorry, there's no any file in the directory."
Exit Sub
Else
N = N - 1
End If
With objExcel
.Visible = True
Set objATPack = objExcel.AddIns.Item("PowerPlay for Excel 6.6 Add-In")
.Workbooks.Open ("C:\Program Files\Cognos\cer1\bin\PPXLSERVER.XLA")
.Workbooks("PPXLSERVER.XLA").RunAutoMacros xlAutoOpen
'.Run "OnPPXLSetSheetActive"
But the When it process on "RunAutoMacros xlAutoOpen", it will show "Connection error... "
That should be something wrong on my program setting.
Is that anyone can reply me.
Thanks...
BR,
Lyvia Chung
Posted by Damon Ostrander on August 02, 2001 11:02 AM
Hello Lyvia,
1. Do you know for sure that the PPXLSERVER addin has an AutoOpen macro? If the addin has been updated to the new Workbook_Open event macro capability of Excel, no AutoOpen macro will be found.
2. The code creates an objATPack object, but then never uses it (at least within the range of code you provided). Just an observation.
3. Is "PowerPlay for Excel 6.6 Add-In" the same addin as the PPXLSERVER.XLA, or are these two different add-ins? If they are the same, then opening the PPXLSERVER.XLA workbook is redundant. All you should need to do is make sure the addin is installed via a
objATPack.Installed = True
If it is already installed, then perhaps it is conflicting with the retundant PPXLSERVER.XLA workbook.
There are just some thoughts. I hope this provides more light than heat.
Damon strSource = "C:\DSS\PROGRAM\Input" strTarget = "C:\DSS\PROGRAM\Output" N = 0 ReDim Files(N) Files(N) = Dir(strSource & "\*.*") While Files(N) <> "" N = N + 1 ReDim Preserve Files(N) Files(N) = Dir Wend If N = 0 And Files(N) = "" Then MsgBox "Sorry, there's no any file in the directory." Exit Sub Else N = N - 1 End If
