CODEWITHSUNDEEP
excelvba
user3146807
user3146807

Reputation: 21

Excel VBA for opening a file with a date in the filename that changes

I have an Excel file that is created every day, named "three day outlook 7-7-2014. The date changes every day, but the files are stored by day in a folder, so I have multiple files named "three day outlook" but with different dates.

I am trying to use VBA code to open certain files by date that are selected by a date selection on another Excel sheet. I have tried the following: 

Workbooks.Open Filename:=("C:\Users\sdfe\Documents\three day outlook" & Cells(D3).xlsx)

Upvotes: 0

Views: 16080

Answers (2)

user1644564
user1644564

Reputation: 385

Use the format function so that you always know what the date will appear like. Everyone will have a different date format set on their computer. And you never know if the computer just sends the date as just a number instead of a date.

Upvotes: 0

djikay
djikay

Reputation: 10628

Try the following:

Workbooks.Open Filename:="C:\Users\sdfe\Documents\three day outlook " & Range("D3") & ".xlsx"

According to your question, there's a space character between the word "outlook" and the date. I assume the date is formatted on the sheet with the correct format. In fact, it might be better to show a message box with the path to the file you're trying to open. Something like:

MsgBox """C:\Users\sdfe\Documents\three day outlook " & Range("D3") & ".xlsx"""

and make sure this string is properly constructed before you use it.

Update (following your comment below):

Since your date is formatted as "7/7/2014" and not as "7-7-2014", there's a couple of things you can do:

  1. Format the cell where the date is (D3) as Text, so that it gets entered as you like and doesn't change by the date formatting.

  2. Format the cell where the date is (D3) with a Custom format of d-m-yyyy and then above, instead of Range("D3") use Range("D3").Text.

  3. Keep the cell formatting as Date (as I assume it is), and then above, instead of Range("D3") use Format(Range("D3"), "d-m-yyyy").

Upvotes: 2

Related Questions