Excel Save as (1 Viewer)

[Gismo]

Hi Al

Please could you advise on how to export to an excel spreads sheet but instead of the excel spreadsheet opening directly, I would like to have a "Save as" screen and then to select the location

 

[Minty]

Something like (Air code - Untested)

Dim Spreadsheet_Name As Variant

Spreadsheet_Name = Application.GetSaveAsFilename
If Spreadsheet_Name <> False Then  ActiveWorkbook.SaveAs Filename:=Spreadsheet_Name

Obviously, replace ActiveWorkbook with your Access Object reference.

 

[Gismo]

Something like (Air code - Untested)

Dim Spreadsheet_Name As Variant

Spreadsheet_Name = Application.GetSaveAsFilename
If Spreadsheet_Name <> False Then  ActiveWorkbook.SaveAs Filename:=Spreadsheet_Name

Obviously, replace ActiveWorkbook with your Access Object reference.

Thank you

If GetSaveAsfilename correct, as I get a compile error on method

Dim CSOrderlineItemsQry As Variant
CSOrderlineItemsQry = Application.GetSaveAsFilename
If CSOrderlineItemsQry <> False Then ActiveWorkbook.SaveAs FileName:=CSOrderlineItemsQry

 

[Minty]

Sorry you'll need something like

xlApp.GetSaveAsFilename

Where xlApp is your excel application object in your automation code.

 

[Gismo]

Sorry you'll need something like

xlApp.GetSaveAsFilename

Where xlApp is your excel application object in your automation code.

Could you please elaborate on XLApp

 

[Minty]

Can you show your current export to excel code, that will help determine what you need/the references to use.

 

[Gismo]

Private Sub ExcelSparesforOrders__ExcelSparesforOrders_Click()
On Error GoTo ExcelSparesforOrders_Click_Err

DoCmd.OutputTo acOutputQuery, "CS Orders - Spares for Orders QRY", "Excel97-Excel2003Workbook(*.xls)", "Spares for Orders" & " - Registration -" & " " & Forms![CS Orders Detail - Main]![RegCBO] & ".xls", True, "", , acExportQualityPrint
ExcelSparesforOrders_Click_Exit:

Exit Sub

ExcelSparesforOrders_Click_Err:
MsgBox Error$
Resume ExcelSparesforOrders_Click_Exit
End Sub

 

[Minty]

Ah - I had assumed you were doing the export in a different way.
The code below opens a file dialog and lets you choose a folder or a file, it returns the full path to whichever you use.

Public Function strGetFileFolderName(Optional strInitialDir As String = "", Optional lngType As Long = 4, Optional strPattern As String = "All Files,*.*") As String
    'StrInitialDir = where the filedialog starts browsing
    'lngType e.g. 'msoFileDialogFilePicker = 3, msoFileDialogFolderPicker =4, msoFileDialogOpen=1,msoFileDialogSaveAs=2

    Dim fDialog     As Object
    Dim vFile       As Variant, varEntry As Variant


    strGetFileFolderName = ""
   
    Set fDialog = Application.FileDialog(lngType)
   
    With fDialog
        .Title = "Browse for "
        Select Case lngType
            Case 1                                'msoFileDialogOpen
                .Title = .Title & "File to open"
            Case 2                                'msoFileDialogSaveAs
                .Title = .Title & "File to SaveAs"
            Case 3                                'msoFileDialogFilePicker
                .Title = .Title & "File"
            Case 4                                'msoFileDialogFolderPicker
                .Title = .Title & "Folder"
        End Select
       
        Select Case strPattern
            Case "Excel"
                strPattern = "MS Excel,*.XLSX; *.XLSM; *.XLS"
            Case "Access"
                strPattern = "MS Access,*.ACCDB"
            Case "PPT"
                strPattern = "MS Powerpoint,*.PPTX; *.PPTM"
        End Select
       
       
        If lngType <> 4 Then
            'Reset then add filter patterns separated by tildes (~) where
            '  multiple extensions are separated by semi-colons (;) and the
            '  description is separated from them by a comma (,).
            '  Example strPattern :
            '  "MS Access,*.ACCDB; *.MDB~MS Excel,*.XLSX; *.XLSM; *.XLS"
            Call .Filters.Clear
            For Each varEntry In Split(strPattern, "~")
                Call .Filters.Add(Description:=Split(varEntry, ",")(0), _
                    Extensions:=Split(varEntry, ",")(1))
            Next varEntry
        End If
        'Set some default settings
        .InitialFileName = strInitialDir
        .AllowMultiSelect = False
        .InitialView = 2                          'msoFileDialogViewDetails
        'Only return a value from the FileDialog if not cancelled.
        If .Show Then strGetFileFolderName = .SelectedItems(1)
       
    End With
   
ExitHere:
    Exit Function
   
HandleErrors:
    MsgBox "Error: " & Err.Description & " (" & Err.Number & ")"
    Resume ExitHere
End Function

So you would assign a variable (Lets say strFileName) to the result of the function then use that in place of your file name.
I then would use something like
(Slightly Aircode you would need to dim these variables etc.)

strFileName =  strGetFileFolderName(, 2, "Excel")
qryName = "CS Orders - Spares for Orders QRY"

DoCmd.TransferSpreadsheet acExport, 10, qryName, strSaveFile, True, , True

