Reason for Excel Error message "Cannot run the macro..."

G’day Y’all,

I am getting the error shown in the attached html file and cannot figure out how to fix it.  I have seen the comments by broomee9 and BullmanTech but they had already been considered and taken care of.  When the spreadsheet opens, Workbook_Open()  runs the procedure called CreateXMLFile from module CreateXMLFiles and works as expected.  Once the procedure runs it is supposed to wait one minute and run the module CreateXMLFiles again and then keep looping “forever”.  However, when it loops the first time I get the error message.

The code in this spreadsheet is an attempt to do what I would like to do in my open question called: What is the reason for an “Unexpected error 50290…” in VB6 code?.  In that spreadsheet I do not use the “Application.OnTime Now + TimeValue("00:01:00"), "CreateXMLFiles"” statement since the VB code does the looping.

TIA Excel-XML-Info.htm
'
' This code is in XMLGTLInput
'
Public Sub Workbook_Open()
 
    CreateXMLFile

End Sub
 '
 ' This code is in Module CreateXMLFiles
 '
 Public Sub CreateXMLFile()
 
Dim I As Long, J As Long, K As Long
Dim InArray(40) As String
'
' Select sheet, columns and clear columns we are writing to.
'
    Sheets("GTLSetGeneratorMW").Activate
    Range("C2:C40,H2:H40,I2:I40").ClearContents
'
'   Initialize
'
    J = 0
    K = 1
'
' Import File Data
'
    Open "\\ECCNT\from_ems\GTL_Data\set_generator.txt" For Input As #1
     
    Do While Not EOF(1)
        
                Line Input #1, InArray(J)
             
                Range("C2:C40").Cells(K, 1).Value = Mid(InArray(J), 1, 11)
                Range("H2:H40").Cells(K, 1).Value = Mid(InArray(J), 12, 4)
                Range("I2:I40").Cells(K, 1).Value = Mid(InArray(J), 17, 4)
                 
                K = K + 1
                J = J + 1
    
    Loop
    Close #1
     
    Range("A1:J40").Select
         
    With Selection
    
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .EntireColumn.AutoFit
        
    End With
    
    Range("A45:A45").Select
	
'
' Close and exit while saving changes
'
    Application.DisplayAlerts = False
             
Application.OnTime Now + TimeValue("00:01:00"), "CreateXMLFiles"

End Sub

Open in new window

lostinspace9Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TomasPCommented:
can you test to see if "\\ECCNT\from_ems\GTL_Data\set_generator.txt"  is accessible. Check path and permissions.
Put a message before/after the open to see what is throwing the error
0
lostinspace9Author Commented:
Thanks for the reply.

The code works as expected without the Application.OnTime Now + TimeValue("00:01:00"), "CreateXMLFiles" statement.
0
TomasPCommented:
What happens if you remove the call to CreateXMLFiles? This is to see what part of the statement is failing
0
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

lostinspace9Author Commented:
That was a good idea TomasP.  But of course, I received the same error message and no others.  This poser has you guys thinking I see.  Thanks.
0
TomasPCommented:
Ok, now it is time simplify.
Reduce the function to something that puts up a simple message and nothing else. This will tell you if you are setting up the call correctly and if the problem is even in your function. I suspect that it is your setup to call your function and not the function itself. This will prove that this is the case.
It may well be you need to return something other than a string but something that is recognized as a URL by the caller.
0
lostinspace9Author Commented:
TomasP, I think I understand what you are after.  Here is what I did to test things:

Public Sub Workbook_Open()
 
   Call CreateXMLFile

End Sub

Public Sub CreateXMLFile()
 
    MsgBox ("Hello CreateXMLFile")
 
End Sub
 
I received the message box as expected.  But I do not understand "It may well be you need to return something other than a string but something that is recognized as a URL by the caller."  Also, remember that Workbook_Open will run CreateXMLFile as long as the statement Application.OnTime Now + TimeValue("00:01:00"), "CreateXMLFiles" is commented out.

However, I might not fully understand what all you would like me to test.
Thanks
0
TomasPCommented:
You did exactly what I needed. I just wanted to assure myself that nothing else was corrupting the transfer of control to your function.
Can you strip the Application.OnTime Now + TimeValue("00:01:00") statement down to Application.OnTime Now

Also, is the workbook open so that the Application object is instanciated?
0
Rory ArchibaldCommented:
Your procedure is called CreateXMLFile and not CreateXMLFiles so you just need to alter the OnTime line to:
Application.OnTime Now + TimeValue("00:01:00"), "CreateXMLFile"

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lostinspace9Author Commented:
That took care of my problem. Thanks rorrya.  And thanks TomasP for your time.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.