Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to save as
Hi below is the code I use to create a macro for when pressed the user is
asked to save as. Is they anyway I can call the file a certain cell name for example what ever is in A7 and also state the date instead of the user typing the file name. Also I would like to default the location to a specific place. 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 flToSave = Application.GetSaveAsFilename(flName, filefilter:="Excel Files (*.xls),*.xls", _ Title:="Save File As...") If flToSave = False Then Exit Sub Else ThisWorkbook.SaveAs Filename:=flToSave, FileFormat:=flFormat |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to save as
Somthing like the below
Dim strFolder As String Dim strFile As String strFolder = "c:\" strFile = Worksheets("Sheet1").Range("A1") ThisWorkbook.SaveAs Filename:=strFolder & strFile, _ FileFormat:=ActiveWorkbook.FileFormat If this post helps click Yes --------------- Jacob Skaria "Neil Holden" wrote: Hi below is the code I use to create a macro for when pressed the user is asked to save as. Is they anyway I can call the file a certain cell name for example what ever is in A7 and also state the date instead of the user typing the file name. Also I would like to default the location to a specific place. 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 flToSave = Application.GetSaveAsFilename(flName, filefilter:="Excel Files (*.xls),*.xls", _ Title:="Save File As...") If flToSave = False Then Exit Sub Else ThisWorkbook.SaveAs Filename:=flToSave, FileFormat:=flFormat |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to save as
You're mixing ThisWorkbook and Activeworkbook. I'd be more careful with those.
The activeworkbook doesn't need to be ThisWorkbook. Option Explicit Sub testme01() Dim flToSave As String Dim flFormat As Long With ActiveWorkbook 'or thisworkbook flFormat = .FileFormat 'include a path, date, extension???? flToSave = .Path & "\" & .Worksheets("somesheet").Range("A7").Value _ & "_" & Format(Date, "yyyymmdd") & ".xls" 'no prompt for overwriting an existing file??? Application.DisplayAlerts = False On Error Resume Next 'just in case the name isn't valid .SaveAs Filename:=flToSave, FileFormat:=flFormat If Err.Number < 0 Then MsgBox "not saved" & vbLf & Err.Number & vbLf & Err.Description Err.Clear End If On Error GoTo 0 Application.DisplayAlerts = True End With End Sub Neil Holden wrote: Hi below is the code I use to create a macro for when pressed the user is asked to save as. Is they anyway I can call the file a certain cell name for example what ever is in A7 and also state the date instead of the user typing the file name. Also I would like to default the location to a specific place. 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 flToSave = Application.GetSaveAsFilename(flName, filefilter:="Excel Files (*.xls),*.xls", _ Title:="Save File As...") If flToSave = False Then Exit Sub Else ThisWorkbook.SaveAs Filename:=flToSave, FileFormat:=flFormat -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to Insert Current Date into cell - Macro to "Save As" | Excel Worksheet Functions | |||
Macro Will Not Save | Excel Discussion (Misc queries) | |||
MACRO SAVE HELP | Excel Worksheet Functions | |||
Macro to Save without the Save Message | Excel Discussion (Misc queries) | |||
Macro to save | Excel Worksheet Functions |