Naming a workbook using a macro

[lj]

Hi,
I'm putting together a macro that saves down a file. Part of the name
is "Dashboard" and part of the name is a date. I want to macro to
reference a a particular cell in my spreadsheet for the date and
append it to dashboard. I've named the cell in the spreadheet that
contains the date I need, Date in the macro. I'm not sure how to
incorporate it below with my network location. I've tried using
&Date&. This doesn't work. What is the correct syntax? Thanks for
your help.

"\\Corp.real.com\rnfiles\games\Business Operations\Dashboards\New
Dashboard\Dashboard&Date&.xls" _


Lisa

 

[David Heaton]

Hi,
I'm putting together a macro that saves down a file.  Part of the name
is "Dashboard" and part of the name is a date.  I want to macro to
reference a a particular cell in my spreadsheet for the date and
append it to dashboard. I've named the cell in the spreadheet that
contains the date I need, Date in the macro.  I'm not sure how to
incorporate it below with my network location.  I've tried using
&Date&.  This doesn't work.  What is the correct syntax?  Thanks for
your help.

 "\\Corp.real.com\rnfiles\games\Business Operations\Dashboards\New
Dashboard\Dashboard&Date&.xls" _

Lisa

Lisa,

I'm assuming the 'date' reference is a named range in your worksheet
try this
"\\Corp.real.com\rnfiles\games\Business Operations\Dashboards\New
Dashboard\Dashboard" & RANGE("date") & ".xls"

hth

Regards

David

 

[lisaj]

Lisa,

I'm assuming the 'date' reference is a named range in your worksheet
try this
"\\Corp.real.com\rnfiles\games\Business Operations\Dashboards\New
Dashboard\Dashboard" & RANGE("date")  & ".xls"

hth

Regards

David

Thanks, I'm still getting an error though. Here's exactly what I'm
entering into visual basic.

ActiveWorkbook.SaveAs Filename:= _
"\\Corp.real.com\rnfiles\games\Business Operations\Dashboards
\New Dashboard\" & Range("Date") & ".xls" _
, FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

 

[David Heaton]

Thanks, I'm still getting an error though.  Here's exactly what I'm
entering into visual basic.

    ActiveWorkbook.SaveAs Filename:= _
        "\\Corp.real.com\rnfiles\games\Business Operations\Dashboards
\New Dashboard\" & Range("Date") & ".xls" _
        , FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False- Hidequoted text -

- Show quoted text -

Lisa,

a couple of questions to narrow it down.

What error are you getting?

what value is in the date cell.? if the date is in 01/01/09 format,
it wont work as the \ is part of the folder structure.

Also , set a breakpoint on the line, then when it stops goto the the
debug window and type

? Range("date")

to make sure the correct value is being pickup up

Regards

David

 

[Dave Peterson]

Maybe...

Dim myFolderName as string
dim myFileName as string

myfoldername = "\\Corp.real.com\rnfiles\games\" _
& "Business Operations\Dashboards\New Dashboard\"
myfilename = "dashboard_" _
& format(activesheet.range("myDate"), "yyyy-mm-dd") & ".xls"

Activeworkbook.saveas filename:=myfoldername & myfilename, _
fileformat:=xlworkbooknormal

========

I used the activeworkbook, but that may not be correct.

And you'll have to format your date nicely. WinTel machines won't allow slashes
in the filename.

And even though excel allows it, I wouldn't name a cell "Date". It looks too
much like the =date() worksheet function and would confuse me. I used myDate as
the name.

And I'm not sure how that last "dashboard" is supposed to be used. If it's a
variable or if it's a string or what???

 

[Dave Peterson]

I like to specify my properties and I missed one (.value):

myfilename = "dashboard_" _
& format(activesheet.range("myDate").Value, "yyyy-mm-dd") _
& ".xls"

 

[lisaj]

I like to specify my properties and I missed one (.value):

myfilename = "dashboard_" _
                & format(activesheet.range("myDate").Value, "yyyy-mm-dd") _
                & ".xls"














--

Dave Peterson- Hide quoted text -

- Show quoted text -

I checked to make sure my range is being passed correctly and my date
is in the right format. I changed the name "date" in case that was an
issue. I can't seem to figure it out though. Here's the error I get.
I get run time error 1004 "Method 'Range' of Object '_Global failed

Here's my statement.

ActiveWorkbook.SaveAs Filename:= _
"\\Corp.real.com\rnfiles\games\Business Operations\Dashboards
\NewDashboard\Dashboard" & Range("Curdate") & ".xls" _
, FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

 

