Activate file with date name

[Lift Off]

I have mulitple files, all with date names that vary. The format produces names like Nov 21.xlsx.

From another file I need to go to the date named file to refresh a pivot. I've tried the code below and keep getting a run time error 9. "Script out of range."

From the locals window these are the expressions and they are correct:
: sPath : "C:\Users\clwillis\Desktop" : String
: WorkbookName : "sPath\Nov 21.xlsx" : String

This is the code:

Please Login or Register  to view this content.

The code stops at the fourth line from bottom: Windows("WorkbookName").Activate

Any ideas what's wrong?

Thanks, Cliff

[Lift Off]

Bump. I would like some input on this.

Thanks, Cliff

[Ricker090]

I don't think you need quotes around sPath since it's a variable.

So the fifth from last line of code should look something like this:

Please Login or Register  to view this content.

[Lift Off]

I don't think you need quotes around sPath since it's a variable.

So the fifth from last line of code should look something like this:

Please Login or Register  to view this content.

Hi,

I removed the quotes but that didn't make a difference. The code get stuck on the next line from the line you mentioned. It stops at "Windows(WorkbookName).Activate" with an Out of range error. In the locals window i can see that 'WorkbookName is correct.

Cliff

[Ricker090]

Oh, I see. "Windows(WorkbookName).Activate" isn't supposed to work unless the workbook is open.

Try this:

Please Login or Register  to view this content.

[Lift Off]

Oh, I see. "Windows(WorkbookName).Activate" isn't supposed to work unless the workbook is open.

Try this:

Please Login or Register  to view this content.

The workbook is already open and the macro is intended to update the pivot in the workbook. That is why I just needed to activate the workbook. However; i went ahead and put your suggested 'Open Filename' in the code and it ran. (took out the activate.) Of course it gave me an error the workbook was already open but I answered Yes to reopen and the macro completed. So there is something wrong with my Activate code because it stops there and not with your Open code. Any thoughts?

Thanks for helping on this. Cliff

[Ricker090]

I didn't realize that you already had the workbook open.

You don't need to define the path if it's already open. All you need is the name of the file.

Try this:

Please Login or Register  to view this content.

[Lift Off]

I didn't realize that you already had the workbook open.

You don't need to define the path if it's already open. All you need is the name of the file.

Try this:

Please Login or Register  to view this content.

That was it! Many thanks for taking the time with me.
Enjoy the coming holidays.
Cliff

[ConneXionLost]

Don't quote whole posts -- it's just clutter.

If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding.