?
Solved

open filename

Posted on 2010-01-01
11
Medium Priority
?
462 Views
Last Modified: 2012-05-08
       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"
0
Comment
Question by:derekackerman
  • 8
  • 2
11 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26160774
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
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 26160775
Hello derekackerman,
Try replacing trend with trends:

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

Regards,

chris_bottomley
0
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 1000 total points
ID: 26160778
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26160781
Almost ;-)

Change:

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

to:

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

Kevin
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26160782
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
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26160785
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
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26160786
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
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26160792
Experts like getting feedback. Ever see tigers at the zoo which haven't been fed?
0
 

Author Comment

by:derekackerman
ID: 26160816
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
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26160823
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
 
LVL 81

Assisted Solution

by:zorvek (Kevin Jones)
zorvek (Kevin Jones) earned 1000 total points
ID: 26160825
In short, change:

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

to:

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

The debugger is your friend ;-)

Kevin
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question