Excel Save as

[Gismo]

You supply NOTHING! to the function, so how is Access meant to know where you want to be?

Do you ever try anything yourself to see what works and what does not?

I have just used a file name, no folder path with it, just a filename as the first parameter, and it opens in My Documents folder with the name of the filename I supplied in the parameter?

You still need to decide what to do when no filename is supplied?

I actually did try a few options as I mentioned before

And I do not know how what to do to have the fine name automatically populated
I do not know where to point for the File name, that I what I keep on telling you

StrFileName points to public function StrGetFileFolderName, I assume I dont need to make any rectification in this module as I use this funtion for multiple other save as reports

So I my File Name must be specified in the exportSparesforOrders funtion and I dont know how to perform this as when I cancel, I still receive the File Name argument error

Private Sub ExcelSparesforOrders__ExcelSparesforOrders_Click()

Dim StrFileName As String
Dim StrQryName As String
Dim StrSaveFile As String

StrFileName = strGetFileFolderName("Spares for Orders", 2, "Excel")
Debug.Print StrFileName
StrQryName = "CS Orders - Spares for Orders QRY"


'DoCmd.TransferSpreadsheet acExport, 10, StrQryName, StrSaveFile, True, , True
DoCmd.TransferSpreadsheet acExport, 10, StrQryName, StrFileName, True, , True
'DoCmd.TransferSpreadsheet acExport, 10, "CS Orders - Spares for Orders QRY", "C:\Users\e498309\Test.xls", True, StrFileName, True
'DoCmd.TransferSpreadsheet acExport, 10, qryName, strSaveFile, True, , True
End Sub

 

[Gasman]

I'm afraid someone else is going to have to try
I clearly cannot express myself enough, for you to understand what you are doing wrong.

You clearly have no idea as to what the code is doing, so unable to understand what YOU need to supply to get what you want.

I have already told you what I did to get the filename in the dialogue. I have already told you that you need to check if the function returns a filename and code accordingly, but not understanding that is a huge obstacle.

 

[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

Hi Minty,

Could you please direct me to rectify my code as I am getting the below error

Export works perfectly, only when clicked on cancel I get the error

Everyone (Gasman) tried to assist me but I must me missing something or not understanding where they are trying to point me

Below is the reworked code that you have provided

Private Sub ExcelSparesforOrders__ExcelSparesforOrders_Click()
On Error GoTo ExcelSparesforOrders_Click_Err

Dim StrFileName As String
Dim StrQryName As String
Dim StrSaveFile As String

StrFileName = strGetFileFolderName("Spares for Orders" & " - Registration -" & " " & Forms![CS Orders Detail - Main]![RegCBO], 2, "Excel")
'Debug.Print StrFileName
StrQryName = "CS Orders - Spares for Orders QRY"

DoCmd.TransferSpreadsheet acExport, 10, StrQryName, StrFileName, True, , True
ExcelSparesforOrders_Click_Exit:

Exit Sub

ExcelSparesforOrders_Click_Err:
MsgBox Error$
Resume ExcelSparesforOrders_Click_Exit
End Sub

 

[Minty]

If you press Cancel in the file selection process then strFileName will be null or empty.
So you would need to check for that and provide a default if it's not set, something like

...
StrFileName = strGetFileFolderName("Spares for Orders" & " - Registration -" & " " & Forms![CS Orders Detail - Main]![RegCBO], 2, "Excel")
'Debug.Print StrFileName
StrQryName = "CS Orders - Spares for Orders QRY"
If Len(StrFileName & "") < 0 Then
    StrFileName = Application.CurrentProject.Path & "\Spares_For_Orders_" & Format(Date, "yyyymmdd") & ".xls"
End If
...

 

[Gismo]

If you press Cancel in the file selection process then strFileName will be null or empty.
So you would need to check for that and provide a default if it's not set, something like

...
StrFileName = strGetFileFolderName("Spares for Orders" & " - Registration -" & " " & Forms![CS Orders Detail - Main]![RegCBO], 2, "Excel")
'Debug.Print StrFileName
StrQryName = "CS Orders - Spares for Orders QRY"
If Len(StrFileName & "") < 0 Then
    StrFileName = Application.CurrentProject.Path & "\Spares_For_Orders_" & Format(Date, "yyyymmdd") & ".xls"
End If
...

Hi Minty,

I tried your suggestion, I even tried a direct Dim File name but still get the FileName Argument notification on cancel

Private Sub ExcelSparesforOrders__ExcelSparesforOrders_Click()
On Error GoTo ExcelSparesforOrders_Click_Err

Dim StrFileName As String
Dim StrQryName As String
Dim StrSaveFile As String
Dim Filename As String

StrFileName = strGetFileFolderName("Spares for Orders" & " - Registration -" & " " & Forms![CS Orders Detail - Main]![RegCBO], 2, "Excel")
Debug.Print StrFileName
StrQryName = "CS Orders - Spares for Orders QRY"


If Len(StrFileName & "") < 0 Then
Filename = Me.Registration
'StrFileName = Application.CurrentProject.Path & "\Spares_For_Orders_" & Format(Date, "yyyymmdd") & ".xls"
End If

DoCmd.TransferSpreadsheet acExport, 10, StrQryName, StrFileName, True, , True
'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]

Sorry - this line
If Len(StrFileName & "") < 0 Then
should be
If Len(StrFileName & "") < 1 Then

And add another
Debug.Print "My File is:" & StrFileName

before your transfer spreadsheet command

 

[Gismo]

Sorry - this line
If Len(StrFileName & "") < 0 Then
should be
If Len(StrFileName & "") < 1 Then

And add another
Debug.Print "My File is:" & StrFileName

before your transfer spreadsheet command

Well that did the trick

Private Sub ExcelOutstanding_Click()
On Error GoTo ExcelOutstanding_Click_Err

Dim StrFileName As String
Dim StrQryName As String
Dim StrSaveFile As String

StrFileName = strGetFileFolderName("Spares for Order (Outstanding)" & " - Registration -" & " " & Forms![CS Orders Detail - Main]![RegCBO], 2, "Excel")
'Debug.Print StrFileName
StrQryName = "CS Orders - Outstanding Spares QRY"

If Len(StrFileName & "") < 1 Then
StrFileName = Application.CurrentProject.Path & "\Spares for Order (Outstanding)" & Format(Date, "yyyymmdd") & ".xls"
End If

Debug.Print "My File is:" & StrFileName
DoCmd.TransferSpreadsheet acExport, 10, StrQryName, StrFileName, True, , True

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

Exit Sub

ExcelOutstanding_Click_Err:
MsgBox Error$
Resume ExcelOutstanding_Click_Exit

End Sub

Thank you very much for your assistance and everyone else that always handles my ignorance

Maybe one day I would be able to assist others