Format text when export data from Access to Excel

  • Thread starter Thread starter Fractal
  • Start date Start date
F

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
 
Fractal said:
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
Click to expand...

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/
 
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.
 
Back
Top