VBA Code to Enter File Name, Activate the File, and Run Macro on File

ALCON,

First of all let me say hello, I am a new member and wanted to greet all my fellow Excellians.

Now, I am having fits trying to get this to work. All I want is for a user to enter a file name in an "InputBox" and then take that entered file name and run a macro against it to change some static data on the worksheets in the file. There is not a consistent amount of worksheets across files, but each worksheet is identical in format.

So, what I have now (that doesn't work by the way) follows:

Please Login or Register  to view this content.

As I said, what I have doesn't work. I can enter the file name fine, after that, crashes with run time error.

Any and all assistance/advice/suggestions will be greatly appreciated and I would like to thank you all in advance for your understanding and cooperation in this matter.

Sincerely,

Confused Analyst

Thank you to all who assisted, and you know whom you are, besides, I think everyone out here deserves Kudos for having to deal with an MS Office product on a daily basis. I just wish my co-workers would see that Excel can do more than add a column of numbers. Most of what I am doing, with ya'lls help, is so far above their heads that they will most likely never understand, but they are a smaaat group of people though, just not computer savvy as some. Since I come from a computer analyst/programmer background, transitioning into the Financial arena has been easier on me than most. But I am blessed with help all around, and especially from here. I have had more problems solved by searching here for solutions, or by posting a question. Thanks to all who replied, and a special hello to my Canadian responder. I grew up in Canada, not in Toronto, but in Ottawa, and I sure do miss it, especially the winters. Yes, I know I am crazy, but I love snow, the deeper the better. Here in the SE USA we get an inch of snow and they close down the entire city for a week or so. Just don't get that, all they have to do is forecast snow and the schools are immediately closed. Kind of makes me wish I had of grown up down here, but not that hard.....ha ha.

Thanks again to all who assisted me.

Here you go

Please Login or Register  to view this content.

Please note that the code of the macro Change_Year_data should be placed in the same code page

Originally Posted by carsto

your code assume the file the user enters is OPEN.

change Windows('filenam1').Activate to
Workbooks(filenam1).Activate

I tried the above, but received a Run-Time Error '9' "Subscript out of range", because it seems it is now treating the "filenam1" in parentheses as an array, and now I am really confused as to what to do. I just want to activate a closed file, make some cosmetic changes, then be done with it. I really do appreciate all of the advice so far, but still not quite there.

Dhartwig35805,
Did you try my code?

Case 1: File not open and user will select it rather than typing it

Please Login or Register  to view this content.

Case 2: File not open and user will type the file name including the path

Please Login or Register  to view this content.

Case 3: File is already Opened and user will type the name of the file without the path

Please Login or Register  to view this content.

Dhartwig35805,
Thanks for the good evaluation.
Please mark this thread as SOLVED