Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saveas Not Working
Hello,
I'm having a problem with SAVEAS in following code. Basically trying to Add a new Workbook (wb2) so that I can move sheets to it from an existing workbook (wb1). When running, I get the dialogue box asking me to save as in the correct directory, and it seems to save, but at the end of the routine the file doesn't exist in the directory. When I step through the code, I can see at the point right after I get the SaveAs Dialogue box (wb2.SaveAs ChosenFileName, xlWorkbookNormal) that the workbook still has it's original default name assigned by the Workbooks.Add command (Book1.xls, or whatever it is). Not sure how to troubleshoot this . . . I don't get any error. Any help is very appreciated. Thanks in advance. Dim CurrentPath As String Dim Filename As Variant 'Store the current path CurrentPath = CurDir 'Change the path to the one we want SetCurrentDirectory "\\SRV1\RATER\Attach PIC Rater" 'setting activeworkbook (self-rater) as wb1 Set wb1 = ActiveWorkbook wb1Name = ActiveWorkbook.Name 'Adding a new workbook to move Quote tab to Dim wb2 As Workbook Set wb2 = Workbooks.Add tamfile = InputBox("Enter your Prospect Code", "Save As Prospect Code", wb1Name) ChosenFileName = Application.GetSaveAsFilename(tamfile, "Excel Files (*.xls),*.xls,", , "Title") wb2.SaveAs ChosenFileName, xlWorkbookNormal |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saveas Not Working
I ran this porftion of your code and it worked perfectly
----------------------------------------------------------------------------------- 'Adding a new workbook to move Quote tab to Dim wb2 As Workbook Set wb2 = Workbooks.Add tamfile = InputBox("Enter your Prospect Code", "Save As Prospect Code", wb1Name) ChosenFileName = Application.GetSaveAsFilename(tamfile, "Excel Files (*.xls),*.xls,", , "Title") wb2.SaveAs ChosenFileName, xlWorkbookNormal ----------------------------------------------------------------------------------------- I would start by trying to save the file to your local c:\folder and see if the same thing happens. Don't change any code just select the C:\drive and save the file in a location you know you have sacved filesd befoire. I think the code is working bt the file is going to another folder. Another way of proving this is to go to the new workbook after your executed the SAVEAS and using the menu File - Saveas Look at the full default folder wherer the file is being saved bu pressing the arrow in the pull down folder menu at the top of the SAVEAS window. "ehale" wrote: Hello, I'm having a problem with SAVEAS in following code. Basically trying to Add a new Workbook (wb2) so that I can move sheets to it from an existing workbook (wb1). When running, I get the dialogue box asking me to save as in the correct directory, and it seems to save, but at the end of the routine the file doesn't exist in the directory. When I step through the code, I can see at the point right after I get the SaveAs Dialogue box (wb2.SaveAs ChosenFileName, xlWorkbookNormal) that the workbook still has it's original default name assigned by the Workbooks.Add command (Book1.xls, or whatever it is). Not sure how to troubleshoot this . . . I don't get any error. Any help is very appreciated. Thanks in advance. Dim CurrentPath As String Dim Filename As Variant 'Store the current path CurrentPath = CurDir 'Change the path to the one we want SetCurrentDirectory "\\SRV1\RATER\Attach PIC Rater" 'setting activeworkbook (self-rater) as wb1 Set wb1 = ActiveWorkbook wb1Name = ActiveWorkbook.Name 'Adding a new workbook to move Quote tab to Dim wb2 As Workbook Set wb2 = Workbooks.Add tamfile = InputBox("Enter your Prospect Code", "Save As Prospect Code", wb1Name) ChosenFileName = Application.GetSaveAsFilename(tamfile, "Excel Files (*.xls),*.xls,", , "Title") wb2.SaveAs ChosenFileName, xlWorkbookNormal |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saveas Not Working
You may want to take a look at this site. In Excel 2007, you need to have a
fileformat that matches the file extension or you get wierd results. I've done it and files seemed to save, but wouldn't open later. http://www.rondebruin.nl/saveas.htm "ehale" wrote: Hello, I'm having a problem with SAVEAS in following code. Basically trying to Add a new Workbook (wb2) so that I can move sheets to it from an existing workbook (wb1). When running, I get the dialogue box asking me to save as in the correct directory, and it seems to save, but at the end of the routine the file doesn't exist in the directory. When I step through the code, I can see at the point right after I get the SaveAs Dialogue box (wb2.SaveAs ChosenFileName, xlWorkbookNormal) that the workbook still has it's original default name assigned by the Workbooks.Add command (Book1.xls, or whatever it is). Not sure how to troubleshoot this . . . I don't get any error. Any help is very appreciated. Thanks in advance. Dim CurrentPath As String Dim Filename As Variant 'Store the current path CurrentPath = CurDir 'Change the path to the one we want SetCurrentDirectory "\\SRV1\RATER\Attach PIC Rater" 'setting activeworkbook (self-rater) as wb1 Set wb1 = ActiveWorkbook wb1Name = ActiveWorkbook.Name 'Adding a new workbook to move Quote tab to Dim wb2 As Workbook Set wb2 = Workbooks.Add tamfile = InputBox("Enter your Prospect Code", "Save As Prospect Code", wb1Name) ChosenFileName = Application.GetSaveAsFilename(tamfile, "Excel Files (*.xls),*.xls,", , "Title") wb2.SaveAs ChosenFileName, xlWorkbookNormal |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saveas Not Working
You may want to take a look at this site. In Excel 2007, you need to have a
fileformat that matches the file extension or you get wierd results. I've done it and files seemed to save, but wouldn't open later. http://www.rondebruin.nl/saveas.htm "ehale" wrote: Hello, I'm having a problem with SAVEAS in following code. Basically trying to Add a new Workbook (wb2) so that I can move sheets to it from an existing workbook (wb1). When running, I get the dialogue box asking me to save as in the correct directory, and it seems to save, but at the end of the routine the file doesn't exist in the directory. When I step through the code, I can see at the point right after I get the SaveAs Dialogue box (wb2.SaveAs ChosenFileName, xlWorkbookNormal) that the workbook still has it's original default name assigned by the Workbooks.Add command (Book1.xls, or whatever it is). Not sure how to troubleshoot this . . . I don't get any error. Any help is very appreciated. Thanks in advance. Dim CurrentPath As String Dim Filename As Variant 'Store the current path CurrentPath = CurDir 'Change the path to the one we want SetCurrentDirectory "\\SRV1\RATER\Attach PIC Rater" 'setting activeworkbook (self-rater) as wb1 Set wb1 = ActiveWorkbook wb1Name = ActiveWorkbook.Name 'Adding a new workbook to move Quote tab to Dim wb2 As Workbook Set wb2 = Workbooks.Add tamfile = InputBox("Enter your Prospect Code", "Save As Prospect Code", wb1Name) ChosenFileName = Application.GetSaveAsFilename(tamfile, "Excel Files (*.xls),*.xls,", , "Title") wb2.SaveAs ChosenFileName, xlWorkbookNormal |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saveas Not Working
I'd guess that either the folder wasn't what you wanted when the user ok'd the
getfilesaveasname dialog or you didn't notice the full name of the workbook: I'd add some msgboxes just to help with the debugging: Dim ChosenFileName As Variant 'could be false if the user cancels .... ChosenFileName = Application.GetSaveAsFilename(InitialFileName:=Tam File, _ filefilter:="Excel Files ,*.xls,", Title:="Save As") If ChosenFileName = False Then 'user hit cancel 'what should happen Else MsgBox ChosenFileName wb2.SaveAs Filename:=ChosenFileName, FileFormat:=xlWorkbookNormal MsgBox wb2.FullName End If ehale wrote: Hello, I'm having a problem with SAVEAS in following code. Basically trying to Add a new Workbook (wb2) so that I can move sheets to it from an existing workbook (wb1). When running, I get the dialogue box asking me to save as in the correct directory, and it seems to save, but at the end of the routine the file doesn't exist in the directory. When I step through the code, I can see at the point right after I get the SaveAs Dialogue box (wb2.SaveAs ChosenFileName, xlWorkbookNormal) that the workbook still has it's original default name assigned by the Workbooks.Add command (Book1.xls, or whatever it is). Not sure how to troubleshoot this . . . I don't get any error. Any help is very appreciated. Thanks in advance. Dim CurrentPath As String Dim Filename As Variant 'Store the current path CurrentPath = CurDir 'Change the path to the one we want SetCurrentDirectory "\\SRV1\RATER\Attach PIC Rater" 'setting activeworkbook (self-rater) as wb1 Set wb1 = ActiveWorkbook wb1Name = ActiveWorkbook.Name 'Adding a new workbook to move Quote tab to Dim wb2 As Workbook Set wb2 = Workbooks.Add tamfile = InputBox("Enter your Prospect Code", "Save As Prospect Code", wb1Name) ChosenFileName = Application.GetSaveAsFilename(tamfile, "Excel Files (*.xls),*.xls,", , "Title") wb2.SaveAs ChosenFileName, xlWorkbookNormal -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saveas Not Working
Hi Joel,
Thanks for your response. I tried saving to C drive with the same results. It actually sometimes saves the file fine but other times doesn't (should have mentioned that in original post). When I see the SaveAs dialogue box, I can see all the other files in the folder that have been sucessfully saved via the same routine so I know the path is correct. I click ok after entering the desired file name, but the file simply retains the generic "Book1.xls" name and isn't saved in the folder. No error. I decided to change the path of the file from the absolute path //srv01 . . .. to R:/Attach . . . I also moved the saveas to the end of the routine. So far all my tests have been sucessful, but I'll see what happens tomorrow when users give it a try. Not too optimistic since I still don't know what the problem is! Thanks again, Erica "joel" wrote: I ran this porftion of your code and it worked perfectly ----------------------------------------------------------------------------------- 'Adding a new workbook to move Quote tab to Dim wb2 As Workbook Set wb2 = Workbooks.Add tamfile = InputBox("Enter your Prospect Code", "Save As Prospect Code", wb1Name) ChosenFileName = Application.GetSaveAsFilename(tamfile, "Excel Files (*.xls),*.xls,", , "Title") wb2.SaveAs ChosenFileName, xlWorkbookNormal ----------------------------------------------------------------------------------------- I would start by trying to save the file to your local c:\folder and see if the same thing happens. Don't change any code just select the C:\drive and save the file in a location you know you have sacved filesd befoire. I think the code is working bt the file is going to another folder. Another way of proving this is to go to the new workbook after your executed the SAVEAS and using the menu File - Saveas Look at the full default folder wherer the file is being saved bu pressing the arrow in the pull down folder menu at the top of the SAVEAS window. "ehale" wrote: Hello, I'm having a problem with SAVEAS in following code. Basically trying to Add a new Workbook (wb2) so that I can move sheets to it from an existing workbook (wb1). When running, I get the dialogue box asking me to save as in the correct directory, and it seems to save, but at the end of the routine the file doesn't exist in the directory. When I step through the code, I can see at the point right after I get the SaveAs Dialogue box (wb2.SaveAs ChosenFileName, xlWorkbookNormal) that the workbook still has it's original default name assigned by the Workbooks.Add command (Book1.xls, or whatever it is). Not sure how to troubleshoot this . . . I don't get any error. Any help is very appreciated. Thanks in advance. Dim CurrentPath As String Dim Filename As Variant 'Store the current path CurrentPath = CurDir 'Change the path to the one we want SetCurrentDirectory "\\SRV1\RATER\Attach PIC Rater" 'setting activeworkbook (self-rater) as wb1 Set wb1 = ActiveWorkbook wb1Name = ActiveWorkbook.Name 'Adding a new workbook to move Quote tab to Dim wb2 As Workbook Set wb2 = Workbooks.Add tamfile = InputBox("Enter your Prospect Code", "Save As Prospect Code", wb1Name) ChosenFileName = Application.GetSaveAsFilename(tamfile, "Excel Files (*.xls),*.xls,", , "Title") wb2.SaveAs ChosenFileName, xlWorkbookNormal |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saveas Not Working
Thanks Barb -- using older version of Excel.
"Barb Reinhardt" wrote: You may want to take a look at this site. In Excel 2007, you need to have a fileformat that matches the file extension or you get wierd results. I've done it and files seemed to save, but wouldn't open later. http://www.rondebruin.nl/saveas.htm "ehale" wrote: Hello, I'm having a problem with SAVEAS in following code. Basically trying to Add a new Workbook (wb2) so that I can move sheets to it from an existing workbook (wb1). When running, I get the dialogue box asking me to save as in the correct directory, and it seems to save, but at the end of the routine the file doesn't exist in the directory. When I step through the code, I can see at the point right after I get the SaveAs Dialogue box (wb2.SaveAs ChosenFileName, xlWorkbookNormal) that the workbook still has it's original default name assigned by the Workbooks.Add command (Book1.xls, or whatever it is). Not sure how to troubleshoot this . . . I don't get any error. Any help is very appreciated. Thanks in advance. Dim CurrentPath As String Dim Filename As Variant 'Store the current path CurrentPath = CurDir 'Change the path to the one we want SetCurrentDirectory "\\SRV1\RATER\Attach PIC Rater" 'setting activeworkbook (self-rater) as wb1 Set wb1 = ActiveWorkbook wb1Name = ActiveWorkbook.Name 'Adding a new workbook to move Quote tab to Dim wb2 As Workbook Set wb2 = Workbooks.Add tamfile = InputBox("Enter your Prospect Code", "Save As Prospect Code", wb1Name) ChosenFileName = Application.GetSaveAsFilename(tamfile, "Excel Files (*.xls),*.xls,", , "Title") wb2.SaveAs ChosenFileName, xlWorkbookNormal |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saveas Not Working
Hi Dave,
Thanks for responding -- message boxes are a good idea. The Saveas worked initially, then one user started having trouble and suddenly multiple users (including me) had trouble. Now, It's back to working with identical code and message boxes are confirming path and all are correct. When the problem returns, which I'm guessing it will, I will use the message boxes to see what's going on. Thanks, Erica "Dave Peterson" wrote: I'd guess that either the folder wasn't what you wanted when the user ok'd the getfilesaveasname dialog or you didn't notice the full name of the workbook: I'd add some msgboxes just to help with the debugging: Dim ChosenFileName As Variant 'could be false if the user cancels .... ChosenFileName = Application.GetSaveAsFilename(InitialFileName:=Tam File, _ filefilter:="Excel Files ,*.xls,", Title:="Save As") If ChosenFileName = False Then 'user hit cancel 'what should happen Else MsgBox ChosenFileName wb2.SaveAs Filename:=ChosenFileName, FileFormat:=xlWorkbookNormal MsgBox wb2.FullName End If ehale wrote: Hello, I'm having a problem with SAVEAS in following code. Basically trying to Add a new Workbook (wb2) so that I can move sheets to it from an existing workbook (wb1). When running, I get the dialogue box asking me to save as in the correct directory, and it seems to save, but at the end of the routine the file doesn't exist in the directory. When I step through the code, I can see at the point right after I get the SaveAs Dialogue box (wb2.SaveAs ChosenFileName, xlWorkbookNormal) that the workbook still has it's original default name assigned by the Workbooks.Add command (Book1.xls, or whatever it is). Not sure how to troubleshoot this . . . I don't get any error. Any help is very appreciated. Thanks in advance. Dim CurrentPath As String Dim Filename As Variant 'Store the current path CurrentPath = CurDir 'Change the path to the one we want SetCurrentDirectory "\\SRV1\RATER\Attach PIC Rater" 'setting activeworkbook (self-rater) as wb1 Set wb1 = ActiveWorkbook wb1Name = ActiveWorkbook.Name 'Adding a new workbook to move Quote tab to Dim wb2 As Workbook Set wb2 = Workbooks.Add tamfile = InputBox("Enter your Prospect Code", "Save As Prospect Code", wb1Name) ChosenFileName = Application.GetSaveAsFilename(tamfile, "Excel Files (*.xls),*.xls,", , "Title") wb2.SaveAs ChosenFileName, xlWorkbookNormal -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
saveas ActiveWorkbook.SaveAs Filename:=Range("A1").Value | Excel Programming | |||
Calculate working days but change working week | Excel Discussion (Misc queries) | |||
BeforeSave event fired but not working when triggered by SaveAs. | Excel Programming | |||
SaveAs another file name, then keep working | Excel Programming | |||
Help with SaveAs | Excel Programming |