Macro worked yesterday, today it falls over!

[chuckles1066]

I have a macro that checks that another file is open before doing anything else - if the file isn't open a message box pops up informing the user and then exits the sub.

Worked fine yesterday.

Today I've run it and it falls over at this point - I think it was error 404 or similar, "object required".

I have acquired a little knowledge of VBA and so moved the Dim wb as Workbooks reference out of the global bit at the start of the macro and down to the relevant bit of code.

Now it works fine.

But, just so I'm armed for future reference, why would something work yesterday and not today?

 

[GlennUK]

At a guess, your code took a different path through your coding structure ... maybe an IF test was true today that wasn't true yesterday, and a variant result was applied to the wb object ... maybe. Hard to say without even seeing the code.

 

[Ruddles]

Yesyerday you may have run the code one or more times resulting in a (global) variable being created and/or assigned a value, then subsequent runs relied upon the variable existing and/or having a value.

Today you may have run the code and that variable either didn't exist or didn't have a value or had the wrong value, resulting in the error.

So basically what Glenn suggested.

PS. Instead of popping up a message box, could you not simply open the file in your program?

 

[chuckles1066]

PS. Instead of popping up a message box, could you not simply open the file in your program?

No, it's a text file with columns separated by a ^ sign.

Unless there's some code that deals with the text import file wizard?

 

[GlennUK]

You can import a text file in code ... just use the macro recorder to record yourself opening the file the way you want. Then incorporate that into your original code.

 

[chuckles1066]

Well flap me sideways, never knew you could but I've just tried it at home and it does generate code.

Thanks for that, I'll give it a whirl on Monday.