SAVE AS MACRO
Hello Gurus, I am trying to create a macro so when the user clicks the save
as command box will appear and then they type in the file name and save. The code below brings the save as prompt up but doesn't save : ( Please help. Dim flToSave As Variant 'brings up save as dialogue filling in file name with Job number Dim flName As String Dim flFormat As Long flFormat = ActiveWorkbook.FileFormat flName = Sheets("summary").Range("f7").Value & _ " " & Sheets("summary").Range("f8").Value & " " & "CHR for Monthly Update" & ".xls" flToSave = Application.GetSaveAsFilename(flName, filefilter:="Excel Files (*.xls),*.xls", _ Title:="Save File As...") |
SAVE AS MACRO
Add this code to the bottom
ActiveWorkbook.SaveAs flToSave, flFormat OR ActiveWorkbook.SaveCopyAs flToSave If this post helps click Yes --------------- Jacob Skaria "Neil Holden" wrote: Hello Gurus, I am trying to create a macro so when the user clicks the save as command box will appear and then they type in the file name and save. The code below brings the save as prompt up but doesn't save : ( Please help. Dim flToSave As Variant 'brings up save as dialogue filling in file name with Job number Dim flName As String Dim flFormat As Long flFormat = ActiveWorkbook.FileFormat flName = Sheets("summary").Range("f7").Value & _ " " & Sheets("summary").Range("f8").Value & " " & "CHR for Monthly Update" & ".xls" flToSave = Application.GetSaveAsFilename(flName, filefilter:="Excel Files (*.xls),*.xls", _ Title:="Save File As...") |
SAVE AS MACRO
That just gets a path, you have to explicitly save it
Activeworkbook.SaveAs flToSave -- __________________________________ HTH Bob "Neil Holden" wrote in message ... Hello Gurus, I am trying to create a macro so when the user clicks the save as command box will appear and then they type in the file name and save. The code below brings the save as prompt up but doesn't save : ( Please help. Dim flToSave As Variant 'brings up save as dialogue filling in file name with Job number Dim flName As String Dim flFormat As Long flFormat = ActiveWorkbook.FileFormat flName = Sheets("summary").Range("f7").Value & _ " " & Sheets("summary").Range("f8").Value & " " & "CHR for Monthly Update" & ".xls" flToSave = Application.GetSaveAsFilename(flName, filefilter:="Excel Files (*.xls),*.xls", _ Title:="Save File As...") |
SAVE AS MACRO
Thanks guys, that was helpful!!! BUT... lol the annoying thing is now is
when the button is: The lazy users have to click to the desired location (which will be in the same folder) The path is: M:\Contract\Current\Nationwide\Templates\Project Brief&SOR\Project Briefs to be Approved prior to sending inc master SOR Project brief/ Then they type the name of the file in. Is they anyway it can default to that location and then the users type in the save as name. Thanks again. "Bob Phillips" wrote: That just gets a path, you have to explicitly save it Activeworkbook.SaveAs flToSave -- __________________________________ HTH Bob "Neil Holden" wrote in message ... Hello Gurus, I am trying to create a macro so when the user clicks the save as command box will appear and then they type in the file name and save. The code below brings the save as prompt up but doesn't save : ( Please help. Dim flToSave As Variant 'brings up save as dialogue filling in file name with Job number Dim flName As String Dim flFormat As Long flFormat = ActiveWorkbook.FileFormat flName = Sheets("summary").Range("f7").Value & _ " " & Sheets("summary").Range("f8").Value & " " & "CHR for Monthly Update" & ".xls" flToSave = Application.GetSaveAsFilename(flName, filefilter:="Excel Files (*.xls),*.xls", _ Title:="Save File As...") |
SAVE AS MACRO
Dim flToSave As String
Dim flName As String Dim flFormat As Long flFormat = ActiveWorkbook.FileFormat flName = Sheets("summary").Range("f7").Value & _ " " & Sheets("summary").Range("f8").Value & " " & "CHR for Monthly Update" & ".xls" 'If it is same as the current workbook flToSave = ActiveWorkbook.Path OR 'If it is a different path assign to that variable flToSave = "m:\contract\" ActiveWorkbook.SaveAs flToSave & "\" & flName, flFormat OR ActiveWorkbook.SaveCopyAs flToSave & "\" & flName If this post helps click Yes --------------- Jacob Skaria "Neil Holden" wrote: Thanks guys, that was helpful!!! BUT... lol the annoying thing is now is when the button is: The lazy users have to click to the desired location (which will be in the same folder) The path is: M:\Contract\Current\Nationwide\Templates\Project Brief&SOR\Project Briefs to be Approved prior to sending inc master SOR Project brief/ Then they type the name of the file in. Is they anyway it can default to that location and then the users type in the save as name. Thanks again. "Bob Phillips" wrote: That just gets a path, you have to explicitly save it Activeworkbook.SaveAs flToSave -- __________________________________ HTH Bob "Neil Holden" wrote in message ... Hello Gurus, I am trying to create a macro so when the user clicks the save as command box will appear and then they type in the file name and save. The code below brings the save as prompt up but doesn't save : ( Please help. Dim flToSave As Variant 'brings up save as dialogue filling in file name with Job number Dim flName As String Dim flFormat As Long flFormat = ActiveWorkbook.FileFormat flName = Sheets("summary").Range("f7").Value & _ " " & Sheets("summary").Range("f8").Value & " " & "CHR for Monthly Update" & ".xls" flToSave = Application.GetSaveAsFilename(flName, filefilter:="Excel Files (*.xls),*.xls", _ Title:="Save File As...") |
SAVE AS MACRO
Dim flToSave As Variant
Dim flName As String Dim flFormat As Long ChDir ThisWorkbook.Path flFormat = ActiveWorkbook.FileFormat flName = Sheets("summary").Range("f7").Value & _ " " & Sheets("summary").Range("f8").Value & " " & "CHR for Monthly Update" & ".xls" flToSave = Application.GetSaveAsFilename(flName, filefilter:="Excel Files (*.xls),*.xls", _ Title:="Save File As...") -- __________________________________ HTH Bob "Neil Holden" wrote in message ... Thanks guys, that was helpful!!! BUT... lol the annoying thing is now is when the button is: The lazy users have to click to the desired location (which will be in the same folder) The path is: M:\Contract\Current\Nationwide\Templates\Project Brief&SOR\Project Briefs to be Approved prior to sending inc master SOR Project brief/ Then they type the name of the file in. Is they anyway it can default to that location and then the users type in the save as name. Thanks again. "Bob Phillips" wrote: That just gets a path, you have to explicitly save it Activeworkbook.SaveAs flToSave -- __________________________________ HTH Bob "Neil Holden" wrote in message ... Hello Gurus, I am trying to create a macro so when the user clicks the save as command box will appear and then they type in the file name and save. The code below brings the save as prompt up but doesn't save : ( Please help. Dim flToSave As Variant 'brings up save as dialogue filling in file name with Job number Dim flName As String Dim flFormat As Long flFormat = ActiveWorkbook.FileFormat flName = Sheets("summary").Range("f7").Value & _ " " & Sheets("summary").Range("f8").Value & " " & "CHR for Monthly Update" & ".xls" flToSave = Application.GetSaveAsFilename(flName, filefilter:="Excel Files (*.xls),*.xls", _ Title:="Save File As...") |
All times are GMT +1. The time now is 05:32 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com