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?
 
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
 
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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
TomasPCommented:
What happens if you remove the call to CreateXMLFiles? This is to see what part of the statement is failing
0
 
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
 
lostinspace9Author Commented:
That took care of my problem. Thanks rorrya.  And thanks TomasP for your time.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.