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!

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

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
>

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

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
>

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

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
>