Hi Mario,
I don't think you can do this with a macro, but you can do it with VBA.
Something like this untested air code, running in Excel, should do the
job. You'll need to set a reference to the DAO 3.6 library (in
Tools|References).
Presumably you'll need some way to specify which of the 700+ tables in
the database should be imported to Excel and which should not. THis code
pops up a message box for each table, but you'll probably be able to
work out how to test T.Name in code to decide whether it should be
imported.
'CODE BEGINS
Sub ImportTables()
Dim db As DAO.Database
Dim T As DAO.TableDef
Dim rs As DAO.Recordset
Dim wks As Excel.Worksheet
Dim R As Excel.Range
Dim lngAnswer As String
Set db = DAO.OpenDatabase("C:\temp\boxwithinbox.mdb", , True)
Set wks = ThisWorkbook.ActiveSheet
For Each T In db.TableDefs
'Do we want to import this one?
lngAnswer = MsgBox("Import table " & T.Name & "?", vbq +
vbYesNoCancel)
Select Case lngAnswer
Case vbYes
Debug.Print "Imported " & T.Name
Set rsr = db.OpenRecordset(T.Name, , dbReadOnly)
'Navigate to row at bottom
Set R = wks.UsedRange.End(xlDown)
Set R = Cells(R.Row + 1, 1)
R.CopyFromRecordset rs
rs.Close
Case vbNo
Debug.Print vbTab & "Rejected " & T.Name
Case vbCancel
Exit For
End Select
Next
db.Close
Set rs = Nothing
Set db = Nothing
Set wks = Nothing
End Sub
'CODE ENDS
You didn't say which version of Office you're using. The above won't
work in Office 97, which doesn't have CopyFromRecordset.
HELP!
Working on a tight deadline.
I have approx 700 tables in access. I need to paste them
into Excel. I have built a macro that gets table 1 and
pastes into excel, but that's it? I cant figure out how to
build the macro to get table 2 and paste to the same excel
spreadsheet, then table 3 and so on.
I need to paste each of these unique tables into excel via
macro.
Does anyone know how to do this?
Struggling here,
Mario
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
