Excel Save as

G

Gismo

Registered User.
Local time
Today, 09:46
Joined
Jun 12, 2017
Messages
1,298
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
 
Something like (Air code - Untested)
Code: 
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.
 
Minty said:
Something like (Air code - Untested)
Code: 
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.
Click to expand...
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
Click to expand...
 
Sorry you'll need something like

xlApp.GetSaveAsFilename

Where xlApp is your excel application object in your automation code.
 
Minty said:
Sorry you'll need something like

xlApp.GetSaveAsFilename

Where xlApp is your excel application object in your automation code.
Click to expand...
Could you please elaborate on XLApp
 
Can you show your current export to excel code, that will help determine what you need/the references to use.
 
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
Click to expand...
 
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.
Code: 
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.)
Code: 
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
 
Have you tried TransferSpreadsheet? It doesn't open Excel.
 
Minty said:
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.
Code: 
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.)
Code: 
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
Click to expand...
I get an error on Call. Filters.clear

1625469126888.png
 
Call is used optionally when executing procedures and functions. Filters.Clear is an internal method. It is not a procedure.
 
Pat Hartman said:
Call is used optionally when executing procedures and functions. Filters.Clear is an internal method. It is not a procedure.
Click to expand...
Am i using the syntax incorrectly as I am getting an error

I checked, Call .Filter seems correct
 
Gismo said:
Am i using the syntax incorrectly as I am getting an error

I checked, Call .Filter seems correct
Click to expand...
Not to me it does?
I would have thought in your context that you would use

Code: 
.Filters.Clear
Same with the .Add ?

Where did you check for that syntax??? :unsure:
 
Gasman said:
Not to me it does?
I would have thought in your context that you would use

Code: 
.Filters.Clear
Same with the .Add ?

Where did you check for that syntax??? :unsure:
Click to expand...
I did use Call .Filters.clear

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

1625566353083.png
 
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?
 
@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?
 
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.
 
You must log in or register to reply here.

Similar threads

B
Importing from Excel
Replies
7
Views
518
AndT
A
S
Access table protection from connections and DBreplications
Replies
13
Views
287
isladogs
isladogs
Gasman
OpenQuery and TransferSpreadsheet?
Replies
17
Views
448
The_Doc_Man
The_Doc_Man
B
Open Excel after transfering a query
Replies
7
Views
195
arnelgp
arnelgp
Rodeo Clown
Access import function converts text field to scientific notation
Replies
9
Views
599
cheekybuddha
cheekybuddha

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom