Problem with Save as Dialog Box

jsdsvs

New Member
Joined
Aug 19, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
This code is doing exactly what I need it to except that when I click "Save" in the dialog box, the file doesn't actually save anywhere. The dialog box disappears, and the file is not on my desktop. Can someone help?

VBA Code:
Sub Complete_Form()

ActiveWorkbook.Sheets("Data Input").Visible = xlSheetHidden

Dim fPth As Object
Dim InitialName As String
Dim fileSaveName As Variant
Dim eventdate As String
Set fPth = Application.FileDialog(msoFileDialogSaveAs)
eventdate = ThisWorkbook.Sheets("Event Details").Range("B10")
InitialName = "Logistics " & ThisWorkbook.Sheets("Event Details").Range("B3") & " " & ThisWorkbook.Sheets("Event Details").Range("N3") & " " & Format(eventdate, "m.d.yyyy")

With fPth
  .InitialFileName = InitialName
  .Title = "Save as"
  .FilterIndex = 1
  .InitialView = msoFileDialogViewList
  .Show
End With

End Sub
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Hi & welcome to MrExcel.
What are you trying to save?
 
Upvote 0
I'm trying to save the entire workbook as a new .xlsx file. The workbook is a logistics document that will be filled by my team, saved as a new file with a unique name, then passed to a third party. To save a few steps, I want the macro to finalize the document then open the Save as dialog and allow them to save it wherever they want to.

Does that make sense?
 
Upvote 0
Oh, and I want it to save as a .xlsx because the end user doesn't need the macros.
 
Upvote 0
Ok, how about
VBA Code:
Sub Complete_Form()

ActiveWorkbook.Sheets("Data Input").Visible = xlSheetHidden

Dim fPth As Object
Dim InitialName As String, SaveName As String
Dim fileSaveName As Variant
Dim eventdate As String
Set fPth = Application.FileDialog(msoFileDialogSaveAs)
eventdate = ThisWorkbook.Sheets("Event Details").Range("B10")
InitialName = "Logistics " & ThisWorkbook.Sheets("Event Details").Range("B3") & " " & ThisWorkbook.Sheets("Event Details").Range("N3") & " " & Format(eventdate, "m.d.yyyy")

With fPth
  .InitialFileName = InitialName
  .Title = "Save as"
  .FilterIndex = 1
  .InitialView = msoFileDialogViewList
   Application.DisplayAlerts = False
   If .Show Then ThisWorkbook.SaveAs .SelectedItems(1), 51
   Application.DisplayAlerts = True
End With
 
Upvote 0
Amazing! Thank you so much for the help. It worked like a charm.
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top