Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use a Macro to Save a Copy of a Workbook?
Hi,
I am trying to streamline my department's scheduling process by using a macro. I want an employee to open a master workbook (master.xls), then enter data, then SaveAs a workbook with a name that is taken from a string in cell A2. I want the last of those three steps to be done automatically using a macro. I am new to programming excel and my current attempts at this macro are a mess of copy-and-pasted code from these message boards. My current code works to some extent. It does what I want it to, but Excel crashes immeadiately after. Can Someone please help me out with some clear code to prevent Excel from crashing after the save? I would also like if Excel did not ask to overwrite a file with the same name... I want it to automatically overwrite without complaining. Thanks, Joe |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use a Macro to Save a Copy of a Workbook?
Joe, try this,
Sub Save_As() Dim SaveName As String On Error GoTo E Application.DisplayAlerts = False SaveName = Range("A2").Value ActiveWorkbook.SaveAs Filename:=(SaveName), FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False E: Application.DisplayAlerts = True End Sub -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Joe D" wrote in message oups.com... Hi, I am trying to streamline my department's scheduling process by using a macro. I want an employee to open a master workbook (master.xls), then enter data, then SaveAs a workbook with a name that is taken from a string in cell A2. I want the last of those three steps to be done automatically using a macro. I am new to programming excel and my current attempts at this macro are a mess of copy-and-pasted code from these message boards. My current code works to some extent. It does what I want it to, but Excel crashes immeadiately after. Can Someone please help me out with some clear code to prevent Excel from crashing after the save? I would also like if Excel did not ask to overwrite a file with the same name... I want it to automatically overwrite without complaining. Thanks, Joe |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use a Macro to Save a Copy of a Workbook?
If you know the folder name where the file will be save you could do it
something like this: Sub SaveFile() Dim Pth As String Dim flName As String Pth = "C:\Temp Folder\" flName = Sheets("Sheet1").Range("A2").Value Application.DisplayAlerts = False ThisWorkbook.SaveAs Pth & flName Application.DisplayAlerts = True End Sub Or you could prompt the user to select a folder using code like this (not this will also allow the user to change the filename): Sub SaveFile2() Dim flName As String Dim fullName As Variant Dim filter As String flName = Sheets("Sheet1").Range("A2").Value filter = "Excel Files (*.xls), *.xls" fullName = Application.GetSaveAsFilename(flName, filter) If fullName < False Then Application.DisplayAlerts = False ThisWorkbook.SaveAs fullName Application.DisplayAlerts = True End If End Sub Hope this helps Rowan "Joe D" wrote: Hi, I am trying to streamline my department's scheduling process by using a macro. I want an employee to open a master workbook (master.xls), then enter data, then SaveAs a workbook with a name that is taken from a string in cell A2. I want the last of those three steps to be done automatically using a macro. I am new to programming excel and my current attempts at this macro are a mess of copy-and-pasted code from these message boards. My current code works to some extent. It does what I want it to, but Excel crashes immeadiately after. Can Someone please help me out with some clear code to prevent Excel from crashing after the save? I would also like if Excel did not ask to overwrite a file with the same name... I want it to automatically overwrite without complaining. Thanks, Joe |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use a Macro to Save a Copy of a Workbook?
Thanks guys so much! I ended up using Rowan's first example because I
did know which folder I was going to save it in. Very slick. My boss loves me now. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trying to use VBA to save a copy of a workbook | Excel Discussion (Misc queries) | |||
Save a copy of a Workbook without its modules | Excel Programming | |||
Save exact copy of workbook | Excel Programming | |||
copy 3 sheets & save them into new workbook | Excel Programming | |||
copy 3 sheets & save them into new workbook | Excel Programming |