Solved Command button on Report to export to excel (1 Viewer)

[Design by Sue]

I have been searching for a solution to add a command button to a report that will export the report (or underlying query which ever is better) to an excel file. I have found the following which is supposed to save the file with the date added to the name. I get the message box that the file is being outputted but I get a runtime error 2302 "Can't save the output data to the file you've selected. " I am sure the part c:\Desktop\TimeComplete is my error. I would like this to save the file to the desktop of the user. What would be the correct info for this>

DoCmd.OutputTo acOutputReport, "TimeCompletedRPT", acFormatXLS, "c:\Desktop\TimeComplete - " & Format(Date, "ddmmyyyy") & ".xls"

Thanks
Sue

 

[Design by Sue]

Actually better would be if I could replace the location of desktop to a browse window allowing the user to select the location. Any thought?

 

[The_Doc_Man]

Part of the problem depends on where Desktop is located. In Win 11 it is something like "C:\Users\username\Desktop" - but it is a "special" name that can be redirected. Therefore, using "C:\Desktop" is probably not a valid file path.

Putting a command button in a report is legal but you have to be careful where you put it because reports scroll a lot and that button might not stay visible unless you put it in the Report Header section (top of the report) or Report Footer section (bottom of the report). As to opening a "browse window" I can point you to the File Dialog object, which would open a browser window for you.

FileDialog object (Office)

Office VBA reference topic

learn.microsoft.com

Your command button would have an OnClick routine so you could put some VBA code under the button_Click event to start in some particular location and browse from there. Once you do that and actually select something, the dialog returns control to you and you can test for whether a file was selected. And if so, which one. Then you could build the file string you need for that DoCmd.OutputTo action.

 

[theDBguy]

Actually better would be if I could replace the location of desktop to a browse window allowing the user to select the location. Any thought?

Why a button on the report? Are you using Report View and not Print Preview?

 

[Design by Sue]

I am getting closer to what I want by using the following. The problem with this is it puts the results I'm the folder where the access application is. If I could get this to put the file in the user's documents file I could make that work.

Public Function MyDocumentFile(ByVal filName As String) As String
MyDocumentFile = Environ$("userprofile") & "\documents\" & filName
End Function

The Doc Man - thanks for the link will check that out if I can't get this to work

DBGuy At this point this is in the report header. Eventually I will put this on a form where I give the user the ability to filter by dates and then base the output on the query for that form. I am doing this as a rush solution to get the client something that they can use tomorrow. Then when I have time I will get this working the way it should.

Thanks

 

[Design by Sue]

Part of the problem depends on where Desktop is located. In Win 11 it is something like "C:\Users\username\Desktop" - but it is a "special" name that can be redirected. Therefore, using "C:\Desktop" is probably not a valid file path.

Putting a command button in a report is legal but you have to be careful where you put it because reports scroll a lot and that button might not stay visible unless you put it in the Report Header section (top of the report) or Report Footer section (bottom of the report). As to opening a "browse window" I can point you to the File Dialog object, which would open a browser window for you.

FileDialog object (Office)

Office VBA reference topic

learn.microsoft.com

Your command button would have an OnClick routine so you could put some VBA code under the button_Click event to start in some particular location and browse from there. Once you do that and actually select something, the dialog returns control to you and you can test for whether a file was selected. And if so, which one. Then you could build the file string you need for that DoCmd.OutputTo action.

This is really great but all I need is to select the path to the folder but not select a file within. I am not sure what of the code to remove to just get the path to the folder selected. Could you please give me some help on this.

I then need to make it work with my export code. Maybe I can figure it out once I get the dialog box to work for me.

 

[Design by Sue]

I think I found the answer here: https://stackoverflow.com/questions/19372319/vba-folder-picker-set-where-to-start

(I can't copy and paste code - it get all messed up - sorry - but it is the code about half way down and starts with
Public Function GetFolderName( Optional<OpenAt...

 

[Design by Sue]

I got the code working perfectly - browser window allows the choice of folder and the export to excel works; Appreciate your replies.

 

[Eugene-LS]

I think I found the answer

One way more (using msoFileDialog):

    With Application.FileDialog(4) '4 = msoFileDialogFolderPicker
        .Title = "BrowseForFolder"
        .InitialFileName = Environ("UserProfile") & "\Documents"
        .Filters.Clear
        .Show
        If .SelectedItems.Count > 0 Then
            MsgBox "Selected folder:" & vbCrLf & .SelectedItems(1), vbInformation, "Test"
        End If
    End With