Access Data to Excel (1 Viewer)

ElcoyotldeAztlan · Jul 26, 2017

Hello all

I have manged to create user interface where I can import an Excel sheet into a table but does anyone know how to do the opposite where I can export my access data into an excel sheet on a form. I know I can create reports for each table and then export that data into an excel sheet but perhaps is there an easy way to export all the data into an excel sheet at once

any thoughts
Thanks

pbaldy · Jul 26, 2017

Not sure of the goal, but options include TransferSpreadsheet, OutputTo, and Excel automation. The first 2 are simplest, the last gives you the most control but is harder to implement.

ashleedawg · Jul 27, 2017

Code:

Sub ExportAllTables()
'export all non-system tables to Excel file in same folder as DB
'(creates an Excel file if it doesn't already exist)
Dim tdf As TableDef, xlsFileName As String, count As Integer

xlsFileName = CurrentProject.Path & "\" & _
    Left(Application.CurrentProject.Name, _
    InStr(1, Application.CurrentProject.Name, ".")) & "XLSX"

For Each tdf In CurrentDb.TableDefs
    If (tdf.Attributes And dbSystemObject) = 0 Then 'skip system tables
        Debug.Print tdf.Name & ": Exporting, "; 'export other tables
        DoCmd.TransferSpreadsheet acExport, , tdf.Name, xlsFileName, True
        Debug.Print "Done."
        count = count + 1
    End If
Next tdf
MsgBox count & " tables exported to: " & vbCrLf & xlsFileName
End Sub

ElcoyotldeAztlan · Jul 27, 2017

Thanks ashleedawg,

but if this is on a form then shouldnt I have a button?
Shouldnt Name_Click() be on the code somewhere?

Access Data to Excel (1 Viewer)

ElcoyotldeAztlan

Registered User.

pbaldy

Wino Moderator

ashleedawg

"Here for a good time"

ElcoyotldeAztlan

Registered User.

Similar threads

Users who are viewing this thread