VBA for saving a file to a specific location
 

I'm using Excel 2003 and I'm recording a macro.
At the end of the macro I need to save the file to a specific location and
use the concatenation of two range names for the file name.
Does anyone know how the code should look like for this assuming:
location is "L:\Clients\symbol\"
range names are "month_entry" & "year_entry"
Please help!

VBA for saving a file to a specific location
 

Dim myFileName as string
with worksheets("sheet1")
myfilename = .range("month_entry").value & .range("year_entry").value
end with

Then use myfilename in your SaveAs line.

TimT wrote:

I'm using Excel 2003 and I'm recording a macro.
At the end of the macro I need to save the file to a specific location and
use the concatenation of two range names for the file name.
Does anyone know how the code should look like for this assuming:
location is "L:\Clients\symbol\"
range names are "month_entry" & "year_entry"
Please help!

--

Dave Peterson

VBA for saving a file to a specific location
 

Thanks!
What about making it default to .prn file format?

"Dave Peterson" wrote:

Dim myFileName as string
with worksheets("sheet1")
myfilename = .range("month_entry").value & .range("year_entry").value
end with

Then use myfilename in your SaveAs line.

TimT wrote:

I'm using Excel 2003 and I'm recording a macro.
At the end of the macro I need to save the file to a specific location and
use the concatenation of two range names for the file name.
Does anyone know how the code should look like for this assuming:
location is "L:\Clients\symbol\"
range names are "month_entry" & "year_entry"
Please help!

--

Dave Peterson

VBA for saving a file to a specific location
 

Record a macro when you save it as .prn and you'll see the code.

I got this:

ActiveWorkbook.SaveAs Filename:="C:\My Documents\excel\Book1.prn", _
FileFormat:=xlTextPrinter, CreateBackup:=False

So I could just change the filename...

ActiveWorkbook.SaveAs Filename:=myfilename, _
FileFormat:=xlTextPrinter, CreateBackup:=False

Remember to activate the correct worksheet first. (.Prn's will only save the
activesheet.)

TimT wrote:

Thanks!
What about making it default to .prn file format?

"Dave Peterson" wrote:

Dim myFileName as string
with worksheets("sheet1")
myfilename = .range("month_entry").value & .range("year_entry").value
end with

Then use myfilename in your SaveAs line.

TimT wrote:

I'm using Excel 2003 and I'm recording a macro.
At the end of the macro I need to save the file to a specific location and
use the concatenation of two range names for the file name.
Does anyone know how the code should look like for this assuming:
location is "L:\Clients\symbol\"
range names are "month_entry" & "year_entry"
Please help!

--

Dave Peterson


--

Dave Peterson

VBA for saving a file to a specific location
 

I need help on the syntax:
how come when I replace the filename "book1.prn" with "filename" (the
string) it saves it as "filename" and not the concatenation that I declared
it to be?

Dim fname As String
With Worksheets("intro")

fname = "Symbol_" & .Range("month_entry").Value &
..Range("yr_entry").Value
End With

ChDir "L:\Tax Department\Clients\Symbol Technologies\Adjustment Imports"
ActiveWorkbook.SaveAs filename:= _
"L:\Tax Department\Clients\Symbol Technologies\Adjustment
Imports\fname", _
FileFormat:=xlTextPrinter, CreateBackup:=False








"Dave Peterson" wrote:

Record a macro when you save it as .prn and you'll see the code.

I got this:

ActiveWorkbook.SaveAs Filename:="C:\My Documents\excel\Book1.prn", _
FileFormat:=xlTextPrinter, CreateBackup:=False

So I could just change the filename...

ActiveWorkbook.SaveAs Filename:=myfilename, _
FileFormat:=xlTextPrinter, CreateBackup:=False

Remember to activate the correct worksheet first. (.Prn's will only save the
activesheet.)

TimT wrote:

Thanks!
What about making it default to .prn file format?

"Dave Peterson" wrote:

Dim myFileName as string
with worksheets("sheet1")
myfilename = .range("month_entry").value & .range("year_entry").value
end with

Then use myfilename in your SaveAs line.

TimT wrote:

I'm using Excel 2003 and I'm recording a macro.
At the end of the macro I need to save the file to a specific location and
use the concatenation of two range names for the file name.
Does anyone know how the code should look like for this assuming:
location is "L:\Clients\symbol\"
range names are "month_entry" & "year_entry"
Please help!

