CODEWITHSUNDEEP
vbams-access
user2039795
user2039795

Reputation: 79

MS Access table export as excel file to user defined folder

Below code is working fine and exporting access table data into excel file (C:\temp\test.xlsx).

But my requirement is instead of defining C:\temp folder in VBA script, system should ask user to select the output directory. Could you please help me to prepare that VBA script?

Private Sub Command3_Click()

 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Fields", _
       "C:\temp\text.xlsx", True
End Sub

Upvotes: 0

Views: 8184

Answers (1)

Parfait
Parfait

Reputation: 107587

As @Shanayl points out, you can prompt the user to select a folder on their local machines and then pass the result into DoCmd.TransferSpreadsheet since it accepts a string value. Do not simply concatenat fd into file path.

Below modifies @eabraham's answer to run a folder picker dialog not file picker for user in a VBA function and not a VBA sub to be called later for Excel file.

Function (place behind form/report in same area as button click event)

Private Function GetExcelFolder() As String       
   Dim fldr As FileDialog
   Dim txtFileName As String

   ' FOLDER PICKER
   Set fldr = Application.FileDialog(msoFileDialogFolderPicker)

   With fldr
      .AllowMultiSelect = False

      ' Set the title of the dialog box.
      .Title = "Please select folder for Excel output."

      ' Show the dialog box. If the .Show method returns True, the
      ' user picked at least one file. If the .Show method returns
      ' False, the user clicked Cancel.
      If .Show = True Then
        txtFileName = .SelectedItems(1)
      Else
        Msgbox "No File Picked!", vbExclamation
        txtFileName = ""
      End If
   End With

   ' RETURN FOLDER NAME
   GetExcelFolder = txtFileName       
End Function

Button Click Event

Private Sub Command3_Click()
    Dim user_excel_fldr As String

    ' CALL FUNCTION
    user_excel_fldr = GetExcelFolder()    
    If user_excel_fldr = "" Then Exit Sub

    ' SPECIFY ONE TABLE
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "myTableName", _
       user_excel_fldr & "\" & "ExcelOutput.xlsx", True
End Sub

And for every table in your database, loop through TableDefs, using range argument of MS Access's DoCmd.TransferSpreadsheet method to output each to specific worksheet tab.

Private Sub Command3_Click()
    Dim user_excel_fldr As String
    Dim tbldef As TableDef

    ' CALL FUNCTION
    user_excel_fldr = GetExcelFolder()
    If user_excel_fldr = "" Then Exit Sub

    ' LOOP THROUGH ALL TABLE NAMES
    For Each tbldef In CurrentDb.TableDefs    
        If Not tbldef.Name Like "*MSys*"    ' AVOID SYSTEM TABLES
           DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, tbldef.Name, _
               user_excel_fldr & "\" & "ExcelOutput.xlsx", True, tbldef.Name & "!"
        End If
    Next tbldef

    Set tbldef = Nothing
End Sub

By the way, do note Excel is not a database. Having to dump an entire MS Access database into an Excel workbook even entire tables may require reconsideration. Maybe your users need tailored and filtered tables or queries (i.e., QueryDefs). Best practice is to use Excel as the end-use report application and Access as central repository backend.

Upvotes: 2

Related Questions