Export Many Unrelated Access (2000) Data Tables to Excel

[Dennis]

I must export 130+ data tables to Excel...and those data tables are in an
(one single) Access database as separate, unrelated tables. The Access
database appears to be an Access 2000 creation.

Trust me....there are NO relationships. It is as if someone created
speradsheets within Access. Although the tables contain similar content,
they represent distinct groups of records.

I can open each table and export it as an Excel file (with types and content
sufficient for my further processing requirements). However, this is a
recurring task for which I have recently been given responsibility.

I am not very experienced in Access (I have created and used simple
databases in the past; yes, relational)--but there has to be a better/faster
way to do this export operation. I would be happy to export all tables as
separate tabs or worksheets in one Excel workbook, if that can be done
instead.

Help!

 

[Klatuu]

I would suggest creating a table with two fields. One with the name of the
table and one with the name of the path where the table should be exported.
Then create a loop to export all the tables:

Dim rstTables As Recordset
Dim strTable As String
Dim strFileName as String

Set rstTables = Currentdb.OpenRecordset("tblExportTables")
With rstTables
If .RecordCount = 0 Then
MsgBox "No Records to Process"
Else
.MoveLast
.MoveFirst
Do Whil Not .EOF
strTable = ![TableName]
strFileName = ![FileName]
Docmd.TransferSpreadsheet, acExport, , strTable,
strFileName, True
.MoveNext
Loop
.Close
End If
End With
Set rstTables = Nothing

 

[Dennis]

Thanks for the suggestion. I just received databases with nearly 300 such
tables. I am trying out your suggestion, and I wonder if there is a syntax
error that I just cannot find, in the Docmd. line..... (I had to change Whil
to While).

Help is much appreciated; I would like to get this one going!

I would suggest creating a table with two fields. One with the name of the
table and one with the name of the path where the table should be exported.
Then create a loop to export all the tables:

Dim rstTables As Recordset
Dim strTable As String
Dim strFileName as String

Set rstTables = Currentdb.OpenRecordset("tblExportTables")
With rstTables
If .RecordCount = 0 Then
MsgBox "No Records to Process"
Else
.MoveLast
.MoveFirst
Do Whil Not .EOF
strTable = ![TableName]
strFileName = ![FileName]
Docmd.TransferSpreadsheet, acExport, , strTable,
strFileName, True
.MoveNext
Loop
.Close
End If
End With
Set rstTables = Nothing
--
Dave Hargis, Microsoft Access MVP

I must export 130+ data tables to Excel...and those data tables are in an
(one single) Access database as separate, unrelated tables. The Access
database appears to be an Access 2000 creation.

Trust me....there are NO relationships. It is as if someone created
speradsheets within Access. Although the tables contain similar content,
they represent distinct groups of records.

I can open each table and export it as an Excel file (with types and content
sufficient for my further processing requirements). However, this is a
recurring task for which I have recently been given responsibility.

I am not very experienced in Access (I have created and used simple
databases in the past; yes, relational)--but there has to be a better/faster
way to do this export operation. I would be happy to export all tables as
separate tabs or worksheets in one Excel workbook, if that can be done
instead.

Help!

 

[Klatuu]

When are you getting the error? What is the description of the error?
--
Dave Hargis, Microsoft Access MVP

Thanks for the suggestion. I just received databases with nearly 300 such
tables. I am trying out your suggestion, and I wonder if there is a syntax
error that I just cannot find, in the Docmd. line..... (I had to change Whil
to While).

Help is much appreciated; I would like to get this one going!

I would suggest creating a table with two fields. One with the name of the
table and one with the name of the path where the table should be exported.
Then create a loop to export all the tables:

Dim rstTables As Recordset
Dim strTable As String
Dim strFileName as String

Set rstTables = Currentdb.OpenRecordset("tblExportTables")
With rstTables
If .RecordCount = 0 Then
MsgBox "No Records to Process"
Else
.MoveLast
.MoveFirst
Do Whil Not .EOF
strTable = ![TableName]
strFileName = ![FileName]
Docmd.TransferSpreadsheet, acExport, , strTable,
strFileName, True
.MoveNext
Loop
.Close
End If
End With
Set rstTables = Nothing
--
Dave Hargis, Microsoft Access MVP

I must export 130+ data tables to Excel...and those data tables are in an
(one single) Access database as separate, unrelated tables. The Access
database appears to be an Access 2000 creation.

Trust me....there are NO relationships. It is as if someone created
speradsheets within Access. Although the tables contain similar content,
they represent distinct groups of records.

