Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
No file when Save As is executed
With the changes from your first reply, when I do a save as and go to the
folder to check, the workbook still does not exist? I changed SaveName to Variant as well as NewName to Variant, would that cause the problem? Thanks again for you assistance. "Dave Peterson" wrote: You can copy a worksheet to a new workbook and save that workbook. Is that what you meant? Something like this may get you going: Option Explicit Sub testme() Dim wks As Worksheet Set wks = ActiveWorkbook.Worksheets("sheet1") wks.Copy 'to a new workbook With ActiveSheet.Parent .SaveAs Filename:="hithere" .Close savechanges:=False End With End Sub If you're going to overwrite an existing file, put: application.displayalerts = false ..saveas filename:=.... application.displayalerts = true to suppress any "are you sure" prompt. D.Parker wrote: Dave: Is there a way to save a particular worksheet into a new workbook? Otherwise, I will just have to password protect the code in the current workbook. Thanks again! "Dave Peterson" wrote: This line: SaveName = Application.GetSaveAsFilename _ (NewName, fileFilter:=fFilter) only returns the name of the file the user chose--it doesn't do the actual save. Sub RenameFilenameUponClose() Dim SaveName As variant '<--changed Dim fFilter As String Dim NewName As String NewName = "P2 LogHistory Shift" fFilter = "Excel Files (*.xls), *.xls" SaveName = Application.GetSaveAsFilename _ (NewName, fileFilter:=fFilter) if savename = false then 'use cancelled--what to do? else thisworkbook.saveas filename:=savename, fileformat:=xlworkbooknormal end if End Sub I change SaveName from a String to Variant--so that it could represent the boolean value False, too. D.Parker wrote: I am trying to save a worksheet as a separate workbook. I am manipulating code from a pervious response. The Save As form comes up and I can set a file name and choose a path, but when I click the save button there is no file in my designated folder. The Save As is good since the user will be changing the filename each time upon exiting. I'm assuming I missing some code somewhere? Secondly, is there a way to save the worksheet object as opposed to the entire workbook (i.e. save the worksheet into a new workbook, less the VBA code preferrably)? Sub RenameFilenameUponClose() Dim SaveName As String Dim fFilter As String Dim NewName As String NewName = "P2 LogHistory Shift" fFilter = "Excel Files (*.xls), *.xls" SaveName = Application.GetSaveAsFilename _ (NewName, fileFilter:=fFilter) End Sub Your help is greatly appreciated as always. -- Dave Peterson -- Dave Peterson |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
No file when Save As is executed
I think if you post your current code, it would be easier to guess.
D.Parker wrote: With the changes from your first reply, when I do a save as and go to the folder to check, the workbook still does not exist? I changed SaveName to Variant as well as NewName to Variant, would that cause the problem? Thanks again for you assistance. "Dave Peterson" wrote: You can copy a worksheet to a new workbook and save that workbook. Is that what you meant? Something like this may get you going: Option Explicit Sub testme() Dim wks As Worksheet Set wks = ActiveWorkbook.Worksheets("sheet1") wks.Copy 'to a new workbook With ActiveSheet.Parent .SaveAs Filename:="hithere" .Close savechanges:=False End With End Sub If you're going to overwrite an existing file, put: application.displayalerts = false ..saveas filename:=.... application.displayalerts = true to suppress any "are you sure" prompt. D.Parker wrote: Dave: Is there a way to save a particular worksheet into a new workbook? Otherwise, I will just have to password protect the code in the current workbook. Thanks again! "Dave Peterson" wrote: This line: SaveName = Application.GetSaveAsFilename _ (NewName, fileFilter:=fFilter) only returns the name of the file the user chose--it doesn't do the actual save. Sub RenameFilenameUponClose() Dim SaveName As variant '<--changed Dim fFilter As String Dim NewName As String NewName = "P2 LogHistory Shift" fFilter = "Excel Files (*.xls), *.xls" SaveName = Application.GetSaveAsFilename _ (NewName, fileFilter:=fFilter) if savename = false then 'use cancelled--what to do? else thisworkbook.saveas filename:=savename, fileformat:=xlworkbooknormal end if End Sub I change SaveName from a String to Variant--so that it could represent the boolean value False, too. D.Parker wrote: I am trying to save a worksheet as a separate workbook. I am manipulating code from a pervious response. The Save As form comes up and I can set a file name and choose a path, but when I click the save button there is no file in my designated folder. The Save As is good since the user will be changing the filename each time upon exiting. I'm assuming I missing some code somewhere? Secondly, is there a way to save the worksheet object as opposed to the entire workbook (i.e. save the worksheet into a new workbook, less the VBA code preferrably)? Sub RenameFilenameUponClose() Dim SaveName As String Dim fFilter As String Dim NewName As String NewName = "P2 LogHistory Shift" fFilter = "Excel Files (*.xls), *.xls" SaveName = Application.GetSaveAsFilename _ (NewName, fileFilter:=fFilter) End Sub Your help is greatly appreciated as always. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
No file when Save As is executed
Hello again, here is my current code. I made NewName a Variant since the
filename entered by the user can have alpha characters as well as numerics. I thought I manipulated the IF..Then to reflect your changes also, could that be the problem also? Thank you. Sub RenameFilenameUponClose() Dim SaveName As Variant Dim fFilter As String Dim NewName As Variant NewName = "P2 LogHistory Shift" fFilter = "Excel Files (*.xls), *.xls" SaveName = Application.GetSaveAsFilename _ (NewName, fileFilter:=fFilter) If SaveName = True Then ThisWorkbook.SaveAs Filename:=SaveName, _ FileFormat:=xlWorkbookNormal End If End Sub "Dave Peterson" wrote: I think if you post your current code, it would be easier to guess. D.Parker wrote: With the changes from your first reply, when I do a save as and go to the folder to check, the workbook still does not exist? I changed SaveName to Variant as well as NewName to Variant, would that cause the problem? Thanks again for you assistance. "Dave Peterson" wrote: You can copy a worksheet to a new workbook and save that workbook. Is that what you meant? Something like this may get you going: Option Explicit Sub testme() Dim wks As Worksheet Set wks = ActiveWorkbook.Worksheets("sheet1") wks.Copy 'to a new workbook With ActiveSheet.Parent .SaveAs Filename:="hithere" .Close savechanges:=False End With End Sub If you're going to overwrite an existing file, put: application.displayalerts = false ..saveas filename:=.... application.displayalerts = true to suppress any "are you sure" prompt. D.Parker wrote: Dave: Is there a way to save a particular worksheet into a new workbook? Otherwise, I will just have to password protect the code in the current workbook. Thanks again! "Dave Peterson" wrote: This line: SaveName = Application.GetSaveAsFilename _ (NewName, fileFilter:=fFilter) only returns the name of the file the user chose--it doesn't do the actual save. Sub RenameFilenameUponClose() Dim SaveName As variant '<--changed Dim fFilter As String Dim NewName As String NewName = "P2 LogHistory Shift" fFilter = "Excel Files (*.xls), *.xls" SaveName = Application.GetSaveAsFilename _ (NewName, fileFilter:=fFilter) if savename = false then 'use cancelled--what to do? else thisworkbook.saveas filename:=savename, fileformat:=xlworkbooknormal end if End Sub I change SaveName from a String to Variant--so that it could represent the boolean value False, too. D.Parker wrote: I am trying to save a worksheet as a separate workbook. I am manipulating code from a pervious response. The Save As form comes up and I can set a file name and choose a path, but when I click the save button there is no file in my designated folder. The Save As is good since the user will be changing the filename each time upon exiting. I'm assuming I missing some code somewhere? Secondly, is there a way to save the worksheet object as opposed to the entire workbook (i.e. save the worksheet into a new workbook, less the VBA code preferrably)? Sub RenameFilenameUponClose() Dim SaveName As String Dim fFilter As String Dim NewName As String NewName = "P2 LogHistory Shift" fFilter = "Excel Files (*.xls), *.xls" SaveName = Application.GetSaveAsFilename _ (NewName, fileFilter:=fFilter) End Sub Your help is greatly appreciated as always. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
No file when Save As is executed
I think the problem is the True portion:
Option Explicit Sub RenameFilenameUponClose() Dim SaveName As Variant Dim fFilter As String Dim NewName As Variant NewName = "P2 LogHistory Shift" fFilter = "Excel Files (*.xls), *.xls" SaveName = Application.GetSaveAsFilename _ (NewName, fileFilter:=fFilter) If SaveName = False Then 'do nothing Else ThisWorkbook.SaveAs Filename:=SaveName, _ FileFormat:=xlWorkbookNormal End If End Sub SaveName will be the filename if the user clicks Save. It'll be False if they hit cancel. D.Parker wrote: Hello again, here is my current code. I made NewName a Variant since the filename entered by the user can have alpha characters as well as numerics. I thought I manipulated the IF..Then to reflect your changes also, could that be the problem also? Thank you. Sub RenameFilenameUponClose() Dim SaveName As Variant Dim fFilter As String Dim NewName As Variant NewName = "P2 LogHistory Shift" fFilter = "Excel Files (*.xls), *.xls" SaveName = Application.GetSaveAsFilename _ (NewName, fileFilter:=fFilter) If SaveName = True Then ThisWorkbook.SaveAs Filename:=SaveName, _ FileFormat:=xlWorkbookNormal End If End Sub "Dave Peterson" wrote: I think if you post your current code, it would be easier to guess. D.Parker wrote: With the changes from your first reply, when I do a save as and go to the folder to check, the workbook still does not exist? I changed SaveName to Variant as well as NewName to Variant, would that cause the problem? Thanks again for you assistance. "Dave Peterson" wrote: You can copy a worksheet to a new workbook and save that workbook. Is that what you meant? Something like this may get you going: Option Explicit Sub testme() Dim wks As Worksheet Set wks = ActiveWorkbook.Worksheets("sheet1") wks.Copy 'to a new workbook With ActiveSheet.Parent .SaveAs Filename:="hithere" .Close savechanges:=False End With End Sub If you're going to overwrite an existing file, put: application.displayalerts = false ..saveas filename:=.... application.displayalerts = true to suppress any "are you sure" prompt. D.Parker wrote: Dave: Is there a way to save a particular worksheet into a new workbook? Otherwise, I will just have to password protect the code in the current workbook. Thanks again! "Dave Peterson" wrote: This line: SaveName = Application.GetSaveAsFilename _ (NewName, fileFilter:=fFilter) only returns the name of the file the user chose--it doesn't do the actual save. Sub RenameFilenameUponClose() Dim SaveName As variant '<--changed Dim fFilter As String Dim NewName As String NewName = "P2 LogHistory Shift" fFilter = "Excel Files (*.xls), *.xls" SaveName = Application.GetSaveAsFilename _ (NewName, fileFilter:=fFilter) if savename = false then 'use cancelled--what to do? else thisworkbook.saveas filename:=savename, fileformat:=xlworkbooknormal end if End Sub I change SaveName from a String to Variant--so that it could represent the boolean value False, too. D.Parker wrote: I am trying to save a worksheet as a separate workbook. I am manipulating code from a pervious response. The Save As form comes up and I can set a file name and choose a path, but when I click the save button there is no file in my designated folder. The Save As is good since the user will be changing the filename each time upon exiting. I'm assuming I missing some code somewhere? Secondly, is there a way to save the worksheet object as opposed to the entire workbook (i.e. save the worksheet into a new workbook, less the VBA code preferrably)? Sub RenameFilenameUponClose() Dim SaveName As String Dim fFilter As String Dim NewName As String NewName = "P2 LogHistory Shift" fFilter = "Excel Files (*.xls), *.xls" SaveName = Application.GetSaveAsFilename _ (NewName, fileFilter:=fFilter) End Sub Your help is greatly appreciated as always. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
No file when Save As is executed
Dave, thank you for your response, but with the changes, there is still no
file save when I execute the code. The Save As window pops up, I can select a directory, the Cancel works okay, but when I use the same filename or a different filename in the window, nothing is getting saved. Any other ideas are greatly appreciated? Thank you. "Dave Peterson" wrote: I think the problem is the True portion: Option Explicit Sub RenameFilenameUponClose() Dim SaveName As Variant Dim fFilter As String Dim NewName As Variant NewName = "P2 LogHistory Shift" fFilter = "Excel Files (*.xls), *.xls" SaveName = Application.GetSaveAsFilename _ (NewName, fileFilter:=fFilter) If SaveName = False Then 'do nothing Else ThisWorkbook.SaveAs Filename:=SaveName, _ FileFormat:=xlWorkbookNormal End If End Sub SaveName will be the filename if the user clicks Save. It'll be False if they hit cancel. D.Parker wrote: Hello again, here is my current code. I made NewName a Variant since the filename entered by the user can have alpha characters as well as numerics. I thought I manipulated the IF..Then to reflect your changes also, could that be the problem also? Thank you. Sub RenameFilenameUponClose() Dim SaveName As Variant Dim fFilter As String Dim NewName As Variant NewName = "P2 LogHistory Shift" fFilter = "Excel Files (*.xls), *.xls" SaveName = Application.GetSaveAsFilename _ (NewName, fileFilter:=fFilter) If SaveName = True Then ThisWorkbook.SaveAs Filename:=SaveName, _ FileFormat:=xlWorkbookNormal End If End Sub "Dave Peterson" wrote: I think if you post your current code, it would be easier to guess. D.Parker wrote: With the changes from your first reply, when I do a save as and go to the folder to check, the workbook still does not exist? I changed SaveName to Variant as well as NewName to Variant, would that cause the problem? Thanks again for you assistance. "Dave Peterson" wrote: You can copy a worksheet to a new workbook and save that workbook. Is that what you meant? Something like this may get you going: Option Explicit Sub testme() Dim wks As Worksheet Set wks = ActiveWorkbook.Worksheets("sheet1") wks.Copy 'to a new workbook With ActiveSheet.Parent .SaveAs Filename:="hithere" .Close savechanges:=False End With End Sub If you're going to overwrite an existing file, put: application.displayalerts = false ..saveas filename:=.... application.displayalerts = true to suppress any "are you sure" prompt. D.Parker wrote: Dave: Is there a way to save a particular worksheet into a new workbook? Otherwise, I will just have to password protect the code in the current workbook. Thanks again! "Dave Peterson" wrote: This line: SaveName = Application.GetSaveAsFilename _ (NewName, fileFilter:=fFilter) only returns the name of the file the user chose--it doesn't do the actual save. Sub RenameFilenameUponClose() Dim SaveName As variant '<--changed Dim fFilter As String Dim NewName As String NewName = "P2 LogHistory Shift" fFilter = "Excel Files (*.xls), *.xls" SaveName = Application.GetSaveAsFilename _ (NewName, fileFilter:=fFilter) if savename = false then 'use cancelled--what to do? else thisworkbook.saveas filename:=savename, fileformat:=xlworkbooknormal end if End Sub I change SaveName from a String to Variant--so that it could represent the boolean value False, too. D.Parker wrote: I am trying to save a worksheet as a separate workbook. I am manipulating code from a pervious response. The Save As form comes up and I can set a file name and choose a path, but when I click the save button there is no file in my designated folder. The Save As is good since the user will be changing the filename each time upon exiting. I'm assuming I missing some code somewhere? Secondly, is there a way to save the worksheet object as opposed to the entire workbook (i.e. save the worksheet into a new workbook, less the VBA code preferrably)? Sub RenameFilenameUponClose() Dim SaveName As String Dim fFilter As String Dim NewName As String NewName = "P2 LogHistory Shift" fFilter = "Excel Files (*.xls), *.xls" SaveName = Application.GetSaveAsFilename _ (NewName, fileFilter:=fFilter) End Sub Your help is greatly appreciated as always. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
No file when Save As is executed
It worked fine for me. I got a workbook named "P2 Loghistory shift.xls".
Did you make any other changes that may have broken the macro? ps. This code uses ThisWorkbook. It's gonna save the workbook that owns the code--not the activeworkbook. (But it does save something for me.) D.Parker wrote: Dave, thank you for your response, but with the changes, there is still no file save when I execute the code. The Save As window pops up, I can select a directory, the Cancel works okay, but when I use the same filename or a different filename in the window, nothing is getting saved. Any other ideas are greatly appreciated? Thank you. "Dave Peterson" wrote: I think the problem is the True portion: Option Explicit Sub RenameFilenameUponClose() Dim SaveName As Variant Dim fFilter As String Dim NewName As Variant NewName = "P2 LogHistory Shift" fFilter = "Excel Files (*.xls), *.xls" SaveName = Application.GetSaveAsFilename _ (NewName, fileFilter:=fFilter) If SaveName = False Then 'do nothing Else ThisWorkbook.SaveAs Filename:=SaveName, _ FileFormat:=xlWorkbookNormal End If End Sub SaveName will be the filename if the user clicks Save. It'll be False if they hit cancel. D.Parker wrote: Hello again, here is my current code. I made NewName a Variant since the filename entered by the user can have alpha characters as well as numerics. I thought I manipulated the IF..Then to reflect your changes also, could that be the problem also? Thank you. Sub RenameFilenameUponClose() Dim SaveName As Variant Dim fFilter As String Dim NewName As Variant NewName = "P2 LogHistory Shift" fFilter = "Excel Files (*.xls), *.xls" SaveName = Application.GetSaveAsFilename _ (NewName, fileFilter:=fFilter) If SaveName = True Then ThisWorkbook.SaveAs Filename:=SaveName, _ FileFormat:=xlWorkbookNormal End If End Sub "Dave Peterson" wrote: I think if you post your current code, it would be easier to guess. D.Parker wrote: With the changes from your first reply, when I do a save as and go to the folder to check, the workbook still does not exist? I changed SaveName to Variant as well as NewName to Variant, would that cause the problem? Thanks again for you assistance. "Dave Peterson" wrote: You can copy a worksheet to a new workbook and save that workbook. Is that what you meant? Something like this may get you going: Option Explicit Sub testme() Dim wks As Worksheet Set wks = ActiveWorkbook.Worksheets("sheet1") wks.Copy 'to a new workbook With ActiveSheet.Parent .SaveAs Filename:="hithere" .Close savechanges:=False End With End Sub If you're going to overwrite an existing file, put: application.displayalerts = false ..saveas filename:=.... application.displayalerts = true to suppress any "are you sure" prompt. D.Parker wrote: Dave: Is there a way to save a particular worksheet into a new workbook? Otherwise, I will just have to password protect the code in the current workbook. Thanks again! "Dave Peterson" wrote: This line: SaveName = Application.GetSaveAsFilename _ (NewName, fileFilter:=fFilter) only returns the name of the file the user chose--it doesn't do the actual save. Sub RenameFilenameUponClose() Dim SaveName As variant '<--changed Dim fFilter As String Dim NewName As String NewName = "P2 LogHistory Shift" fFilter = "Excel Files (*.xls), *.xls" SaveName = Application.GetSaveAsFilename _ (NewName, fileFilter:=fFilter) if savename = false then 'use cancelled--what to do? else thisworkbook.saveas filename:=savename, fileformat:=xlworkbooknormal end if End Sub I change SaveName from a String to Variant--so that it could represent the boolean value False, too. D.Parker wrote: I am trying to save a worksheet as a separate workbook. I am manipulating code from a pervious response. The Save As form comes up and I can set a file name and choose a path, but when I click the save button there is no file in my designated folder. The Save As is good since the user will be changing the filename each time upon exiting. I'm assuming I missing some code somewhere? Secondly, is there a way to save the worksheet object as opposed to the entire workbook (i.e. save the worksheet into a new workbook, less the VBA code preferrably)? Sub RenameFilenameUponClose() Dim SaveName As String Dim fFilter As String Dim NewName As String NewName = "P2 LogHistory Shift" fFilter = "Excel Files (*.xls), *.xls" SaveName = Application.GetSaveAsFilename _ (NewName, fileFilter:=fFilter) End Sub Your help is greatly appreciated as always. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
No file when Save As is executed
Ahhh, maybe therein lies the problem. I was putting the code in a module
instead of the ThisWorkbook object of the VBA Project. Could that be it? I had a button on my spreadsheet that launches the "RenameFilenameUponClose" macro. Other than that, that's all I have. No other changes other than what you specify. I apologize for the many strings of replies, but your assistance is superb! Thank you so much!!! Kind regards, D.Parker "Dave Peterson" wrote: It worked fine for me. I got a workbook named "P2 Loghistory shift.xls". Did you make any other changes that may have broken the macro? ps. This code uses ThisWorkbook. It's gonna save the workbook that owns the code--not the activeworkbook. (But it does save something for me.) D.Parker wrote: Dave, thank you for your response, but with the changes, there is still no file save when I execute the code. The Save As window pops up, I can select a directory, the Cancel works okay, but when I use the same filename or a different filename in the window, nothing is getting saved. Any other ideas are greatly appreciated? Thank you. "Dave Peterson" wrote: I think the problem is the True portion: Option Explicit Sub RenameFilenameUponClose() Dim SaveName As Variant Dim fFilter As String Dim NewName As Variant NewName = "P2 LogHistory Shift" fFilter = "Excel Files (*.xls), *.xls" SaveName = Application.GetSaveAsFilename _ (NewName, fileFilter:=fFilter) If SaveName = False Then 'do nothing Else ThisWorkbook.SaveAs Filename:=SaveName, _ FileFormat:=xlWorkbookNormal End If End Sub SaveName will be the filename if the user clicks Save. It'll be False if they hit cancel. D.Parker wrote: Hello again, here is my current code. I made NewName a Variant since the filename entered by the user can have alpha characters as well as numerics. I thought I manipulated the IF..Then to reflect your changes also, could that be the problem also? Thank you. Sub RenameFilenameUponClose() Dim SaveName As Variant Dim fFilter As String Dim NewName As Variant NewName = "P2 LogHistory Shift" fFilter = "Excel Files (*.xls), *.xls" SaveName = Application.GetSaveAsFilename _ (NewName, fileFilter:=fFilter) If SaveName = True Then ThisWorkbook.SaveAs Filename:=SaveName, _ FileFormat:=xlWorkbookNormal End If End Sub "Dave Peterson" wrote: I think if you post your current code, it would be easier to guess. D.Parker wrote: With the changes from your first reply, when I do a save as and go to the folder to check, the workbook still does not exist? I changed SaveName to Variant as well as NewName to Variant, would that cause the problem? Thanks again for you assistance. "Dave Peterson" wrote: You can copy a worksheet to a new workbook and save that workbook. Is that what you meant? Something like this may get you going: Option Explicit Sub testme() Dim wks As Worksheet Set wks = ActiveWorkbook.Worksheets("sheet1") wks.Copy 'to a new workbook With ActiveSheet.Parent .SaveAs Filename:="hithere" .Close savechanges:=False End With End Sub If you're going to overwrite an existing file, put: application.displayalerts = false ..saveas filename:=.... application.displayalerts = true to suppress any "are you sure" prompt. D.Parker wrote: Dave: Is there a way to save a particular worksheet into a new workbook? Otherwise, I will just have to password protect the code in the current workbook. Thanks again! "Dave Peterson" wrote: This line: SaveName = Application.GetSaveAsFilename _ (NewName, fileFilter:=fFilter) only returns the name of the file the user chose--it doesn't do the actual save. Sub RenameFilenameUponClose() Dim SaveName As variant '<--changed Dim fFilter As String Dim NewName As String NewName = "P2 LogHistory Shift" fFilter = "Excel Files (*.xls), *.xls" SaveName = Application.GetSaveAsFilename _ (NewName, fileFilter:=fFilter) if savename = false then 'use cancelled--what to do? else thisworkbook.saveas filename:=savename, fileformat:=xlworkbooknormal end if End Sub I change SaveName from a String to Variant--so that it could represent the boolean value False, too. D.Parker wrote: I am trying to save a worksheet as a separate workbook. I am manipulating code from a pervious response. The Save As form comes up and I can set a file name and choose a path, but when I click the save button there is no file in my designated folder. The Save As is good since the user will be changing the filename each time upon exiting. I'm assuming I missing some code somewhere? Secondly, is there a way to save the worksheet object as opposed to the entire workbook (i.e. save the worksheet into a new workbook, less the VBA code preferrably)? Sub RenameFilenameUponClose() Dim SaveName As String Dim fFilter As String Dim NewName As String NewName = "P2 LogHistory Shift" fFilter = "Excel Files (*.xls), *.xls" SaveName = Application.GetSaveAsFilename _ (NewName, fileFilter:=fFilter) End Sub Your help is greatly appreciated as always. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
No file when Save As is executed
Nope. You were doing it ok--that code should go into a general module.
It sounds like you used a button from the forms toolbar. Are you sure that button's macro is the correct version of your macro (just in case you had multiple versions in different modules????) D.Parker wrote: Ahhh, maybe therein lies the problem. I was putting the code in a module instead of the ThisWorkbook object of the VBA Project. Could that be it? I had a button on my spreadsheet that launches the "RenameFilenameUponClose" macro. Other than that, that's all I have. No other changes other than what you specify. I apologize for the many strings of replies, but your assistance is superb! Thank you so much!!! Kind regards, D.Parker "Dave Peterson" wrote: It worked fine for me. I got a workbook named "P2 Loghistory shift.xls". Did you make any other changes that may have broken the macro? ps. This code uses ThisWorkbook. It's gonna save the workbook that owns the code--not the activeworkbook. (But it does save something for me.) D.Parker wrote: Dave, thank you for your response, but with the changes, there is still no file save when I execute the code. The Save As window pops up, I can select a directory, the Cancel works okay, but when I use the same filename or a different filename in the window, nothing is getting saved. Any other ideas are greatly appreciated? Thank you. "Dave Peterson" wrote: I think the problem is the True portion: Option Explicit Sub RenameFilenameUponClose() Dim SaveName As Variant Dim fFilter As String Dim NewName As Variant NewName = "P2 LogHistory Shift" fFilter = "Excel Files (*.xls), *.xls" SaveName = Application.GetSaveAsFilename _ (NewName, fileFilter:=fFilter) If SaveName = False Then 'do nothing Else ThisWorkbook.SaveAs Filename:=SaveName, _ FileFormat:=xlWorkbookNormal End If End Sub SaveName will be the filename if the user clicks Save. It'll be False if they hit cancel. D.Parker wrote: Hello again, here is my current code. I made NewName a Variant since the filename entered by the user can have alpha characters as well as numerics. I thought I manipulated the IF..Then to reflect your changes also, could that be the problem also? Thank you. Sub RenameFilenameUponClose() Dim SaveName As Variant Dim fFilter As String Dim NewName As Variant NewName = "P2 LogHistory Shift" fFilter = "Excel Files (*.xls), *.xls" SaveName = Application.GetSaveAsFilename _ (NewName, fileFilter:=fFilter) If SaveName = True Then ThisWorkbook.SaveAs Filename:=SaveName, _ FileFormat:=xlWorkbookNormal End If End Sub "Dave Peterson" wrote: I think if you post your current code, it would be easier to guess. D.Parker wrote: With the changes from your first reply, when I do a save as and go to the folder to check, the workbook still does not exist? I changed SaveName to Variant as well as NewName to Variant, would that cause the problem? Thanks again for you assistance. "Dave Peterson" wrote: You can copy a worksheet to a new workbook and save that workbook. Is that what you meant? Something like this may get you going: Option Explicit Sub testme() Dim wks As Worksheet Set wks = ActiveWorkbook.Worksheets("sheet1") wks.Copy 'to a new workbook With ActiveSheet.Parent .SaveAs Filename:="hithere" .Close savechanges:=False End With End Sub If you're going to overwrite an existing file, put: application.displayalerts = false ..saveas filename:=.... application.displayalerts = true to suppress any "are you sure" prompt. D.Parker wrote: Dave: Is there a way to save a particular worksheet into a new workbook? Otherwise, I will just have to password protect the code in the current workbook. Thanks again! "Dave Peterson" wrote: This line: SaveName = Application.GetSaveAsFilename _ (NewName, fileFilter:=fFilter) only returns the name of the file the user chose--it doesn't do the actual save. Sub RenameFilenameUponClose() Dim SaveName As variant '<--changed Dim fFilter As String Dim NewName As String NewName = "P2 LogHistory Shift" fFilter = "Excel Files (*.xls), *.xls" SaveName = Application.GetSaveAsFilename _ (NewName, fileFilter:=fFilter) if savename = false then 'use cancelled--what to do? else thisworkbook.saveas filename:=savename, fileformat:=xlworkbooknormal end if End Sub I change SaveName from a String to Variant--so that it could represent the boolean value False, too. D.Parker wrote: I am trying to save a worksheet as a separate workbook. I am manipulating code from a pervious response. The Save As form comes up and I can set a file name and choose a path, but when I click the save button there is no file in my designated folder. The Save As is good since the user will be changing the filename each time upon exiting. I'm assuming I missing some code somewhere? Secondly, is there a way to save the worksheet object as opposed to the entire workbook (i.e. save the worksheet into a new workbook, less the VBA code preferrably)? Sub RenameFilenameUponClose() Dim SaveName As String Dim fFilter As String Dim NewName As String NewName = "P2 LogHistory Shift" fFilter = "Excel Files (*.xls), *.xls" SaveName = Application.GetSaveAsFilename _ (NewName, fileFilter:=fFilter) End Sub Your help is greatly appreciated as always. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
No file when Save As is executed | Excel Programming | |||
Excell2003 (SP-1) File > Save and File > Save As.. grayed out | Excel Discussion (Misc queries) | |||
Excel marcos firing on file save as but not file save | Excel Programming | |||
How to create LOG file for commands executed in VB..? | Excel Programming | |||
Save File to Another Directory, but not change Users File Save location | Excel Programming |