VBA to perform Save As, delete old data, and display new file

[General Ledger]

Dear All,


I am trying to create a macro that will:

1. Save data that was exported to Excel in a .xlsx file. The exported data could be xls, xlsx, csv, txt or tsv file format.
2. Make that new .xlsx file the active workbook
3. Close the file of exported data without saving

The code below (which I built from pieces I found in this forum) gets me close to my goal. However:

• It opens the File Save dialog and not the Save As dialog. I don't know if this is a problem.
• After execution, I am still looking at the exported file and not the .xlsx file that was saved.
• Is there a way in this macro to maximize the Save As dialog?

Dim strFolder As String
 
    Filename = "Invoice Headers " & Format(Now, "yyyy-mm-dd hh mm") & ".xlsx"
 
With Application.FileDialog(msoFileDialogSaveAs)
  .AllowMultiSelect = False
  .InitialFileName = "Z:\Excel\" & Filename
  .InitialView = msoFileDialogViewDetails
  If .Show = -1 Then strFolder = .SelectedItems(1) Else Exit Sub
End With

I am at best a novice so please be detailed in any responses.

Thank you,

GL

 

[AlphaFrog]

I don't understand why you are calling the SaveAs dialog. That's not listed in your three criteria.

This saves the current workbook as an .xslx file.

    Dim strFileName As String, strPath As String
    
    strPath = "Z:\Excel\"
    strFileName = "Invoice Headers " & Format(Now, "yyyy-mm-dd hh mm") & ".xlsx"
    ActiveWorkbook.SaveAs Filename:=strPath & strFileName, FileFormat:=xlOpenXMLWorkbook

Use VBA SaveAs in Excel 2007-2010

 

[General Ledger]

AlphaFrog,

The reason I use the Save As dialog is to give the user the opportunity to navigate and select a folder if the default location I provided ("Z:\Excel\") is not correct.

In the meantime, I found I was missing one important line of code: .Execute

My only remaining wish is code that will maximize the Save As dialog window.

Dim strFolder As String
 
    Filename = "Invoice Headers " & Format(Now, "yyyy-mm-dd hh mm") & ".xlsx"
 
With Application.FileDialog(msoFileDialogSaveAs)
  .AllowMultiSelect = False
  .InitialFileName = "Z:\Excel\" & Filename
  .InitialView = msoFileDialogViewDetails
  If .Show = -1 Then strFolder = .SelectedItems(1) Else Exit Sub
  .Execute
End With

Thanks,

GL