--

Dave Peterson


--

Dave Peterson

VBA for saving a file to a specific location
 

Maybe...

'delete the chdir line, you don't need it
ActiveWorkbook.SaveAs filename:= _
"L:\Tax Department\Clients\Symbol Technologies\Adjustment Imports\" _
& fname, _
FileFormat:=xlTextPrinter, CreateBackup:=False

By including the fname inside the quotes, excel saw it as part of the
string--not a variable.


TimT wrote:

I need help on the syntax:
how come when I replace the filename "book1.prn" with "filename" (the
string) it saves it as "filename" and not the concatenation that I declared
it to be?

Dim fname As String
With Worksheets("intro")

fname = "Symbol_" & .Range("month_entry").Value &
.Range("yr_entry").Value
End With

ChDir "L:\Tax Department\Clients\Symbol Technologies\Adjustment Imports"
ActiveWorkbook.SaveAs filename:= _
"L:\Tax Department\Clients\Symbol Technologies\Adjustment
Imports\fname", _
FileFormat:=xlTextPrinter, CreateBackup:=False


"Dave Peterson" wrote:

Record a macro when you save it as .prn and you'll see the code.

I got this:

ActiveWorkbook.SaveAs Filename:="C:\My Documents\excel\Book1.prn", _
FileFormat:=xlTextPrinter, CreateBackup:=False

So I could just change the filename...

ActiveWorkbook.SaveAs Filename:=myfilename, _
FileFormat:=xlTextPrinter, CreateBackup:=False

Remember to activate the correct worksheet first. (.Prn's will only save the
activesheet.)

TimT wrote:

Thanks!
What about making it default to .prn file format?

"Dave Peterson" wrote:

Dim myFileName as string
with worksheets("sheet1")
myfilename = .range("month_entry").value & .range("year_entry").value
end with

Then use myfilename in your SaveAs line.

TimT wrote:

I'm using Excel 2003 and I'm recording a macro.
At the end of the macro I need to save the file to a specific location and
use the concatenation of two range names for the file name.
Does anyone know how the code should look like for this assuming:
location is "L:\Clients\symbol\"
range names are "month_entry" & "year_entry"
Please help!

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

VBA for saving a file to a specific location
 

Works awsome thank you so much Dave!

"Dave Peterson" wrote:

Maybe...

'delete the chdir line, you don't need it
ActiveWorkbook.SaveAs filename:= _
"L:\Tax Department\Clients\Symbol Technologies\Adjustment Imports\" _
& fname, _
FileFormat:=xlTextPrinter, CreateBackup:=False

By including the fname inside the quotes, excel saw it as part of the
string--not a variable.


TimT wrote:

I need help on the syntax:
how come when I replace the filename "book1.prn" with "filename" (the
string) it saves it as "filename" and not the concatenation that I declared
it to be?

Dim fname As String
With Worksheets("intro")

fname = "Symbol_" & .Range("month_entry").Value &
.Range("yr_entry").Value
End With

ChDir "L:\Tax Department\Clients\Symbol Technologies\Adjustment Imports"
ActiveWorkbook.SaveAs filename:= _
"L:\Tax Department\Clients\Symbol Technologies\Adjustment
Imports\fname", _
FileFormat:=xlTextPrinter, CreateBackup:=False


"Dave Peterson" wrote:

Record a macro when you save it as .prn and you'll see the code.

I got this:

ActiveWorkbook.SaveAs Filename:="C:\My Documents\excel\Book1.prn", _
FileFormat:=xlTextPrinter, CreateBackup:=False

So I could just change the filename...

ActiveWorkbook.SaveAs Filename:=myfilename, _
FileFormat:=xlTextPrinter, CreateBackup:=False

Remember to activate the correct worksheet first. (.Prn's will only save the
activesheet.)

TimT wrote:

Thanks!
What about making it default to .prn file format?

"Dave Peterson" wrote:

Dim myFileName as string
with worksheets("sheet1")
myfilename = .range("month_entry").value & .range("year_entry").value
end with

Then use myfilename in your SaveAs line.

TimT wrote:

I'm using Excel 2003 and I'm recording a macro.
At the end of the macro I need to save the file to a specific location and
use the concatenation of two range names for the file name.
Does anyone know how the code should look like for this assuming:
location is "L:\Clients\symbol\"
range names are "month_entry" & "year_entry"
Please help!

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson