Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
run macro with input msg based on cell input | Excel Discussion (Misc queries) | |||
macro to save as a data input by user file name from a designated | Excel Discussion (Misc queries) | |||
how to pause a 'save-as' and continue after input | Excel Programming | |||
Save from form input | Excel Programming | |||
how to save the input of the controls in a form | Excel Programming |