"DoCmd.OutputTo" to Excel truncates character/ field

[Claude Colaco]

Hi all,

I have been using this command in VB to export Access tables to Excel. Everything works fine except when the table contains a large Character/Text field- it gets truncated to around 200 chars. Is this a known limitation or I am doing something wrong? DoCmd.TransferSpreadsheet command works correctly but there are some limitations to this command.

Can please somebody help
Thanks
Claude

 

[Bilbo_Baggins_Esq]

This is an Excel Limitation. I'm pretty sure it is 254 characters. The only way around it would be to add a column whose function checks the length of the initial column and if it is greater than 254, returns the string after the first 254 characters.

 

[Moniker]

Also, keep in mind that that DoCmd.OutPutTo limits you to around 16K records, whereas DoCmd.TransferSpeadsheet does not have that limitation.

And, on the subject of Excel limits, the width limit is 255 for a cell, which is probably what you're hitting. Turn on Wrap Text for every cell to view up to 1024 characters. (That's in Format -> Cells -> Alignment tab.) A cell in Excel will hold approximatley 32K worth of text, but it can only display 1024 of them. All of the text will be in the formula bar at the top, but that makes for horrid editing/viewing.

 

[Claude Colaco]

Thanks a lot guys

DoCmd.TransferSpreadsheet does more but DoCmd.OutPutTo offers a better user experience - allows you to choose the location of the file and also opens Excel with the exported file. Can we do the same with DoCmd.TransferSpreadsheet?

Thanks
claude

 

[Moniker]

TransferSpreadsheet allows you to select the location and name the file whatever you want to. There's no built-in option to open Excel with it, but you could easily do it with an Excel Object.