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
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,694
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
What are you trying to save?
 

jsdsvs

New Member
Joined
Aug 19, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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?
 

jsdsvs

New Member
Joined
Aug 19, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Oh, and I want it to save as a .xlsx because the end user doesn't need the macros.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,694
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 

jsdsvs

New Member
Joined
Aug 19, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Amazing! Thank you so much for the help. It worked like a charm.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,694
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,927
Messages
5,627,675
Members
416,264
Latest member
Dezmo

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
Top