open filename

       I'm trying to open a filename that is a variable string
DL = "C"
Nodes = "\PROD SUPPORT\A\B\"
Prefix = DL & ":" & Nodes
   
For yyyy = 2002 To 2003
 For mm = 1 To 12
    If mm < 10 Then mm = "0" & mm
     path = Prefix & yyyy & "\" & mm & "\"
    For dd = 1 To 31
If dd < 10 Then dd = "0" & dd
Fn = "Trend" & yyyy & "-" & mm & "-" & dd & ".xls"
Fulln = Path & Fn
  this doesn't work...
   Workbooks.Open Filename:=Fulln
this does work...
  Workbooks.Open Filename:="C:\PROD SUPPORT\A\B\TRENDS\2002\01\Trend 01_09.xls"
derekackermanAsked:
Who is Participating?
 
Chris BottomleySoftware Quality Lead EngineerCommented:
derekackerman,

OOh arr and add the space!
Fn = "Trend" & yyyy & "-" & mm & "-" & dd & ".xls"
to
Fn = "Trends " & yyyy & "-" & mm & "-" & dd & ".xls"

BTW I assume you have allowed for months without 31 days?

chris_bottomley
0
 
zorvek (Kevin Jones)ConsultantCommented:
Is this a valid file path?

C:\PROD SUPPORT\A\B\2002\01\Trend2002-01-01.xls

It's the first generated by your code.

Kevin
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Hello derekackerman,
Try replacing trend with trends:

Fn = "Trend" & yyyy & "-" & mm & "-" & dd & ".xls"
to
Fn = "Trends" & yyyy & "-" & mm & "-" & dd & ".xls"

Regards,

chris_bottomley
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
zorvek (Kevin Jones)ConsultantCommented:
Almost ;-)

Change:

Fn = "Trend" & yyyy & "-" & mm & "-" & dd & ".xls"

to:

Fn = "TRENDS\" & yyyy & "-" & mm & "-" & dd & ".xls"

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
Oh jeez...there are multiple errors! Fixed:

DL = "C"
Nodes = "\PROD SUPPORT\A\B\TRENDS\"
Prefix = DL & ":" & Nodes
   
For yyyy = 2002 To 2003
 For mm = 1 To 12
    If mm < 10 Then mm = "0" & mm
     path = Prefix & yyyy & "\" & mm & "\"
    For dd = 1 To 31
If dd < 10 Then dd = "0" & dd
Fn = "Trend " & mm & "_" & dd & ".xls"
Fulln = Path & Fn

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
If this is a correct path:

"C:\PROD SUPPORT\A\B\TRENDS\2002\01\Trend 2009_01_09.xls"

and not

"C:\PROD SUPPORT\A\B\TRENDS\2002\01\Trend 01_09.xls"

Then:

DL = "C"
Nodes = "\PROD SUPPORT\A\B\TRENDS\"
Prefix = DL & ":" & Nodes
   
For yyyy = 2002 To 2003
 For mm = 1 To 12
    If mm < 10 Then mm = "0" & mm
     path = Prefix & yyyy & "\" & mm & "\"
    For dd = 1 To 31
If dd < 10 Then dd = "0" & dd
Fn = "Trend " & yyyy & "_" & mm & "_" & dd & ".xls"
Fulln = Path & Fn

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
derekackerman,

Have you ever considered debugging your code? You can step through it and look at the variables. The Debug.Print is a handy tool too:

...
Fulln = Path & Fn
Debug.Print Fulln

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
Experts like getting feedback. Ever see tigers at the zoo which haven't been fed?
0
 
derekackermanAuthor Commented:
my example had an error, here is the real McCoy....
==============================================

DL = "C"
Nodes = "\PROD SUPPORT\ADTP\ADT LPAR TRENDS\"
Prefix = DL & ":" & Nodes
   
For yyyy = 2002 To 2003
    yy = yyyy - 100 * Int(yyyy / 100)
    If yy < 10 Then yy = "0" & yy
       
For mm = 1 To 12
    If mm < 10 Then mm = "0" & mm
   
    path = Prefix & yyyy & "\" & mm & "\"
   
  For dd = 1 To 31
    If dd < 10 Then dd = "0" & dd

Fn = "Trend" & yyyy & "-" & mm & "-" & dd & ".xls"
Fulln = path & Fn
day = yy & mm & dd

'  MsgBox ("FN = '" & Fn & "'  Day = " & day)

 day = yy & mm & dd
 md = mm & "-" & dd
 mdy = md & "-" & yy
 
 Application.StatusBar = "*** " & Fulln

Application.DisplayAlerts = False

 wd = Weekday(mdy)

  If wd = 1 Then GoTo bump:
  If wd = 7 Then GoTo bump:
 ' If md = "05-31" Then GoTo bump:
  If md = "07-04" Then GoTo bump:
  If md = "12-25" Then GoTo bump:
  If md = "01-01" Then GoTo bump:
 
  On Error GoTo ErrHandler:
   
  MsgBox (Fulln)
 
 does not work --->  Workbooks.Open Filename:=Fulln
 works --->  Workbooks.Open Filename:="C:\PROD SUPPORT\ADTP\ADT LPAR TRENDS\2002\01\Trend 01_09.xls"

(CHris: if the month lacks all the days [could even be the 1st], the error handler goes to  next)
0
 
zorvek (Kevin Jones)ConsultantCommented:
Fixed:

DL = "C"
Nodes = "\PROD SUPPORT\ADTP\ADT LPAR TRENDS\"
Prefix = DL & ":" & Nodes
   
For yyyy = 2002 To 2003
    yy = yyyy - 100 * Int(yyyy / 100)
    If yy < 10 Then yy = "0" & yy
       
For mm = 1 To 12
    If mm < 10 Then mm = "0" & mm
   
    Path = Prefix & yyyy & "\" & mm & "\"
   
  For dd = 1 To 31
    If dd < 10 Then dd = "0" & dd

Fn = "Trend" & mm & "_" & dd & ".xls"
Fulln = Path & Fn

'  MsgBox ("FN = '" & Fn & "'  Day = " & day)

 md = mm & "-" & dd
 mdy = md & "-" & yy
 
  'MsgBox (Fulln)
  Debug.Print Fulln

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
In short, change:

Fn = "Trend" & yyyy & "-" & mm & "-" & dd & ".xls"

to:

Fn = "Trend" & mm & "_" & dd & ".xls"

The debugger is your friend ;-)

Kevin
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.