Acc export: getting around Excel row limits

[LeAnne]

Hi all -

I am, and shall ever remain, VBA-impaired. I would very much appreciate
some help translating the following garbage into usable code to
automatically, conditionally transfer Access (97) tables (or query
recordsets) into Excel (97) spreadsheets for archiving purposes. The
problem surfaces when a table (or query) recordset contains >65535
records. Excel 97 truncates the import at this cut-off point. I'm
trying to write a procedure that will count the records in a recordset
if the db object is named "ARCHIVE_*"; then, if there are <= 65535
records, just perform TransferSpreadsheet; if there are more than 65535
records, then run a crosstab query on the table (or query) and export
the results to Excel. Below is my pathetic attempt. Lame, I know. Any
assistance would be greatly appreciated.

PS. Using Acc97 and XL97, as noted. WinXP. I also have Office 2003
loaded on the same desktop, if that's of any use.

tia,

LeAnne

**BAD AIR CODE FOLLOWS**

Do While

TableDef.Name = “ARCHIVE*”


Sub CountRecords()
Dim dbs As Database, rst As Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("ARCHIVE_MyTable")
Debug.Print rst.RecordCount
rst.Close
Set dbs = Nothing
End Sub

Followed by this:

Sub Archive()
Dim dbs As Database, rst As Recordset
Set dbs = CurrentDb

IF CountRecords() <= 65535, Then

‘export table directly to Excel

DoCmd.TransferSpreadsheet acImport, 8, "ARCHIVE_MyTable","[filepath]", True

Else

‘Pivot first…

‘Dim something
‘Set object reference for the pivot recordset

DoCmd.RunSQL “TRANSFORM First(ARCHIVE_MyTable.[valuefield]) " & _
“SELECT ARCHIVE_MyTable.[fieldnames] ” &_
“FROM ARCHIVE_MyTable ” &_
“GROUP BY ARCHIVE_MyTable.[fieldnames] ” &_
“PIVOT ARCHIVE_MyTable.[columnfields];”

‘…Then export pivot recordset to Excel

‘Repeat for all tabledefs of type “ARCHIVE”

Exit Do

End Sub

 

[Guest]

Try archiving into a Word document with tab delimited.

 

[Immanuel Sibero]

Hi LeAnne,

- Any reason why you cant just flag your archive records with a boolean
field (i.e. Yes/No) and keep all the data in the database?
- If you feel the need to offload the archived data somewhere, any reason
why you cant offload it to another Access mdb file? Why Excel???
-*Pivot first then archive*??? So you have some raw data to archive and
since you dont have enough space in Excel you would summarize (i.e. pivot)
the raw data and then archive it?? What if you want to go back to the raw
data??

Immanuel Sibero

 

[LeAnne]

Hi Immanuel,

Thanks for replying. See comments below.

Hi LeAnne,

- Any reason why you cant just flag your archive records with a boolean
field (i.e. Yes/No) and keep all the data in the database?

Yes. See below.

- If you feel the need to offload the archived data somewhere, any reason
why you cant offload it to another Access mdb file? Why Excel???

Sigh...because that's what the boss wants.

-*Pivot first then archive*??? So you have some raw data to archive and
since you dont have enough space in Excel you would summarize (i.e. pivot)
the raw data and then archive it?? What if you want to go back to the raw
data??

Not exactly. It's quite easy to pivot without summarizing IF you
include enough information in the row columns of your crosstab. In that
case, FIRST(), LAST(), and AVG() all return the same result; I usually
use FIRST(). Second, these aren't exactly "raw" data, but derived data,
i.e. the results of calculations. I said "table (or query)" in my
original post for completeness, but I'm mainly concerned with the
outputs of queries that I've crafted to calculate these derived data. I
do have need to archive the results periodically, since I periodically
alter the formulae in queries to test different measures, modify how
measures are calculated, experiment with different ways to "score"
measures based on the outcomes of calculations, etc..

Hope this clarifies -

L.

Immanuel Sibero

Hi all -

I am, and shall ever remain, VBA-impaired. I would very much appreciate
some help translating the following garbage into usable code to
automatically, conditionally transfer Access (97) tables (or query
recordsets) into Excel (97) spreadsheets for archiving purposes. The
problem surfaces when a table (or query) recordset contains >65535
records. Excel 97 truncates the import at this cut-off point. I'm
trying to write a procedure that will count the records in a recordset
if the db object is named "ARCHIVE_*"; then, if there are <= 65535
records, just perform TransferSpreadsheet; if there are more than 65535
records, then run a crosstab query on the table (or query) and export
the results to Excel. Below is my pathetic attempt. Lame, I know. Any
assistance would be greatly appreciated.

PS. Using Acc97 and XL97, as noted. WinXP. I also have Office 2003
loaded on the same desktop, if that's of any use.

tia,

LeAnne

**BAD AIR CODE FOLLOWS**

Do While