I can open each table and export it as an Excel file (with types and content
sufficient for my further processing requirements). However, this is a
recurring task for which I have recently been given responsibility.

I am not very experienced in Access (I have created and used simple
databases in the past; yes, relational)--but there has to be a better/faster
way to do this export operation. I would be happy to export all tables as
separate tabs or worksheets in one Excel workbook, if that can be done
instead.

Help!

 

[Dennis]

Dave,

While I am Visual Basioc editor, that line shows in red (indicating some
kind of error; the Whil line was in red, until I made it While). Haven't
gotten beyond that so far.

 

[Dennis]

Dave,

Sorry...I am new to this kind of forum and apparently I did not reply to
yoiur questions at the proper level (?)...

Anyway, I had to drop this pursuit in order to get it done (manually, with
help) for nearly 800 separate and unrelated tables in 8 or 9 Access
databases. I am told that we see multiple tables in multiple databases for
this particular client monthly (the one huge job we finished may be an annual
affair, but 50+ data tables monthly still warrants a faster, smoother way --
if I can find it). Anyway, here was my reply to your last question.

Dave,

While I am Visual Basioc editor, that line shows in red (indicating some
kind of error; the Whil line was in red, until I made it While). Haven't
gotten beyond that so far.

When are you getting the error? What is the description of the error?
--
Dave Hargis, Microsoft Access MVP

Thanks for the suggestion. I just received databases with nearly 300 such
tables. I am trying out your suggestion, and I wonder if there is a syntax
error that I just cannot find, in the Docmd. line..... (I had to change Whil
to While).

Help is much appreciated; I would like to get this one going!

I would suggest creating a table with two fields. One with the name of the
table and one with the name of the path where the table should be exported.
Then create a loop to export all the tables:

Dim rstTables As Recordset
Dim strTable As String
Dim strFileName as String

Set rstTables = Currentdb.OpenRecordset("tblExportTables")
With rstTables
If .RecordCount = 0 Then
MsgBox "No Records to Process"
Else
.MoveLast
.MoveFirst
Do Whil Not .EOF
strTable = ![TableName]
strFileName = ![FileName]
Docmd.TransferSpreadsheet, acExport, , strTable,
strFileName, True
.MoveNext
Loop
.Close
End If
End With
Set rstTables = Nothing
--
Dave Hargis, Microsoft Access MVP


:

I must export 130+ data tables to Excel...and those data tables are in an
(one single) Access database as separate, unrelated tables. The Access
database appears to be an Access 2000 creation.

Trust me....there are NO relationships. It is as if someone created
speradsheets within Access. Although the tables contain similar content,
they represent distinct groups of records.

I can open each table and export it as an Excel file (with types and content
sufficient for my further processing requirements). However, this is a
recurring task for which I have recently been given responsibility.

I am not very experienced in Access (I have created and used simple
databases in the past; yes, relational)--but there has to be a better/faster
way to do this export operation. I would be happy to export all tables as
separate tabs or worksheets in one Excel workbook, if that can be done
instead.

Help!

 

[Dennis]

Belated, thank you. A more experienced programmer than I was able to take
this idea and make it work.

I would suggest creating a table with two fields. One with the name of the
table and one with the name of the path where the table should be exported.
Then create a loop to export all the tables:

Dim rstTables As Recordset
Dim strTable As String
Dim strFileName as String

Set rstTables = Currentdb.OpenRecordset("tblExportTables")
With rstTables
If .RecordCount = 0 Then
MsgBox "No Records to Process"
Else
.MoveLast
.MoveFirst
Do Whil Not .EOF
strTable = ![TableName]
strFileName = ![FileName]
Docmd.TransferSpreadsheet, acExport, , strTable,
strFileName, True
.MoveNext
Loop
.Close
End If
End With
Set rstTables = Nothing
--
Dave Hargis, Microsoft Access MVP

I must export 130+ data tables to Excel...and those data tables are in an
(one single) Access database as separate, unrelated tables. The Access
database appears to be an Access 2000 creation.

Trust me....there are NO relationships. It is as if someone created
speradsheets within Access. Although the tables contain similar content,
they represent distinct groups of records.

I can open each table and export it as an Excel file (with types and content
sufficient for my further processing requirements). However, this is a
recurring task for which I have recently been given responsibility.

I am not very experienced in Access (I have created and used simple
databases in the past; yes, relational)--but there has to be a better/faster
way to do this export operation. I would be happy to export all tables as
separate tabs or worksheets in one Excel workbook, if that can be done
instead.

Help!