Using Workbook.Open Filename to Open a Cell Reference Filename

**PerplexedMel** · 06-10-2014, 08:27 AM

I am trying to get a file to open as a function of a macro where the filename needs to be dynamic by date. There is no option to change how the reference file is saved, so the file with the macro has a cell with a formula that does this: ="\\server\subserver\folder\subfolder\subsubfolder"&"\ParentFile"&TEXT(TODAY(),"MM")&"_"&TEXT(TODAY(),"DD")&"_"&TEXT(TODAY(),"YY")&".xlsm" But I cannot get that to work in the VBA code...it errors out.

Need to open the file using that reference... Not married to the Workbook.Open Filename code, but it's what I know...

Any help is greatly appreciated.

Mel.

**6StringJazzer** · 06-10-2014, 09:16 AM

Workbook.Open is the way to do this in VBA. Show the VBA code that you are trying to use (the whole Sub, not just one line), and tell us what error you are getting.

By the way, be aware that you can do this instead of stringing together several functions and strings:

TEXT(TODAY(),"MM\_DD\_YY")

**PerplexedMel** · 06-10-2014, 09:36 AM

Thanks Jeff - No, I didn't know I could do that... Thanks - I adjusted that formula...

Here is the exact code that I would like to get to work:

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #3 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer

Please Login or Register  to view this content.

But the "TODAY" give me a "compile error - sub or function not defined" - hence why I wanted to switch to using the cell reference since the cell works.

Thanks.

**6StringJazzer** · 06-10-2014, 12:01 PM

There are two ways to do this: reference the cell with your formula, or build the corresponding VBA. Your VBA is close.

Please Login or Register  to view this content.

**PerplexedMel** · 06-10-2014, 12:20 PM

Thank you very much!! The first way was what I was going for initially, but could not get to work and moved on to the 2nd...then posted here. Works perfectly now.

Thank you very much!
Melissa

Using Workbook.Open Filename to Open a Cell Reference Filename

LinkBack

Thread Tools

Rate This Thread

Display

Using Workbook.Open Filename to Open a Cell Reference Filename

Re: Using Workbook.Open Filename to Open a Cell Reference Filename

Re: Using Workbook.Open Filename to Open a Cell Reference Filename

Re: Using Workbook.Open Filename to Open a Cell Reference Filename

Re: Using Workbook.Open Filename to Open a Cell Reference Filename

Thread Information

Users Browsing this Thread

Similar Threads

Workbook.Open Filename procedure

set filename to <filename-date> on open

Activating an Open Workbook without using the whole filename as a reference

Open filename using workbook property

[SOLVED] Copy from one workbook to another-Workbooks.Open Filename

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions