compile/syntax error to save active book to new location on networ

[Guest]

I'm just a beginner at writing macros. And I'm really lost with proper
syntax in order to point to the desired directory location where we need the
file saved.

I recorded the macro, and then edited respective to tab names, etc. I'm
thinking it's maybe the syntax around the TimePeriod string. I'm also not
familiar with all the SaveAs conditions.

Here's the statement that where I get the error:

ActiveWorkbook.SaveAs([Q:\Finance\Forecast\2007\"&(TimePeriod"\TRG\Prelim
Actual vs
Forecast],[.xls],[""],[Normal],[""],[""],[False],[False],[""],[""],[""],[""])

 

[Dave Peterson]

Instead of using positional parameters (who remembers what order they go in???),
you can use keywords. VBA's help for .saveas shows them:

activeworkbook.saveas _
filename:="Q:\Finance\Forecast\2007\" & TimePeriod & _
"\TRG\Prelim Actual vs Forecast.xls", _
fileformat:=xlworkbooknormal

And depending on what timeperiod is, you may have to format it.

If it's a date, then your filename can't contain slashes--if it's a time, then
the filename can't contain the colons--something like:

activeworkbook.saveas _
filename:="Q:\Finance\Forecast\2007\" & format(TimePeriod, "yyyymmdd") & _
"\TRG\Prelim Actual vs Forecast.xls", _
fileformat:=xlworkbooknormal

I'm just a beginner at writing macros. And I'm really lost with proper
syntax in order to point to the desired directory location where we need the
file saved.

I recorded the macro, and then edited respective to tab names, etc. I'm
thinking it's maybe the syntax around the TimePeriod string. I'm also not
familiar with all the SaveAs conditions.

Here's the statement that where I get the error:

ActiveWorkbook.SaveAs([Q:\Finance\Forecast\2007\"&(TimePeriod"\TRG\Prelim
Actual vs
Forecast],[.xls],[""],[Normal],[""],[""],[False],[False],[""],[""],[""],[""])

 

[Guest]

Thanks Dave works great....the TimePeriod format is a text value, so your
first "fix" worked wonderfully!
The only thing that was tricky is that there had to be a space before and
after the & operator ie; " & TimePeriod & "

Thank you So Much!!

Instead of using positional parameters (who remembers what order they go in???),
you can use keywords. VBA's help for .saveas shows them:

activeworkbook.saveas _
filename:="Q:\Finance\Forecast\2007\" & TimePeriod & _
"\TRG\Prelim Actual vs Forecast.xls", _
fileformat:=xlworkbooknormal

And depending on what timeperiod is, you may have to format it.

If it's a date, then your filename can't contain slashes--if it's a time, then
the filename can't contain the colons--something like:

activeworkbook.saveas _
filename:="Q:\Finance\Forecast\2007\" & format(TimePeriod, "yyyymmdd") & _
"\TRG\Prelim Actual vs Forecast.xls", _
fileformat:=xlworkbooknormal

I'm just a beginner at writing macros. And I'm really lost with proper
syntax in order to point to the desired directory location where we need the
file saved.

I recorded the macro, and then edited respective to tab names, etc. I'm
thinking it's maybe the syntax around the TimePeriod string. I'm also not
familiar with all the SaveAs conditions.

Here's the statement that where I get the error:

ActiveWorkbook.SaveAs([Q:\Finance\Forecast\2007\"&(TimePeriod"\TRG\Prelim
Actual vs
Forecast],[.xls],[""],[Normal],[""],[""],[False],[False],[""],[""],[""],[""])