Hopefully that will point you in the right direction

 

[Pat Hartman]

Have you tried TransferSpreadsheet? It doesn't open Excel.

 

[Gismo]

Have you tried TransferSpreadsheet? It doesn't open Excel.

Hi,

I have not yet tried TransferSpreadsheet
Will mosty defenately look into this

Thank you

 

[Gismo]

Ah - I had assumed you were doing the export in a different way.
The code below opens a file dialog and lets you choose a folder or a file, it returns the full path to whichever you use.

Public Function strGetFileFolderName(Optional strInitialDir As String = "", Optional lngType As Long = 4, Optional strPattern As String = "All Files,*.*") As String
    'StrInitialDir = where the filedialog starts browsing
    'lngType e.g. 'msoFileDialogFilePicker = 3, msoFileDialogFolderPicker =4, msoFileDialogOpen=1,msoFileDialogSaveAs=2

    Dim fDialog     As Object
    Dim vFile       As Variant, varEntry As Variant


    strGetFileFolderName = ""
  
    Set fDialog = Application.FileDialog(lngType)
  
    With fDialog
        .Title = "Browse for "
        Select Case lngType
            Case 1                                'msoFileDialogOpen
                .Title = .Title & "File to open"
            Case 2                                'msoFileDialogSaveAs
                .Title = .Title & "File to SaveAs"
            Case 3                                'msoFileDialogFilePicker
                .Title = .Title & "File"
            Case 4                                'msoFileDialogFolderPicker
                .Title = .Title & "Folder"
        End Select
      
        Select Case strPattern
            Case "Excel"
                strPattern = "MS Excel,*.XLSX; *.XLSM; *.XLS"
            Case "Access"
                strPattern = "MS Access,*.ACCDB"
            Case "PPT"
                strPattern = "MS Powerpoint,*.PPTX; *.PPTM"
        End Select
      
      
        If lngType <> 4 Then
            'Reset then add filter patterns separated by tildes (~) where
            '  multiple extensions are separated by semi-colons (;) and the
            '  description is separated from them by a comma (,).
            '  Example strPattern :
            '  "MS Access,*.ACCDB; *.MDB~MS Excel,*.XLSX; *.XLSM; *.XLS"
            Call .Filters.Clear
            For Each varEntry In Split(strPattern, "~")
                Call .Filters.Add(Description:=Split(varEntry, ",")(0), _
                    Extensions:=Split(varEntry, ",")(1))
            Next varEntry
        End If
        'Set some default settings
        .InitialFileName = strInitialDir
        .AllowMultiSelect = False
        .InitialView = 2                          'msoFileDialogViewDetails
        'Only return a value from the FileDialog if not cancelled.
        If .Show Then strGetFileFolderName = .SelectedItems(1)
      
    End With
  
ExitHere:
    Exit Function
  
HandleErrors:
    MsgBox "Error: " & Err.Description & " (" & Err.Number & ")"
    Resume ExitHere
End Function

So you would assign a variable (Lets say strFileName) to the result of the function then use that in place of your file name.
I then would use something like
(Slightly Aircode you would need to dim these variables etc.)

strFileName =  strGetFileFolderName(, 2, "Excel")
qryName = "CS Orders - Spares for Orders QRY"

DoCmd.TransferSpreadsheet acExport, 10, qryName, strSaveFile, True, , True

Hopefully that will point you in the right direction

I get an error on Call. Filters.clear

 

[Gasman]

Do you ever look up the syntax?

Why have you used the word Call ?

https://docs.microsoft.com/en-us/office/vba/api/office.filedialog.filters

 

[Pat Hartman]

Call is used optionally when executing procedures and functions. Filters.Clear is an internal method. It is not a procedure.

 

[Gismo]

Call is used optionally when executing procedures and functions. Filters.Clear is an internal method. It is not a procedure.

Am i using the syntax incorrectly as I am getting an error

I checked, Call .Filter seems correct

 

[Gasman]

Am i using the syntax incorrectly as I am getting an error

I checked, Call .Filter seems correct

Not to me it does?
I would have thought in your context that you would use

.Filters.Clear

Same with the .Add ?

Where did you check for that syntax???

 

[Gismo]

Not to me it does?
I would have thought in your context that you would use

.Filters.Clear

Same with the .Add ?

Where did you check for that syntax???

I did use Call .Filters.clear

Please refer to the code provided in post 8 and my reply in post 11

 

[Minty]

I've been using that code as-is for a long time. Interesting that you are getting an error.

You can remove the initial Call from
Call .Filters.Clear
and it all still works.
But access complains if you remove it from Call .Filters.Add

Very strange! I wonder if it is a references issue?

 

[Gasman]

I did use Call .Filters.clear

Please refer to the code provided in post 8 and my reply in post 11

View attachment 92825

So have you noticed that filters has not changed to Filters ?

Are all those options available?, What is your value of lngType?

Also see the note on this link?,
https://docs.microsoft.com/en-us/office/vba/api/office.filedialogfilters

 

[Pat Hartman]

@Minty, does the module include Option Explicit? It is possible that since .filter doesn't change to .Filter, Access has helpfully created a variant for you?

 

[Isaac]

I would think that's because of late binding.

Intellisense doesn't know or care what filter means at compile-time.

Or, to put it in reverse, you could put .Spaghetti there and it would still compile.