Export difficulties (1 Viewer)

Wapug

Registered User.
Local time
Today, 17:19
Joined
Apr 14, 2017
Messages
51
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

I’m here to help
Staff member
Local time
Today, 14:19
Joined
Oct 29, 2018
Messages
21,358
Hi. Check out the FileDialog object and try to use the Folder Picker.
 

Wapug

Registered User.
Local time
Today, 17:19
Joined
Apr 14, 2017
Messages
51
I not having any luck with that.
 

Wapug

Registered User.
Local time
Today, 17:19
Joined
Apr 14, 2017
Messages
51
Well thanks anyways, its just not working.
 

Gasman

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

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
 
Last edited:

Users who are viewing this thread

Top Bottom