Export difficulties

[Wapug]

Trying to export table. Seems simple enough, but my problem is that the user needs to be able to have a dialogue box that pops up, enabling them to either update the current saved export destination path, or browse to the new location. The table that’s being exported is going to a month end workbook, so obviously it changes each time. I do t want users fiddling with things they shouldn’t be so I need time way to keep this relevant to the export at hand and I need the data to go to the worksheet (always names qMonthendData) in the workbook. Any help would be appreciated.

 

[theDBguy]

Hi. Check out the FileDialog object and try to use the Folder Picker.

 

[Wapug]

I not having any luck with that.

 

[Gasman]

I not having any luck with that.

How about Google?

First link shows how to use FileDialog

Second link if you have to export to a particular excel worksheet.

https://rtmccormick.com/2014/04/23/file-dialog-save-as-access-vba/

https://social.msdn.microsoft.com/F...-to-specific-excel-worksheets?forum=accessdev

HTH

 

[Wapug]

Thank you, but this link doesn't work https://rtmccormick.com/2014/04/23/f...as-access-vba/

 

[Gasman]

Thank you, but this link doesn't work https://rtmccormick.com/2014/04/23/f...as-access-vba/

I do not know what to say. It works fine for me?

Try copy and paste link addresss after a right click or use text below

https://rtmccormick.com/2014/04/23/file-dialog-save-as-access-vba/

 

[Wapug]

Well thanks anyways, its just not working.

 

[Gasman]

Well thanks anyways, its just not working.

Try google with 'vba filedialog' without the quotes.

Edit:
Here is the code from that link, but you really need to read the page, but if you can get y just with the code?

Public Function saveFileAs() As String
    Dim fd As FileDialog, fileName As String
    
    On Error GoTo ErrorHandler

    Set fd = Application.FileDialog(msoFileDialogSaveAs)
    
    If fd.Show = True Then
        If fd.SelectedItems(1) <> vbNullString Then
            fileName = fd.SelectedItems(1)
        End If
    Else
        'Stop Code Execution for Null File String
        End
    End If
    
    saveFileAs = fileName
    
    'Cleanup
    Set fd = Nothing
    
    Exit Function
        
ErrorHandler:
    Set fd = Nothing
    MsgBox "Error " & Err & ": " & Error(Err)

End Function

Public Sub exportTable(tName As String)
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, tName, saveFileAs, True
End Sub

You will need a reference to Microsoft Office [version number] Object Library

HTH