Export using File Dialog box

[Locopete99]

Hi Guys,

I have a query that could have multiple variations as the customer can put in a username as a criteria to produce different variations of the report.

I'm trying to get it to export using the Office File Dialog save as using instructions I have found online. I'm doing this as I want the user to be able save the report multiple times for each user in their department. I've added the Microsoft Office 16.0 Object Library.

The code I have is below. Once I click the button to run the code though, nothing happens. Can anyone see anything obvious that is stopping this from working?

Private Sub Label1_Click()
Dim fd As FileDialog, filename As String


Set fd = Application.FileDialog(msoFileDialogSaveAs)

With fd

.Title = "Please Select the file to save."
Filters.Clear
Filters.Add "Excel Files", "*XLS*"




If fd.Show = True Then

If fd.SelectedItems(1) <> vbNullString Then

filename = fd.SelectedItems(1)
End If

Else

End Sub
End If


DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Qry_CLCN", filename, True


End Sub

 

[Gasman]

You have an End Sub in the middle of the code before the TransferSpreadsheet?

Indent your code to make it easier to spot mistakes like these.

Walk through the code in the debugger to see the paths taken.?

Also missing an End With

 

[Gasman]

Quick Google shows

https://software-solutions-online.com/vba-save-file-dialog-filedialogmsofiledialogsaveas/

as the code complained with filters when I tried your code.?

 

[Gasman]

This works for me, but I had to remove the format of the file and obviously change the name of source.

HTH

Private Sub Label1_Click()
Dim fd As FileDialog, filename As String


Set fd = Application.FileDialog(msoFileDialogSaveAs)

With fd

.Title = "Please Select the file to save."
'.Filters.Clear
'.Filters.Add "Excel Files", "*XLS*"

End With


If fd.Show = True Then
    If fd.SelectedItems(1) <> vbNullString Then
        filename = fd.SelectedItems(1)
        DoCmd.TransferSpreadsheet acExport, , "Query7", filename, True
    End If
End If

End Sub

 

[theDBguy]

Hi Pete. I'm surprised you didn't get a compile error for having two End Subs in the code.

 

[The_Doc_Man]

TheDBGuy - the nature of the compiler, if I recall correctly, is that it stops at the first error so it can highlight it. The "Double End Sub" will show up ONLY if all previous errors have been fixed. So maybe he never got that far?

 

[theDBguy]

TheDBGuy - the nature of the compiler, if I recall correctly, is that it stops at the first error so it can highlight it. The "Double End Sub" will show up ONLY if all previous errors have been fixed. So maybe he never got that far?

Yeah, you're probably right. Thanks.


PS. I tried to test this theory. I'm not sure I'm doing it correctly, but here's what I got.

And after clicking OK, this is what gets highlighted.