VB6 to excel question

[SAL]

Hello,
I hope this is the right place to post this question. I have a VB6
application that has been using automation to run code in Excel for years
and all of a sudden, it can't find the function in Excel that I'm calling.
I'm posting my code below but it's fairly straightforward. I could use a
little help on it.

sToPath = App.Path & "\Excel\FullProperty.xls"
bSuccess = CopyFile(sFromPath, sToPath)
If bSuccess Then
SetAttr sToPath, vbArchive
End If

Set myXL = GetObject(, "Excel.Application")

If myXL Is Nothing Then
Set myXL = CreateObject("Excel.Application")
End If

Set MyXlSheet = myXL.Workbooks.Open(FileName:=sToPath)

MyXlSheet.Application.Visible = True
DoEvents
ReadyExcel

' The next line produces the Error:
' 1004 The macro 'CopyComps' cannot be found
MyXlSheet.Application.Run "CopyComps", 2, m_XArray.UpperBound(2) -
3
MyXlSheet.Application.Run "FillComps", m_vExcelArray

It could always find it before and I have verified that the routine is
definitely there.

Thoughts? PS, the spreadsheet isn't protected....

SAL

 

[Jim Rech]

When I run macros via automation I like to be explicit about the workbook
with the macro:

MyXL.Run MyXlSheet.Name & "!CopyComps", etc

Since MyXL is the Excel object no reason to add complexity:
MyXlSheet.Application

--
Jim
| Hello,
| I hope this is the right place to post this question. I have a VB6
| application that has been using automation to run code in Excel for years
| and all of a sudden, it can't find the function in Excel that I'm calling.
| I'm posting my code below but it's fairly straightforward. I could use a
| little help on it.
|
| sToPath = App.Path & "\Excel\FullProperty.xls"
| bSuccess = CopyFile(sFromPath, sToPath)
| If bSuccess Then
| SetAttr sToPath, vbArchive
| End If
|
| Set myXL = GetObject(, "Excel.Application")
|
| If myXL Is Nothing Then
| Set myXL = CreateObject("Excel.Application")
| End If
|
| Set MyXlSheet = myXL.Workbooks.Open(FileName:=sToPath)
|
| MyXlSheet.Application.Visible = True
| DoEvents
| ReadyExcel
|
| ' The next line produces the Error:
| ' 1004 The macro 'CopyComps' cannot be found
| MyXlSheet.Application.Run "CopyComps", 2, m_XArray.UpperBound(2) -
| 3
| MyXlSheet.Application.Run "FillComps", m_vExcelArray
|
| It could always find it before and I have verified that the routine is
| definitely there.
|
| Thoughts? PS, the spreadsheet isn't protected....
|
| SAL
|
|
|

 

[Bernie Deitrick]

SAL,

Make sure that you don't have a named range or other object with the name CopyComps

Try explicity setting the path to the macro:

Dim str_Macro As String
str_Macro = Thisworkbook.Name & "!CopyComps"
MyXlSheet.Application.Run str_Macro, 2, m_XArray.UpperBound(2) - 3

HTH,
Bernie
MS Excel MVP