[David Heaton]

I checked to make sure my range is being passed correctly and my date
is in the right format.  I changed the name "date" in case that was an
issue.  I can't seem to figure it out though.  Here's the error I get..
I get run time error 1004 "Method 'Range' of Object '_Global failed

Here's my statement.

    ActiveWorkbook.SaveAs Filename:= _
        "\\Corp.real.com\rnfiles\games\Business Operations\Dashboards
\NewDashboard\Dashboard" & Range("Curdate") & ".xls" _
        , FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False- Hidequoted text -

- Show quoted text -

Lisa,

Try Dave's suggestion about adding ActiveSheet.Range and building up
the filename first before you save it so you can check it.

David

 

[Dave Peterson]

And even if you keep it in a longggggggg string, make sure you qualify the
Range() with the correct worksheet.

Activesheet.range("yourDatecell").value
or
worksheets("somesheetnamehere").range("yourdatecell").value

 

[lisaj]

And even if you keep it in a longggggggg string, make sure you qualify the
Range() with the correct worksheet.

Activesheet.range("yourDatecell").value
or
worksheets("somesheetnamehere").range("yourdatecell").value









--

Dave Peterson- Hide quoted text -

- Show quoted text -

I tried this code and it's still not running, it get's hung up on the
section below.

myfilename = "dashboard_" _
& format(activesheet.range("myDate").Value, "yyyy-mm-
dd") _
& ".xls"

The whole thing runs if I run it with myfilename = "dashboard_" _ and
leave out the rest.
The error I get is "application defined or object definied error". I
still can't get it to work when I try and take out all of the date
formatting and just put text into the mydate cell on my report. I
know it is pikcing up the correct "my date" cell becasue I've tested
it a few times and have had the macro paste it somewhere else it the
report. Any more ideas on how to get myfilename section to run?




Dim myFolderName as string
dim myFileName as string

myfoldername = "\\Corp.real.com\rnfiles\games\" _
& "Business Operations\Dashboards\New Dashboard\"

myfilename = "dashboard_" _
& format(activesheet.range("myDate").Value, "yyyy-mm-
dd") _
& ".xls"

Activeworkbook.saveas filename:=myfoldername & myfilename, _
fileformat:=xlworkbooknormal

 

[lisaj]

I tried this code and it's still not running, it get's hung up on the
section below.

myfilename = "dashboard_" _
                & format(activesheet.range("myDate").Value, "yyyy-mm-
dd") _
                & ".xls"

The whole thing runs if I run it with myfilename = "dashboard_" _  and
leave out the rest.
The error I get is "application defined or object definied error".  I
still can't get it to work when I try and take out all of the date
formatting and just put text into the mydate cell on my report.  I
know it is pikcing up the correct "my date" cell becasue I've tested
it a few times and have had the macro paste it somewhere else it the
report.  Any more ideas on how to get myfilename section to run?

Dim myFolderName as string
dim myFileName as string

myfoldername = "\\Corp.real.com\rnfiles\games\" _
                 & "Business Operations\Dashboards\New Dashboard\"

myfilename = "dashboard_" _
                & format(activesheet.range("myDate").Value, "yyyy-mm-
dd") _
                & ".xls"

Activeworkbook.saveas filename:=myfoldername & myfilename, _
        fileformat:=xlworkbooknormal- Hide quoted text -

- Show quoted text -

One more thing, I also can not get the following code to run when I
substitute it in for myFileName. I'm on Office 2007. Could this be
an issue? Thanks for your help.

myFileName = "dashboard_" _
& Range("myDate")

 

[Dave Peterson]

Maybe the activesheet isn't the one with the range named myDate?

Try specifying the real worksheet name. See that older suggestion.



lisaj wrote:

 

[lisaj]

Maybe the activesheet isn't the one with the range named myDate?

Try specifying the real worksheet name.  See that older suggestion.

lisaj wrote:

<<snipped>>












--

Dave Peterson- Hide quoted text -

- Show quoted text -

Just got it to work. Probably not all that elegant, was a trial and
error process with the "myFileName" line. For some reason it didin't
like the quotes and parenthesis around myDate

myFileName = "Dashboard_" & Format(myDate, "yyyy-mm-dd")

Thanks.

 

[Dave Peterson]

If myDate is a variable in your code, then your suggestion will work fine.

But I thought you said that the date was in a cell on a worksheet. Using the
worksheets("...").range("myDate").value should work fine for that.