TableDef.Name = “ARCHIVE*”


Sub CountRecords()
Dim dbs As Database, rst As Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("ARCHIVE_MyTable")
Debug.Print rst.RecordCount
rst.Close
Set dbs = Nothing
End Sub

Followed by this:

Sub Archive()
Dim dbs As Database, rst As Recordset
Set dbs = CurrentDb

IF CountRecords() <= 65535, Then

‘export table directly to Excel

DoCmd.TransferSpreadsheet acImport, 8, "ARCHIVE_MyTable","[filepath]",
True

Else

‘Pivot first…

‘Dim something
‘Set object reference for the pivot recordset

DoCmd.RunSQL “TRANSFORM First(ARCHIVE_MyTable.[valuefield]) " & _
“SELECT ARCHIVE_MyTable.[fieldnames] ” &_
“FROM ARCHIVE_MyTable ” &_
“GROUP BY ARCHIVE_MyTable.[fieldnames] ” &_
“PIVOT ARCHIVE_MyTable.[columnfields];”

‘…Then export pivot recordset to Excel

‘Repeat for all tabledefs of type “ARCHIVE”

Exit Do

End Sub

 

[LeAnne]

Interesting suggestion! I'll definitely look into it.

Thanks,

L.

Try archiving into a Word document with tab delimited.

:

Hi all -

I am, and shall ever remain, VBA-impaired. I would very much appreciate
some help translating the following garbage into usable code to
automatically, conditionally transfer Access (97) tables (or query
recordsets) into Excel (97) spreadsheets for archiving purposes. The
problem surfaces when a table (or query) recordset contains >65535
records. Excel 97 truncates the import at this cut-off point. I'm
trying to write a procedure that will count the records in a recordset
if the db object is named "ARCHIVE_*"; then, if there are <= 65535
records, just perform TransferSpreadsheet; if there are more than 65535
records, then run a crosstab query on the table (or query) and export
the results to Excel. Below is my pathetic attempt. Lame, I know. Any
assistance would be greatly appreciated.

PS. Using Acc97 and XL97, as noted. WinXP. I also have Office 2003
loaded on the same desktop, if that's of any use.

tia,

LeAnne

**BAD AIR CODE FOLLOWS**

Do While

TableDef.Name = “ARCHIVE*â€


Sub CountRecords()
Dim dbs As Database, rst As Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("ARCHIVE_MyTable")
Debug.Print rst.RecordCount
rst.Close
Set dbs = Nothing
End Sub

Followed by this:

Sub Archive()
Dim dbs As Database, rst As Recordset
Set dbs = CurrentDb

IF CountRecords() <= 65535, Then

‘export table directly to Excel

DoCmd.TransferSpreadsheet acImport, 8, "ARCHIVE_MyTable","[filepath]", True

Else

‘Pivot first…

‘Dim something
‘Set object reference for the pivot recordset

DoCmd.RunSQL “TRANSFORM First(ARCHIVE_MyTable.[valuefield]) " & _
“SELECT ARCHIVE_MyTable.[fieldnames] †&_
“FROM ARCHIVE_MyTable †&_
“GROUP BY ARCHIVE_MyTable.[fieldnames] †&_
“PIVOT ARCHIVE_MyTable.[columnfields];â€

‘…Then export pivot recordset to Excel

‘Repeat for all tabledefs of type “ARCHIVEâ€

Exit Do

End Sub

 

[Guest]

Set rst = dbs.OpenRecordset("ARCHIVE_MyTable")

Debug.Print rst.RecordCount
rst.Close

