Re: Choosing an access table with VBA code
From: shockley (shocklee_at_frontiernet.net)
Date: 03/11/04
- Next message: Nick Cranham: "Re: help on linking different worksheets into one"
- Previous message: Martin Wheeler: "Re: copy cell name"
- In reply to: me: "Choosing an access table with VBA code"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 11 Mar 2004 06:33:17 GMT
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
"me" <chrisv@texas.net> wrote in message
news:404fbd54$0$26496$ec3e2dad@news.usenetmonster.com...
> 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
>
>
- Next message: Nick Cranham: "Re: help on linking different worksheets into one"
- Previous message: Martin Wheeler: "Re: copy cell name"
- In reply to: me: "Choosing an access table with VBA code"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
