Exporting data from an Access table to Excel 2

[chatfield]

I have a client whose collegue only works with Excel.
How can I best write a routine that will allow my client's collegue
to activate a form on his database which will display a list of tables or queries in a list
box. Then, when the specific table or query is selected, Access will then export it as an Excel worksheet.

I would really appreciate any suggestions.

Thanks.

chatfield

 

[mikey69]

If the collection of tables doesn't change much, it might be easiest to create a form with one command button per table and then do the work with a VBA procedure associated with the click event. but even easier might be to create Excel tables and link them into Access, they behave like Access tables in Access. You can update them and so on, and your colleague can open them in Excel. This works well.

 

[KyleS]

Hi Chatfield,

This code was just thrown together so it's probably not perfect, but this will populate a combo box (in this case combo0) with a list of the names of all the tables and/or queries in you DB. From there the code to export it to excel is pretty rudimentary.

Let me know how this works, or if you need the code to export to Excel.

Private Sub Form_Load()
Dim rstTables As DAO.Recordset
Set rstTables = CurrentDb.OpenRecordset("msysobjects", dbOpenTable, dbForwardOnly, dbReadOnly)

Do Until rstTables.EOF
If rstTables!Type <> 1 And rstTables!Type <> 5 Then
GoTo NextEntry
End If

If rstTables!Name = &quot;MSysObjects&quot; Or rstTables!Name = &quot;MSysACEs&quot; Or rstTables!Name = &quot;MSysModules&quot; _
Or rstTables!Name = &quot;MSysModules2&quot; Or rstTables!Name = &quot;MSysQueries&quot; Or rstTables!Name = &quot;MSysRelationships&quot; _
Or Left(rstTables!Name, 3) = &quot;~sq&quot; Then
GoTo NextEntry
End If

If Len(Me!Combo0.RowSource & &quot;&quot = 0 Then
Me!Combo0.RowSource = rstTables!Name
Else
Me!Combo0.RowSource = Me!Combo0.RowSource & &quot;;&quot; & rstTables!Name
End If
NextEntry:
rstTables.MoveNext
Loop

rstTables.Close
Set rstTables = Nothing
End Sub

Hope this helps,

Kyle :

PS as a side note, if your using Access 2000 or XP you'll have to go into a code module and go to &quot;Tools ---> References&quot; and make sure the reference for DAO 3.6 is selected, otherwise this won't work.