ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   SAVE AS MACRO (https://www.excelbanter.com/excel-programming/432867-save-macro.html)

Neil Holden
SAVE AS MACRO
 
Please see below, this is a button for when pressed a save as dialog appears
with the name of whatever appears in C7 and the current date, but when i
press ok to save nothing happens!! Please help i'm so close to finishing.

Dim Response As String
Dim msg As String
Dim Style As String
Dim sFilename As String
Dim ans
Dim flToSave As Variant
Dim flName As String
Dim flFormat As Long


msg = "Are you sure you want to save the Smith quote?"
Style = vbYesNo + vbInformation + vbDefaultButton2

Response = MsgBox(msg, Style)
If Response = vbYes Then

flFormat = ActiveWorkbook.FileFormat

DefaultFolder = "M:\Design"
If Right(DefaultFolder, 1) < "\" Then
DefaultFolder = DefaultFolder & "\"
End If

DefaultFilename = Range("C7")
If Right(UCase(DefaultFilename), 2) < "XLS" Then
DefaultFilename = DefaultFilename & Format(Date, "ddmmyyyy") & ".xls"
'DefaultFilename = DefaultFilename & ".xls"
End If



'Create a FileDialog object as a File Picker dialog box.
Set fd = Nothing
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
..InitialFileName = DefaultFolder & DefaultFilename
..Filters.Add "Excel Files", "*.xls", 1
..Title = "Save File As..."
..Show
If .SelectedItems.Count = 0 Then
Exit Sub
End If

Jacob Skaria
SAVE AS MACRO
 
Have you missed to copy the last few lines from Joel's code ?

Try with this line... which save the active workbook as the filename in the
variable

ActiveWorkbook.SaveAs Filename:=flToSave, FileFormat:=flFormat

If this post helps click Yes
---------------
Jacob Skaria


"Neil Holden" wrote:

Please see below, this is a button for when pressed a save as dialog appears
with the name of whatever appears in C7 and the current date, but when i
press ok to save nothing happens!! Please help i'm so close to finishing.

Dim Response As String
Dim msg As String
Dim Style As String
Dim sFilename As String
Dim ans
Dim flToSave As Variant
Dim flName As String
Dim flFormat As Long


msg = "Are you sure you want to save the Smith quote?"
Style = vbYesNo + vbInformation + vbDefaultButton2

Response = MsgBox(msg, Style)
If Response = vbYes Then

flFormat = ActiveWorkbook.FileFormat

DefaultFolder = "M:\Design"
If Right(DefaultFolder, 1) < "\" Then
DefaultFolder = DefaultFolder & "\"
End If

DefaultFilename = Range("C7")
If Right(UCase(DefaultFilename), 2) < "XLS" Then
DefaultFilename = DefaultFilename & Format(Date, "ddmmyyyy") & ".xls"
'DefaultFilename = DefaultFilename & ".xls"
End If



'Create a FileDialog object as a File Picker dialog box.
Set fd = Nothing
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.InitialFileName = DefaultFolder & DefaultFilename
.Filters.Add "Excel Files", "*.xls", 1
.Title = "Save File As..."
.Show
If .SelectedItems.Count = 0 Then
Exit Sub
End If

Neil Holden
SAVE AS MACRO
 
That is in my code, not sure what else it could be.

Neil.

"Jacob Skaria" wrote:

Have you missed to copy the last few lines from Joel's code ?

Try with this line... which save the active workbook as the filename in the
variable

ActiveWorkbook.SaveAs Filename:=flToSave, FileFormat:=flFormat

If this post helps click Yes
---------------
Jacob Skaria


"Neil Holden" wrote:

Please see below, this is a button for when pressed a save as dialog appears
with the name of whatever appears in C7 and the current date, but when i
press ok to save nothing happens!! Please help i'm so close to finishing.

Dim Response As String
Dim msg As String
Dim Style As String
Dim sFilename As String
Dim ans
Dim flToSave As Variant
Dim flName As String
Dim flFormat As Long


msg = "Are you sure you want to save the Smith quote?"
Style = vbYesNo + vbInformation + vbDefaultButton2

Response = MsgBox(msg, Style)
If Response = vbYes Then

flFormat = ActiveWorkbook.FileFormat

DefaultFolder = "M:\Design"
If Right(DefaultFolder, 1) < "\" Then
DefaultFolder = DefaultFolder & "\"
End If

DefaultFilename = Range("C7")
If Right(UCase(DefaultFilename), 2) < "XLS" Then
DefaultFilename = DefaultFilename & Format(Date, "ddmmyyyy") & ".xls"
'DefaultFilename = DefaultFilename & ".xls"
End If



'Create a FileDialog object as a File Picker dialog box.
Set fd = Nothing
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.InitialFileName = DefaultFolder & DefaultFilename
.Filters.Add "Excel Files", "*.xls", 1
.Title = "Save File As..."
.Show
If .SelectedItems.Count = 0 Then
Exit Sub
End If

Jacob Skaria
SAVE AS MACRO
 
Please note the difference between the below two

ActiveWorkbook.SaveAs
will save the workbook which is active

ThisWorkbook.SaveAs
will save the workbook in which the macro is

If this post helps click Yes
---------------
Jacob Skaria


"Neil Holden" wrote:

That is in my code, not sure what else it could be.

Neil.

"Jacob Skaria" wrote:

Have you missed to copy the last few lines from Joel's code ?

Try with this line... which save the active workbook as the filename in the
variable

ActiveWorkbook.SaveAs Filename:=flToSave, FileFormat:=flFormat

If this post helps click Yes
---------------
Jacob Skaria


"Neil Holden" wrote:

Please see below, this is a button for when pressed a save as dialog appears
with the name of whatever appears in C7 and the current date, but when i
press ok to save nothing happens!! Please help i'm so close to finishing.

Dim Response As String
Dim msg As String
Dim Style As String
Dim sFilename As String
Dim ans
Dim flToSave As Variant
Dim flName As String
Dim flFormat As Long


msg = "Are you sure you want to save the Smith quote?"
Style = vbYesNo + vbInformation + vbDefaultButton2

Response = MsgBox(msg, Style)
If Response = vbYes Then

flFormat = ActiveWorkbook.FileFormat

DefaultFolder = "M:\Design"
If Right(DefaultFolder, 1) < "\" Then
DefaultFolder = DefaultFolder & "\"
End If

DefaultFilename = Range("C7")
If Right(UCase(DefaultFilename), 2) < "XLS" Then
DefaultFilename = DefaultFilename & Format(Date, "ddmmyyyy") & ".xls"
'DefaultFilename = DefaultFilename & ".xls"
End If



'Create a FileDialog object as a File Picker dialog box.
Set fd = Nothing
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.InitialFileName = DefaultFolder & DefaultFilename
.Filters.Add "Excel Files", "*.xls", 1
.Title = "Save File As..."
.Show
If .SelectedItems.Count = 0 Then
Exit Sub
End If

Patrick Molloy[_2_]
SAVE AS MACRO
 
change

Dim Response As String


to

Dim Response As Long

because vbYes is a LONG not text

"Neil Holden" wrote:

That is in my code, not sure what else it could be.

Neil.

"Jacob Skaria" wrote:

Have you missed to copy the last few lines from Joel's code ?

Try with this line... which save the active workbook as the filename in the
variable

ActiveWorkbook.SaveAs Filename:=flToSave, FileFormat:=flFormat

If this post helps click Yes
---------------
Jacob Skaria


"Neil Holden" wrote:

Please see below, this is a button for when pressed a save as dialog appears
with the name of whatever appears in C7 and the current date, but when i
press ok to save nothing happens!! Please help i'm so close to finishing.

Dim Response As String
Dim msg As String
Dim Style As String
Dim sFilename As String
Dim ans
Dim flToSave As Variant
Dim flName As String
Dim flFormat As Long


msg = "Are you sure you want to save the Smith quote?"
Style = vbYesNo + vbInformation + vbDefaultButton2

Response = MsgBox(msg, Style)
If Response = vbYes Then

flFormat = ActiveWorkbook.FileFormat

DefaultFolder = "M:\Design"
If Right(DefaultFolder, 1) < "\" Then
DefaultFolder = DefaultFolder & "\"
End If

DefaultFilename = Range("C7")
If Right(UCase(DefaultFilename), 2) < "XLS" Then
DefaultFilename = DefaultFilename & Format(Date, "ddmmyyyy") & ".xls"
'DefaultFilename = DefaultFilename & ".xls"
End If



'Create a FileDialog object as a File Picker dialog box.
Set fd = Nothing
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.InitialFileName = DefaultFolder & DefaultFilename
.Filters.Add "Excel Files", "*.xls", 1
.Title = "Save File As..."
.Show
If .SelectedItems.Count = 0 Then
Exit Sub
End If

Neil Holden
SAVE AS MACRO
 
I have tried that but still unsuccessful.

"Patrick Molloy" wrote:

change

Dim Response As String


to

Dim Response As Long

because vbYes is a LONG not text

"Neil Holden" wrote:

That is in my code, not sure what else it could be.

Neil.

"Jacob Skaria" wrote:

Have you missed to copy the last few lines from Joel's code ?

Try with this line... which save the active workbook as the filename in the
variable

ActiveWorkbook.SaveAs Filename:=flToSave, FileFormat:=flFormat

If this post helps click Yes
---------------
Jacob Skaria


"Neil Holden" wrote:

Please see below, this is a button for when pressed a save as dialog appears
with the name of whatever appears in C7 and the current date, but when i
press ok to save nothing happens!! Please help i'm so close to finishing.

Dim Response As String
Dim msg As String
Dim Style As String
Dim sFilename As String
Dim ans
Dim flToSave As Variant
Dim flName As String
Dim flFormat As Long


msg = "Are you sure you want to save the Smith quote?"
Style = vbYesNo + vbInformation + vbDefaultButton2

Response = MsgBox(msg, Style)
If Response = vbYes Then

flFormat = ActiveWorkbook.FileFormat

DefaultFolder = "M:\Design"
If Right(DefaultFolder, 1) < "\" Then
DefaultFolder = DefaultFolder & "\"
End If

DefaultFilename = Range("C7")
If Right(UCase(DefaultFilename), 2) < "XLS" Then
DefaultFilename = DefaultFilename & Format(Date, "ddmmyyyy") & ".xls"
'DefaultFilename = DefaultFilename & ".xls"
End If



'Create a FileDialog object as a File Picker dialog box.
Set fd = Nothing
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.InitialFileName = DefaultFolder & DefaultFilename
.Filters.Add "Excel Files", "*.xls", 1
.Title = "Save File As..."
.Show
If .SelectedItems.Count = 0 Then
Exit Sub
End If

Patrick Molloy[_2_]
SAVE AS MACRO
 
have you used F8 to step through the code?

"Neil Holden" wrote:

I have tried that but still unsuccessful.

"Patrick Molloy" wrote:

change

Dim Response As String


to

Dim Response As Long

because vbYes is a LONG not text

"Neil Holden" wrote:

That is in my code, not sure what else it could be.

Neil.

"Jacob Skaria" wrote:

Have you missed to copy the last few lines from Joel's code ?

Try with this line... which save the active workbook as the filename in the
variable

ActiveWorkbook.SaveAs Filename:=flToSave, FileFormat:=flFormat

If this post helps click Yes
---------------
Jacob Skaria


"Neil Holden" wrote:

Please see below, this is a button for when pressed a save as dialog appears
with the name of whatever appears in C7 and the current date, but when i
press ok to save nothing happens!! Please help i'm so close to finishing.

Dim Response As String
Dim msg As String
Dim Style As String
Dim sFilename As String
Dim ans
Dim flToSave As Variant
Dim flName As String
Dim flFormat As Long


msg = "Are you sure you want to save the Smith quote?"
Style = vbYesNo + vbInformation + vbDefaultButton2

Response = MsgBox(msg, Style)
If Response = vbYes Then

flFormat = ActiveWorkbook.FileFormat

DefaultFolder = "M:\Design"
If Right(DefaultFolder, 1) < "\" Then
DefaultFolder = DefaultFolder & "\"
End If

DefaultFilename = Range("C7")
If Right(UCase(DefaultFilename), 2) < "XLS" Then
DefaultFilename = DefaultFilename & Format(Date, "ddmmyyyy") & ".xls"
'DefaultFilename = DefaultFilename & ".xls"
End If



'Create a FileDialog object as a File Picker dialog box.
Set fd = Nothing
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.InitialFileName = DefaultFolder & DefaultFilename
.Filters.Add "Excel Files", "*.xls", 1
.Title = "Save File As..."
.Show
If .SelectedItems.Count = 0 Then
Exit Sub
End If

Neil Holden
SAVE AS MACRO
 
Yes I have, no errors were found : (

"Patrick Molloy" wrote:

have you used F8 to step through the code?

"Neil Holden" wrote:

I have tried that but still unsuccessful.

"Patrick Molloy" wrote:

change

Dim Response As String


to

Dim Response As Long

because vbYes is a LONG not text

"Neil Holden" wrote:

That is in my code, not sure what else it could be.

Neil.

"Jacob Skaria" wrote:

Have you missed to copy the last few lines from Joel's code ?

Try with this line... which save the active workbook as the filename in the
variable

ActiveWorkbook.SaveAs Filename:=flToSave, FileFormat:=flFormat

If this post helps click Yes
---------------
Jacob Skaria


"Neil Holden" wrote:

Please see below, this is a button for when pressed a save as dialog appears
with the name of whatever appears in C7 and the current date, but when i
press ok to save nothing happens!! Please help i'm so close to finishing.

Dim Response As String
Dim msg As String
Dim Style As String
Dim sFilename As String
Dim ans
Dim flToSave As Variant
Dim flName As String
Dim flFormat As Long


msg = "Are you sure you want to save the Smith quote?"
Style = vbYesNo + vbInformation + vbDefaultButton2

Response = MsgBox(msg, Style)
If Response = vbYes Then

flFormat = ActiveWorkbook.FileFormat

DefaultFolder = "M:\Design"
If Right(DefaultFolder, 1) < "\" Then
DefaultFolder = DefaultFolder & "\"
End If

DefaultFilename = Range("C7")
If Right(UCase(DefaultFilename), 2) < "XLS" Then
DefaultFilename = DefaultFilename & Format(Date, "ddmmyyyy") & ".xls"
'DefaultFilename = DefaultFilename & ".xls"
End If



'Create a FileDialog object as a File Picker dialog box.
Set fd = Nothing
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.InitialFileName = DefaultFolder & DefaultFilename
.Filters.Add "Excel Files", "*.xls", 1
.Title = "Save File As..."
.Show
If .SelectedItems.Count = 0 Then
Exit Sub
End If

Patrick Molloy[_2_]
SAVE AS MACRO
 
after thjis line...
.Show

you need to actually save the file. All you did to this line was select a
name and a where, you still need to execute the save.

Why not use the Application method?

Sub saveFile()
Dim fn As String
fn = Application.GetSaveAsFilename()

If fn Then
ActiveWorkbook.SaveAs fn
End If

End Sub









"Neil Holden" wrote:

Yes I have, no errors were found : (

"Patrick Molloy" wrote:

have you used F8 to step through the code?

"Neil Holden" wrote:

I have tried that but still unsuccessful.

"Patrick Molloy" wrote:

change

Dim Response As String


to

Dim Response As Long

because vbYes is a LONG not text

"Neil Holden" wrote:

That is in my code, not sure what else it could be.

Neil.

"Jacob Skaria" wrote:

Have you missed to copy the last few lines from Joel's code ?

Try with this line... which save the active workbook as the filename in the
variable

ActiveWorkbook.SaveAs Filename:=flToSave, FileFormat:=flFormat

If this post helps click Yes
---------------
Jacob Skaria


"Neil Holden" wrote:

Please see below, this is a button for when pressed a save as dialog appears
with the name of whatever appears in C7 and the current date, but when i
press ok to save nothing happens!! Please help i'm so close to finishing.

Dim Response As String
Dim msg As String
Dim Style As String
Dim sFilename As String
Dim ans
Dim flToSave As Variant
Dim flName As String
Dim flFormat As Long


msg = "Are you sure you want to save the Smith quote?"
Style = vbYesNo + vbInformation + vbDefaultButton2

Response = MsgBox(msg, Style)
If Response = vbYes Then

flFormat = ActiveWorkbook.FileFormat

DefaultFolder = "M:\Design"
If Right(DefaultFolder, 1) < "\" Then
DefaultFolder = DefaultFolder & "\"
End If

DefaultFilename = Range("C7")
If Right(UCase(DefaultFilename), 2) < "XLS" Then
DefaultFilename = DefaultFilename & Format(Date, "ddmmyyyy") & ".xls"
'DefaultFilename = DefaultFilename & ".xls"
End If



'Create a FileDialog object as a File Picker dialog box.
Set fd = Nothing
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.InitialFileName = DefaultFolder & DefaultFilename
.Filters.Add "Excel Files", "*.xls", 1
.Title = "Save File As..."
.Show
If .SelectedItems.Count = 0 Then
Exit Sub
End If


All times are GMT +1. The time now is 12:11 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com