ExcelBanter - Input to Save As Macro

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Input to Save As Macro (https://www.excelbanter.com/excel-programming/419535-input-save-macro.html)

Beep Beep

Input to Save As Macro

I have a macro that is calling 3 other macros to do certain things and the
last part of this would be to save the file as another name. I want the
directory that is shown, however I need to be able to change the last part of
the file name i.e. (POST_PHYSICAL_INV_REPORT_84_20080111_20080202.xls ") For
all the files the name will be the same except for the (number 84) in the
middle. This number ranges from 1 to 500. Anyway I can have an input macro
for this particular save as.

ChDir "E:\Charlotte Russe\Excel\January 2008 Reports\Save As Examples"
ActiveWorkbook.SaveAs Filename:= _
"E:\Charlotte Russe\Excel\January 2008 Reports\Save As
Examples\POST_PHYSICAL_INV_REPORT_84_20080111_2008 0202.xls" _
, FileFormat:=xlExcel5, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

Dave Peterson

Input to Save As Macro

dim myNum as long
dim myFileName as string
dim myFolder as string

myNum = clng(application.inputbox(Prompt:="enter a number", type:=1)
if myNum < 1 _
or mynum 500 then
msgbox "try again later"
exit sub
end if

myfolder = "E:\Charlotte Russe\Excel\January 2008 Reports\Save As Examples\"
if right(myfolder,1) < "\" then
myfolder = myfolder & "\"
end if

myfilename = myfolder & "POST_PHYSICAL_INV_REPORT_" _
& myNum _
& "_20080111_20080202.xls"

activeworkbook.saveas filename:=myfilename, _
FileFormat:=xlExcel5, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

if you wanted leading 0's in that "84" portion, you could use:
myfilename = myfolder & "POST_PHYSICAL_INV_REPORT_" _
& format(myNum,"000") _
& "_20080111_20080202.xls"

ps.
You don't need the ChDir line in your code. The .saveas is sufficient.

Beep Beep wrote:

I have a macro that is calling 3 other macros to do certain things and the
last part of this would be to save the file as another name. I want the
directory that is shown, however I need to be able to change the last part of
the file name i.e. (POST_PHYSICAL_INV_REPORT_84_20080111_20080202.xls ") For
all the files the name will be the same except for the (number 84) in the
middle. This number ranges from 1 to 500. Anyway I can have an input macro
for this particular save as.

ChDir "E:\Charlotte Russe\Excel\January 2008 Reports\Save As Examples"
ActiveWorkbook.SaveAs Filename:= _
"E:\Charlotte Russe\Excel\January 2008 Reports\Save As
Examples\POST_PHYSICAL_INV_REPORT_84_20080111_2008 0202.xls" _
, FileFormat:=xlExcel5, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

--

Dave Peterson

Beep Beep

Input to Save As Macro

Hey Dave:

Got the following error message at the line starting at:

myNum = clng(application.inputbox(Prompt:="enter a number", type:=1)

Compile Error
Syntex Error

"Dave Peterson" wrote:

dim myNum as long
dim myFileName as string
dim myFolder as string

myNum = clng(application.inputbox(Prompt:="enter a number", type:=1)
if myNum < 1 _
or mynum 500 then
msgbox "try again later"
exit sub
end if

myfolder = "E:\Charlotte Russe\Excel\January 2008 Reports\Save As Examples\"
if right(myfolder,1) < "\" then
myfolder = myfolder & "\"
end if

myfilename = myfolder & "POST_PHYSICAL_INV_REPORT_" _
& myNum _
& "_20080111_20080202.xls"

activeworkbook.saveas filename:=myfilename, _
FileFormat:=xlExcel5, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

if you wanted leading 0's in that "84" portion, you could use:
myfilename = myfolder & "POST_PHYSICAL_INV_REPORT_" _
& format(myNum,"000") _
& "_20080111_20080202.xls"

ps.
You don't need the ChDir line in your code. The .saveas is sufficient.

Beep Beep wrote:

I have a macro that is calling 3 other macros to do certain things and the
last part of this would be to save the file as another name. I want the
directory that is shown, however I need to be able to change the last part of
the file name i.e. (POST_PHYSICAL_INV_REPORT_84_20080111_20080202.xls ") For
all the files the name will be the same except for the (number 84) in the
middle. This number ranges from 1 to 500. Anyway I can have an input macro
for this particular save as.

ChDir "E:\Charlotte Russe\Excel\January 2008 Reports\Save As Examples"
ActiveWorkbook.SaveAs Filename:= _
"E:\Charlotte Russe\Excel\January 2008 Reports\Save As
Examples\POST_PHYSICAL_INV_REPORT_84_20080111_2008 0202.xls" _
, FileFormat:=xlExcel5, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

--

Dave Peterson

GTVT06

Input to Save As Macro

On Nov 4, 4:04*pm, Beep Beep
wrote:
I have a macro that is calling 3 other macros to do certain things and the
last part of this would be to save the file as another name. *I want the
directory that is shown, however I need to be able to change the last part of
the file name i.e. (POST_PHYSICAL_INV_REPORT_84_20080111_20080202.xls ") For
all the files the name will be the same except for the (number 84) in the
middle. This number ranges from 1 to 500. *Anyway I can have an input macro
for this particular save as.

ChDir "E:\Charlotte Russe\Excel\January 2008 Reports\Save As Examples"
* * ActiveWorkbook.SaveAs Filename:= _
* * * * "E:\Charlotte Russe\Excel\January 2008 Reports\Save As
Examples\POST_PHYSICAL_INV_REPORT_84_20080111_2008 0202.xls" _
* * * * , FileFormat:=xlExcel5, Password:="", WriteResPassword:="", _
* * * * ReadOnlyRecommended:=False, CreateBackup:=False

hell try:
Sub Test()
Dim i As String
i = InputBox("File Numer")
ActiveWorkbook.SaveAs Filename:= _
"E:\Charlotte Russe\Excel\January 2008 Reports\Save As Examples
\POST_PHYSICAL_INV_REPORT_" & i & "_20080111_20080202.xls" _
, FileFormat:=xlExcel5, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

Gord Dibben

Input to Save As Macro

Try adding a missed parenthesis at end

myNum = CLng(Application.InputBox(Prompt:="enter a number", Type:=1))

Works for me.

Gord Dibben MS Excel MVP

On Tue, 04 Nov 2008 16:25:04 -0600, Dave Peterson
wrote:

myNum = clng(application.inputbox(Prompt:="enter a number", type:=1)

Beep Beep

Input to Save As Macro

I get the same error message at this point:

\POST_PHYSICAL_INV_REPORT_" & i & "_20080111_20080202.xls" _
, FileFormat:=xlExcel5, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
"GTVT06" wrote:

On Nov 4, 4:04 pm, Beep Beep
wrote:
I have a macro that is calling 3 other macros to do certain things and the
last part of this would be to save the file as another name. I want the
directory that is shown, however I need to be able to change the last part of
the file name i.e. (POST_PHYSICAL_INV_REPORT_84_20080111_20080202.xls ") For
all the files the name will be the same except for the (number 84) in the
middle. This number ranges from 1 to 500. Anyway I can have an input macro
for this particular save as.

ChDir "E:\Charlotte Russe\Excel\January 2008 Reports\Save As Examples"
ActiveWorkbook.SaveAs Filename:= _
"E:\Charlotte Russe\Excel\January 2008 Reports\Save As
Examples\POST_PHYSICAL_INV_REPORT_84_20080111_2008 0202.xls" _
, FileFormat:=xlExcel5, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

hell try:
Sub Test()
Dim i As String
i = InputBox("File Numer")
ActiveWorkbook.SaveAs Filename:= _
"E:\Charlotte Russe\Excel\January 2008 Reports\Save As Examples
\POST_PHYSICAL_INV_REPORT_" & i & "_20080111_20080202.xls" _
, FileFormat:=xlExcel5, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

Dave Peterson

Input to Save As Macro

Thanks for the correction, Gord.

Gord Dibben wrote:

Try adding a missed parenthesis at end

myNum = CLng(Application.InputBox(Prompt:="enter a number", Type:=1))

Works for me.

Gord Dibben MS Excel MVP

On Tue, 04 Nov 2008 16:25:04 -0600, Dave Peterson
wrote:

myNum = clng(application.inputbox(Prompt:="enter a number", type:=1)

--

Dave Peterson

Gord Dibben

Input to Save As Macro

You have been hit by line-wrap

Try this....................

Sub Test()
Dim i As String
i = InputBox("File Numer")
ActiveWorkbook.SaveAs Filename:= _
"E:\Charlotte Russe\Excel\January 2008 Reports\Save As Examples" & _
"\POST_PHYSICAL_INV_REPORT_" & i & "_20080111_20080202.xls" _
, FileFormat:=xlExcel5, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

Gord

On Tue, 4 Nov 2008 15:09:00 -0800, Beep Beep
wrote:

I get the same error message at this point:

\POST_PHYSICAL_INV_REPORT_" & i & "_20080111_20080202.xls" _
, FileFormat:=xlExcel5, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
"GTVT06" wrote:

On Nov 4, 4:04 pm, Beep Beep
wrote:
I have a macro that is calling 3 other macros to do certain things and the
last part of this would be to save the file as another name. I want the
directory that is shown, however I need to be able to change the last part of
the file name i.e. (POST_PHYSICAL_INV_REPORT_84_20080111_20080202.xls ") For
all the files the name will be the same except for the (number 84) in the
middle. This number ranges from 1 to 500. Anyway I can have an input macro
for this particular save as.

ChDir "E:\Charlotte Russe\Excel\January 2008 Reports\Save As Examples"
ActiveWorkbook.SaveAs Filename:= _
"E:\Charlotte Russe\Excel\January 2008 Reports\Save As
Examples\POST_PHYSICAL_INV_REPORT_84_20080111_2008 0202.xls" _
, FileFormat:=xlExcel5, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

hell try:
Sub Test()
Dim i As String
i = InputBox("File Numer")
ActiveWorkbook.SaveAs Filename:= _
"E:\Charlotte Russe\Excel\January 2008 Reports\Save As Examples
\POST_PHYSICAL_INV_REPORT_" & i & "_20080111_20080202.xls" _
, FileFormat:=xlExcel5, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

Gord Dibben

Input to Save As Macro

de nada

Bloody news readers always leaving things out<g

Gord

On Tue, 04 Nov 2008 17:31:47 -0600, Dave Peterson
wrote:

Thanks for the correction, Gord.

Gord Dibben wrote:

Try adding a missed parenthesis at end

myNum = CLng(Application.InputBox(Prompt:="enter a number", Type:=1))

Works for me.

Gord Dibben MS Excel MVP

On Tue, 04 Nov 2008 16:25:04 -0600, Dave Peterson
wrote:

myNum = clng(application.inputbox(Prompt:="enter a number", type:=1)

Dave Peterson

Input to Save As Macro

Or bloody newsgroup posters!

<vbg

Gord Dibben wrote:

de nada

Bloody news readers always leaving things out<g

Gord

On Tue, 04 Nov 2008 17:31:47 -0600, Dave Peterson
wrote:

Thanks for the correction, Gord.

Gord Dibben wrote:

Try adding a missed parenthesis at end

myNum = CLng(Application.InputBox(Prompt:="enter a number", Type:=1))

Works for me.

Gord Dibben MS Excel MVP

On Tue, 04 Nov 2008 16:25:04 -0600, Dave Peterson
wrote:

myNum = clng(application.inputbox(Prompt:="enter a number", type:=1)

--

Dave Peterson

All times are GMT +1. The time now is 05:33 PM.