Macro in xlma not working from time to time

[noemiemich]

Hello everybody,

I have a problem with the following macro:

Sub get_f_data()

Dim path As Variant
Dim excelfile As Variant
path = "C:\Users\me\Desktop\get_f_data\"

excelfile = Dir("*.xls")

Do While excelfile <> ""

Workbooks.Open fileName:=path & excelfile

Call project_capacity
Call electricity_production

excelfile = Dir

Loop

End Sub


It's an xlma file that I launch from the Excel task bar. It opens all workbook in the folder get_f_data and launches the procedures project_capacity and electricity_production.

Sometimes it's working fine, and sometimes when I click on the button in the taskbar that is linked to the xlma nothing happen. I have no error message but nothing happen, no file is opened.

Sometimes after opending different files and running other macros, this macro tries to open files outside the designated folder.

Could you please help me with this? Does anybody have an idea on why it's not working sometimes?

Thank you for your help;

[royUK]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

[noemiemich]

Hello,

Sorry about that. Here is the message with the codes tags:

Hello everybody,

I have a problem with the following macro:

Please Login or Register  to view this content.

It's an xlma file that I launch from the Excel task bar. It opens all workbook in the folder get_f_data and launches the procedures project_capacity and electricity_production.

Sometimes it's working fine, and sometimes when I click on the button in the taskbar that is linked to the xlma nothing happen. I have no error message but nothing happen, no file is opened.

Sometimes after opending different files and running other macros, this macro tries to open files outside the designated folder.

Could you please help me with this? Does anybody have an idea on why it's not working sometimes?

Thank you for your help;
Edit/Delete Message

[royUK]

You should just edit the first post

Why are you declaring Path as a variant instead of a string?
Likewise excelfile

[shg]

This

Please Login or Register  to view this content.

looks for xls files in the current directory, which may be different from one time to the next. Try instead this:

Please Login or Register  to view this content.

That code leaves all those files open ...

[noemiemich]

Hello,

Thank you very much for your help.

royUK, I found the code on the net and I used it as it was, I don't relly know why "variant" is used instead of "string" in this macro.

Shg, thank you very much for your new code, it seems to be working find now.

Thank you very much!

[royUK]

You might also want to use *.xlsm & *.xlsx to allow for different file extensions