No, you need to do MoveLast before the record count is valid,
but really you should just do
set rst = dbs.Openrecordset("select count(*) as n from arch....
debug.print rst("n")

or n = Dcount( ....

DoCmd.RunSQL “TRANSFORM First(ARCHIVE_MyTable.[valuefield]) " & _
‘…Then export pivot recordset to Excel

You might be able to that like this:

dbs.Execute("select * into [Excel 8.0;DATABASE=c:\fred.xls].[hello]
from myquery")

(david)

Hi all -

I am, and shall ever remain, VBA-impaired. I would very much appreciate
some help translating the following garbage into usable code to
automatically, conditionally transfer Access (97) tables (or query
recordsets) into Excel (97) spreadsheets for archiving purposes. The
problem surfaces when a table (or query) recordset contains >65535
records. Excel 97 truncates the import at this cut-off point. I'm
trying to write a procedure that will count the records in a recordset
if the db object is named "ARCHIVE_*"; then, if there are <= 65535
records, just perform TransferSpreadsheet; if there are more than 65535
records, then run a crosstab query on the table (or query) and export
the results to Excel. Below is my pathetic attempt. Lame, I know. Any
assistance would be greatly appreciated.

PS. Using Acc97 and XL97, as noted. WinXP. I also have Office 2003
loaded on the same desktop, if that's of any use.

tia,

LeAnne

**BAD AIR CODE FOLLOWS**

Do While

TableDef.Name = “ARCHIVE*”


Sub CountRecords()
Dim dbs As Database, rst As Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("ARCHIVE_MyTable")
Debug.Print rst.RecordCount
rst.Close
Set dbs = Nothing
End Sub

Followed by this:

Sub Archive()
Dim dbs As Database, rst As Recordset
Set dbs = CurrentDb

IF CountRecords() <= 65535, Then

‘export table directly to Excel

DoCmd.TransferSpreadsheet acImport, 8, "ARCHIVE_MyTable","[filepath]", True

Else

‘Pivot first…

‘Dim something
‘Set object reference for the pivot recordset

DoCmd.RunSQL “TRANSFORM First(ARCHIVE_MyTable.[valuefield]) " & _
“SELECT ARCHIVE_MyTable.[fieldnames] ” &_
“FROM ARCHIVE_MyTable ” &_
“GROUP BY ARCHIVE_MyTable.[fieldnames] ” &_
“PIVOT ARCHIVE_MyTable.[columnfields];”

‘…Then export pivot recordset to Excel

‘Repeat for all tabledefs of type “ARCHIVE”

Exit Do

End Sub

 

[Immanuel Sibero]

Hi LeAnne,

Ouch, I didnt know one of your system requirements is your boss' preferred
file format. I mean Excel?
Anyway, based on your response to another poster you seem open to another
file format such as Word? If so, I would suggest offloading to a text file
(i.e. tab delimited or comma separated values). The limit of the file would
be your disk space.



HTH,
Immanuel Sibero

Hi Immanuel,

Thanks for replying. See comments below.

Hi LeAnne,

- Any reason why you cant just flag your archive records with a boolean
field (i.e. Yes/No) and keep all the data in the database?

Yes. See below.

- If you feel the need to offload the archived data somewhere, any reason
why you cant offload it to another Access mdb file? Why Excel???

Sigh...because that's what the boss wants.

-*Pivot first then archive*??? So you have some raw data to archive and
since you dont have enough space in Excel you would summarize (i.e. pivot)
the raw data and then archive it?? What if you want to go back to the raw
data??

Not exactly. It's quite easy to pivot without summarizing IF you
include enough information in the row columns of your crosstab. In that
case, FIRST(), LAST(), and AVG() all return the same result; I usually
use FIRST(). Second, these aren't exactly "raw" data, but derived data,
i.e. the results of calculations. I said "table (or query)" in my
original post for completeness, but I'm mainly concerned with the
outputs of queries that I've crafted to calculate these derived data. I
do have need to archive the results periodically, since I periodically
alter the formulae in queries to test different measures, modify how
measures are calculated, experiment with different ways to "score"
measures based on the outcomes of calculations, etc..

Hope this clarifies -

L.

Immanuel Sibero

Hi all -

I am, and shall ever remain, VBA-impaired. I would very much appreciate
some help translating the following garbage into usable code to
automatically, conditionally transfer Access (97) tables (or query
recordsets) into Excel (97) spreadsheets for archiving purposes. The
problem surfaces when a table (or query) recordset contains >65535
records. Excel 97 truncates the import at this cut-off point. I'm
trying to write a procedure that will count the records in a recordset
if the db object is named "ARCHIVE_*"; then, if there are <= 65535
records, just perform TransferSpreadsheet; if there are more than 65535
records, then run a crosstab query on the table (or query) and export
the results to Excel. Below is my pathetic attempt. Lame, I know. Any
assistance would be greatly appreciated.

PS. Using Acc97 and XL97, as noted. WinXP. I also have Office 2003
loaded on the same desktop, if that's of any use.

tia,

LeAnne

**BAD AIR CODE FOLLOWS**

Do While

TableDef.Name = “ARCHIVE*”


Sub CountRecords()
Dim dbs As Database, rst As Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("ARCHIVE_MyTable")
Debug.Print rst.RecordCount
rst.Close
Set dbs = Nothing
End Sub

Followed by this:

Sub Archive()
Dim dbs As Database, rst As Recordset
Set dbs = CurrentDb

IF CountRecords() <= 65535, Then

‘export table directly to Excel

DoCmd.TransferSpreadsheet acImport, 8, "ARCHIVE_MyTable","[filepath]",
True

Else

‘Pivot first…

‘Dim something
‘Set object reference for the pivot recordset

DoCmd.RunSQL “TRANSFORM First(ARCHIVE_MyTable.[valuefield]) " & _
“SELECT ARCHIVE_MyTable.[fieldnames] ” &_
“FROM ARCHIVE_MyTable ” &_
“GROUP BY ARCHIVE_MyTable.[fieldnames] ” &_
“PIVOT ARCHIVE_MyTable.[columnfields];”

‘…Then export pivot recordset to Excel

‘Repeat for all tabledefs of type “ARCHIVE”

Exit Do

End Sub