Iam developing a utility using VB2005 which imports a log(.all extn) to a excel (WIN XP) and then has to plot graph according to user selection from the menu bar of the VB form.

Following is the piece of code, where i open a standard template and save it. then i import the .bas file which has the macro to import from the log file. The VBP was working fine and suddenly it strats popping up the error

Runtime error 1004 The macro "Main" cannot be found.

I have referenced OLE automation, Microsoft excel 11.0 Obj Library, Microsoft VB Application extensibility.

ExcelMacro = CurrentDir & "\" & "import_macro.bas"

Set xlImportapp = CreateObject("Excel.Application")
Set xlImportBook = xlImportapp.Workbooks.Open(StdNamePath)
With xlImportapp
.Visible = False
.UserControl = False
.DisplayAlerts = False
End With
'On Error GoTo Disp
xlImportBook.SaveAs WorkbookName

'Importing Macro to the workbook
Set xlmodule = xlImportBook.VBProject.VBComponents.Import(ExcelMacro)
'Call the Excel Macro to plot graphs
xlImportapp.Run "Main", "NORMAL"

The Main() in the import_macro.bas is as follows

Sub Main(strState As String)

MsgBox "In the macro, string is " & strState
If strState = "NORMAL" Then
if strState = "HARVEST" Then
End If
End If

MsgBox "exit macro"
End Sub

Am completely clueless reg this unexpected behaviour and have been struggling to fix it since 2 days.

Request help to fix the error asap.

Thanks in advance.

Re: runtime error 1004 The macro 'Main' cannot be found

Cringing Dragon

Perhaps I'm missing something obvious... but I can't see where your VBA actually accesses import_macro.bas.

It sets a variable ExcelMacro to what I assume is the full path & name of the .bas file.

It later sets an object reference variable xlmodule to create an object reference... that is imported from ExcelMacro.

But it doesn't use the variable xlmodule anywhere that I can see.

So at what stage does it actually import the .bas file

Does you last (red) line need to use xlmodule instead of xlImportapp

Re: runtime error 1004 The macro 'Main' cannot be found

Andy Pope

I think you need to include the workbook name in the Run command

xlImportapp.Run xlImportBook.Name & "!Main", "NORMAL"