Format text when export data from Access to Excel

[Fractal]

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

 

[Tony Toews [MVP]]

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

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

Tony
--
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/

 

[Fractal]

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.