VBA to open file with specific name

[Blunder1]

Hi all,

I have the below code which opens a particular workbook name depending on the date in cell a1. I wrote this the middle of last month and it works fine, however, i have found an error on this and am struggling to resolve.

Basically the file name it opens is in folder 2011, then folder July, then the file name is 'Raw Data' followed by the DDMM. Now we're at the beginging of the month this code bring 'thedate' up as 107 or 207 etc when the file names are 0107 or 0207 etc...

ws.Range("a1").NumberFormat = "YYYY"
theyear = ws.Range("a1").Text
ws.Range("a1").NumberFormat = "MMMMMMMMMMMMM"
themonth = ws.Range("a1").Text
ws.Range("a1").NumberFormat = "DDMM"
thedate = ws.Range("a1").Text
        Workbooks.Open Filename:= _
       "C:\Raw Data\" & theyear & "\" & themonth & "\Raw Data " & thedate & "", ReadOnly:=True

Thanks in advance

Blunder

 

[CharlesChuckieCharles]

Hi all,

I have the below code which opens a particular workbook name depending on the date in cell a1. I wrote this the middle of last month and it works fine, however, i have found an error on this and am struggling to resolve.

Basically the file name it opens is in folder 2011, then folder July, then the file name is 'Raw Data' followed by the DDMM. Now we're at the beginging of the month this code bring 'thedate' up as 107 or 207 etc when the file names are 0107 or 0207 etc...

ws.Range("a1").NumberFormat = "YYYY"
theyear = ws.Range("a1").Text
ws.Range("a1").NumberFormat = "MMMMMMMMMMMMM"
themonth = ws.Range("a1").Text
ws.Range("a1").NumberFormat = "DDMM"
thedate = ws.Range("a1").Text
        Workbooks.Open Filename:= _
       "C:\Raw Data\" & theyear & "\" & themonth & "\Raw Data " & thedate & "", ReadOnly:=True

Thanks in advance

Blunder

try this

Dim sDate as string
sdte = Format(ws.Range("a1"), "YYYY") & "\" & Format(ws.Range("a1"), "mmmm") & "\Raw Data " & Format(ws.Range("a1"), "DDMM")
        Workbooks.Open Filename:= _
       "C:\Raw Data\" & sdte, ReadOnly:=True

 

[AlphaFrog]

Yet another way...

Workbooks.Open Filename:= _
    "C:\Raw Data\" & Format(ws.Range("A1").Value, "yyyy""\""mmmm""\Raw Data ""ddmm"), ReadOnly:=True

 

[Blunder1]

worked like a charm. thank you for you help