Choosing an access table with VBA code

  • Thread starter Thread starter me
  • Start date Start date
M

me

Hello all.

I use the following code to copy and Access data table and export it into an
Excel worksheet. I have an Access database that has 50 tables. What I need
to know is after making the DAO connection, how can I make the the procedure
loop through the entire database and copy/paste the tables into an excel
workbook, or how can I incorporate an input box that allows the user to
select a table from the database so it can be copied over one at a time???

Any suggestions? All help is appreciated.

Code:

Public Sub CopyAccessTable()

'Open the database that will be copied
Set dbs = OpenDatabase _
("C:\Documents and Settings\chrisv\Desktop\db2.mdb")

'Select the initial table to be copied
Set rst = dbs.OpenRecordset("Customer", dbOpenDynaset, _
dbReadOnly)

'Select the starting range to begin copy.
[A2].CopyFromRecordset rst

'These lines iterate fields for copy
For Each fld In rst.Fields
i = i + 1
Cells(1, i).Value = fld.Name
Next fld

'Now we need to get our virtual data on a worksheet
ActiveSheet.Columns.AutoFit
ActiveSheet.Name = rst.Name
'Let's check for other tables in the database

'Let's copy that info into a new worksheet

'Keep this going until we are out of tables


'Clean up shop
dbs.Close

'Verify completion
MsgBox "Your table was successfully imported to Excel"
End Sub
 
You can iterate through the tables in the database, add and name the
worksheets with code like this:

For Each T In db2.TableDefs
If Not T.Name Like "MSys*" Then
Worksheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = T.Name
End If
Next T

Access uses hidden tables with names starting with "MSys..." that you won't
want to include in your workbook.

The most foolproof way to get the user to select a table name is using a
listbox or combobox in a user form. You can populate it like this:

For Each T In db2.TableDefs
If Not T.Name Like "MSys*" Then
frm01.cbxTableNames.AddItem T.Name
End If
Next T

Then use the click event for the combobox to select a table-name and
continue executing the macro.

frm01.Show
Worksheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = frm01.cbxTableNames.Value

HTH,
Shockley
 
Back
Top