New Spreadsheet Name

[eyeman6513_2000]

I have a list of doctors that I need to create templates for and then
refresh the background query (the spreadsheet is linked to SQL) . I
have the logic worked out, the only problem that I am having is that
when it comes to saving the file name, I have the doctors name in Cell
A5, this is a dynmic field based on the query, how I do fix the
following line of code to tell it to look at A5 for the file name? I
thought I would be able to copy from cell A5 and paste it into the file
name, but that is not working, any other suggestions?

The line of code to save the workbook is:

ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\Richard
Sabbara\Desktop\Range('C1')", FileFormat _
:=xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False

Any help is greatly appericated.

Richard

 

[NickHK]

Dim Path As String
Path=""C:\Documents and Settings\Richard Sabbara\Desktop\

With ActiveWorkbook
.SaveAs Filename:=Path & .Worksheets("WhichSheet").Range("C1").Value &
".xls", _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False

Obviously change 'WhichSheet" to worksheet in question.

NickHK

 

[Mark]

I have a list of doctors that I need to create templates for and then
refresh the background query (the spreadsheet is linked to SQL) . I
have the logic worked out, the only problem that I am having is that
when it comes to saving the file name, I have the doctors name in Cell
A5, this is a dynmic field based on the query, how I do fix the
following line of code to tell it to look at A5 for the file name? I
thought I would be able to copy from cell A5 and paste it into the file
name, but that is not working, any other suggestions?

The line of code to save the workbook is:

ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\Richard
Sabbara\Desktop\Range('C1')", FileFormat _
:=xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False

I am a bit rusty with this but I think your using incorrect syntax for
what you want to do with Range('C1'). Try the following:

Dim CurRng As Range
Set CurRng = Range("C1")
ActiveWorkbook.SaveAs Filename:="C:\Documents and
Settings\RichardSabbara\Desktop\" & CurRng, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False, CreateBackup:=False

The reason I use a variable for Range("C1") is because I find it
IMPOSSIBLE to figure out the correct syntax for all of the quotation
marks that occur within the string. Hope that gets it for ya...