Input to Save As Macro

[Beep Beep]

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_20080202.xls" _
, FileFormat:=xlExcel5, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

 

[Dave Peterson]

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]

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

 

[GTVT06]

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_20080202.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]

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

 

[Beep Beep]

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

 

[Dave Peterson]

Thanks for the correction, Gord.

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

 

[Gord Dibben]

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

 

[Gord Dibben]

de nada

Bloody news readers always leaving things out<g>


Gord

 

[Dave Peterson]

Or bloody newsgroup posters!

<vbg>

de nada

Bloody news readers always leaving things out<g>

Gord