Export using File Dialog box (1 Viewer)

Locopete99

Registered User.
Local time
Today, 07:50
Joined
Jul 11, 2016
Messages
163
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?

Code:
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

Enthusiastic Amateur
Local time
Today, 14:50
Joined
Sep 21, 2011
Messages
14,054
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

Enthusiastic Amateur
Local time
Today, 14:50
Joined
Sep 21, 2011
Messages
14,054
This works for me, but I had to remove the format of the file and obviously change the name of source.

HTH
Code:
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

I’m here to help
Staff member
Local time
Today, 07:50
Joined
Oct 29, 2018
Messages
21,358
Hi Pete. I'm surprised you didn't get a compile error for having two End Subs in the code.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 09:50
Joined
Feb 28, 2001
Messages
27,003
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

I’m here to help
Staff member
Local time
Today, 07:50
Joined
Oct 29, 2018
Messages
21,358
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.


 

Attachments

  • test.PNG
    test.PNG
    6.6 KB · Views: 943
  • error.PNG
    error.PNG
    27.2 KB · Views: 993
Last edited:

Users who are viewing this thread

Top Bottom