Text Export to Excel Wrong Format?

EvanL
EvanL used Ask the Experts™
on
I've got a text field in a report.  The text field is an ID number that refers to a document.

Sample data for this field is:

12F
22G
32A
45P

When I export this report to MS Excel, all fields look fine except for any field that ends with the letter "A".  Weird.  So the above column would look like:

12F
22G
0.541666667
45P

Anyone have an idea what might be causing this?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
yes, it thinks that 32A is something else for a data type, such as when you type in 3/14 it will think that it is a date when it may not be. make sure that your field format in excel is set to Text and not General. General will change if it reconizes a new type.

Author

Commented:
Thanks for the reply.  My problem, is that MS Access is creating the file, so I have no place to tell it to use the "text" format instead of "general".

Obviously, you could go into MS Excel and change all of the formats, but this is supposed to be an automated process for the end user.
Commented:
No problem at all. Using OLE automation you can reformat Excel file :

Dim oEx As Object, ExCol as Object
  Set oEx = CreateObject("Excel.Application")
'   oEx.Visible = True
  oEx.Workbooks.Open "C:\1.xls"

        For Each ExCol In oExWorkbooks(1).Worksheets(1).Columns
            ExCol.Select
            ExCol.AutoFit
            oEx.Selection.NumberFormat = "0.00"
        Next

  oEx.Workbooks(1).Save
  oEx.Quit
  Set oEx = Nothing

D'Al
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

not a number format though. it has letters in it. i think that you should replace the line:

oEx.Selection.NumberFormat="0.00"
with:
oEx.Selection.TextFormat
right?

Commented:
Wrong. :-(

oEx.Selection.NumberFormat = "@"

Anyway, my mistake....
you probably haven't even realized that this question was still hanging, EvanL...  =)

whatever. i am doing clean-up of questions i have posted to and yet haven't been accepted yet. i would reward the points to samopal to send this to the PAQ pile. he answered your question perfectly.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial