Transfer Spreadsheet Command Access 97

[DALIEN51]

This problem seems crazy to me.......

If I create a macro with a single entry consisting of Transferspreadsheet from a single Access table to Excel it exports perfectly. When I call Transferspreadsheet from VBA code it moves some of the data and puts them into seemingly random Excel spreadsheet columns why is this?

This issue first came about when we had to export a table with more than 65,000 records in and so I export using a table name "Sheet1" , "Sheet2" as needed but surely the table name that it comes from makes no odds?

Any help appreciated!

Regards,

Neil

 

[The_Doc_Man]

Check your specifications for EXCEL

If it is Excel 97 then you cannot create a spreadsheet with 65536 rows or columns. Blows up long before that.

Not sure but I think newer EXCEL versions also have that limit.

As to why the difference between VBA and Macro cases, damfino

I know that macros are STUPID and have no error handling capability. But they also have no variables, and that might be the key.

The VBA-side issue is whether you used an INTEGER or a LONG (or a constant) for any of the parameters in the VBA. With numbers greater than 32767 and an INTEGER for a holder, you are playing with negative numbers. When it gets above 65535, you are dealing with TRUNCATED numbers.

So how did you declare the variables that fed your VBA command?

 

[ghudson]

Excel 2003 still has the 65,536 row limitation.

Why not export the data as a text file and give it a file extension of .csv? That will get you past the Excel row limitation. You can custom sort your data if you export it with a query.