Format text when export data from Access to Excel

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Fractal - 29 Apr 2008 22:23 GMT

In the following code, data is dumped from query result to an Excel file,
starting at cell a4. My problem is the data in the first column from my
query results . Even though they are all text data (however, numeric in
nature), Excel will change them to numeric. For example, "0006" becomes 6 in
the Excel cell. If I use TransferSpreadsheet command, I do not have this
data issue, but I can not direct the data to start at cell a4. Your help is
appreciated.

Set rs = CurrentDb.OpenRecordset(strQryName, dbOpenDynaset)
'dump all data, starting at cell a4
objXLws.range("a4").CopyFromRecordset rs

Reply to this Message

Tony Toews [MVP] - 30 Apr 2008 01:19 GMT

>In the following code, data is dumped from query result to an Excel file,
>starting at cell a4. My problem is the data in the first column from my
[quoted text clipped - 7 lines]
> 'dump all data, starting at cell a4
> objXLws.range("a4").CopyFromRecordset rs

Try putting cstr(fieldname) around the appropriate fields in the
query. That might, or might now work.

Tony

Signature

Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

Reply to this Message

Fractal - 30 Apr 2008 18:12 GMT

Using Cstr in the query actually removed the prefix 0's. I got "6" back in
the query result. Do you have other suggestions? Thanks.

> >In the following code, data is dumped from query result to an Excel file,
> >starting at cell a4. My problem is the data in the first column from my
[quoted text clipped - 12 lines]
>
> Tony

Reply to this Message