Text Becomes Decimal on Export to Excel

[eed]

Ooookay, another little question. Maybe someone has seen this before and can explain it to me.

I have a text-type field with alphanumeric values like "16P" and "7A." ALL of them appear correctly in Access itself (table and forms).

When the table is exported to Excel, 99% of these values are fine. But some of them (right now it's 16P, but it's been a different value in the past) are converted into decimals!!

It does not do this every time you export, only sometimes, it's completely unpredictable. What on earth could be causing Excel to sometimes spontaneously interpret a text field as a decimal? <<see additional info added below!>>

Any and all thoughts are more than welcome and DEEPLY appreciated, because I don't even have guesses on this one. THANKS!!!!

-----> Additional Information To Add <-----

Okay, I have examined this bizarre event a little more. It's not entirely random: it is only happening on values that are #P, and by comparing the decimals produced, it definitely appears to be a constant formula of (# * P), with P having a value that rounds to about 0.0417.

If there is additional text in the cell besides #P -- even if it is just "#P and ##P" -- the text displays fine and this mysterious calculation doesn't occur.

This only happens on certain computers, meaning that whatever is going on must be a default setting somewhere in that computer's Excel load, but I don't know what or where.

[tommy bak]

This is maybe a wild guess, but cuold it be that p is interpreted as PM and that excel then convert it to a value. You value 0.0417 is exactly 1/24 part of a day = one hour

If this happens again, try checking the Regional settings and see if PM is set to PM

BR
Tommy Bak

[eed]

This is maybe a wild guess, but cuold it be that p is interpreted as PM and that excel then convert it to a value. You value 0.0417 is exactly 1/24 part of a day = one hour

If this happens again, try checking the Regional settings and see if PM is set to PM

BR
Tommy Bak

Are you thinking about regional settings for the Excel file? the Excel application? the computer itself? I'm not having much luck locating regional settings in Excel, but if it were something in the regional settings of the computer itself, would Excel really decide that 16P = 0.666666666666667? I'm more than willing to check out your suggestion, just tell me where I should start looking for this possible fix... thanks!!!!

[tommy bak]

If it happens on only some computers I would have a closer look at Windows control panel. Look under Regional Settings (International, if win98 ) on the tab with date/time-format a see if it differs from one of the computers that works allright.
Then again, I'm not sure, but 0,66666 equal 16 hours in excel (24 hours = 1)
and the fact that your problem is only with P (PM) and A (AM) suggests to me that excel is considering it as a timevalue.


Tommy Bak

[eed]

I'm not having any luck with identifying *why* Excel thinks this is a time function or how to eliminate that time function. If I could maybe approach this problem from a different angle...

Does anyone know how to explicitly identify a field as Text when exporting from Access to Excel? Right now Excel classifies everything on the sheet as data type "General." If I could specify that Access should export everything to Excel as data type "Text", I think that would prevent the misinterpretation of "16P" as a function of "16 * 0.0417".

Any ideas on that approach to the problem...?

[parry]

Hi, this is odd. When I export a table from Access into an Excel file, my field which has a Text data type but contains numbers is exported as text (ie isnt converted to #'s). When you select the table/query then choose File|Export & choose a filetype you will see a box that has Save As Formatted. Tick this and see if it makes any difference.

If still not a goer you can choose to export to a Text file and you will have the option (under Advanced button) to choose an export spcification whereby you can control the data type of each field.

hth

[mark007]

Format the column as text before importing. Excel will not reformat it then.

[eed]

But if I am exporting from Access to Excel (never importing in Excel from Access) and performing the output through VBA, is there a way that I can force the data type of the exported field to be "Text" instead of "General"? Once it is in Excel, it is too late to select the column and set it to Text, because the decimals are not converted back into the proper alphanumeric string.

If necessary, since the decimals have been found to follow a predictable pattern, I could program the VBA to find these decimals and replace them with the appropriate "##P" text string. However, this seems like a bandaid to cover up the problem, as opposed to a solution. I would rather understand why some of my network's Excel loads interpret "##P" as a request to perform a calculation and how to have the misinterpretation not happen at all.

[kenhigg]

Are you using the OutputTo macro?

[eed]

Are you using the OutputTo macro?

I'm using the DoCmd.OutputTo method in VBA, like this:

[VBA]
DoCmd.OutputTo acReport, "rptLIS", "MicrosoftExcel(*.xls)", strExcelPath, False

[/VBA]

[mark007]

I don't know access too well but can you specify directly a worksheet to use in some way? If so then you can format the relevant column as text before exporting the data.

[CBrine]

FYI,
I remember running into formating problems in the past. It seemed as though the output to command would default the excel format based on the data in the first row(ie. titles, or 1st row of data if no titles we exported). If you are not exporting the column headings, try changing it so that they do get exported. Also make sure your column headings are all alpha characters.

[eed]

CBrine - Yes, I export my column headers along with the data, and they are all alphabet characters, not numerics. In fact, ALL my fields are defined as text in Access, but Excel doesn't pay any attention to my Access data type definitions. It defines everything as "General" no matter what.

mark007 - Normally I just create the spreadsheet during the OutputTo so that I don't have to worry about my users moving/deleting a specified sheet. But your idea still might work... I can check for a specific sheet, and if it exists, I can open it, format the column, then export... if it doesn't exist, I can create it, open it, format the column, THEN export... hey, that might do it!

I'll check it out and post whatever solution I might find. Thanks all for the insightful thoughts and suggestions!

[eed]

Okay, that was a good idea, but no. If I format the cell data type to "Text" in Excel first, then export data from Access to Excel, I just write over the type that I set and everything becomes "General" again. Excel basically doesn't hold any formatting when it's receiving data from Access (unless possibly if you use a template, which I have been trying to avoid for this procedure but may soon have to try).

This is awful, I am so confused. This is something going on with the Excel application itself, not my document, because otherwise it would happen consistently on any machine using my document.

Is there a place in Excel to define custom functions or custom constants or something (I don't know, like the way that you can define custom Auto-Correct items in Word)?? If I could find a place like that, it would give me a place to start looking for settings to Undo.

Thank you sooo much to everyone for their continued thought and assistance...

[parry]

Hi, try the TransferSpreadsheet method.

[VBA]DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "table1", "C:\Text.xls", False [/VBA]

[eed]

Hi, try the TransferSpreadsheet method.

[VBA]DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "table1", "C:\Text.xls", False [/VBA]

Sorry it's taken me so long to come back to this post. The TransferSpreadsheet method didn't prevent the decimal change either. Since I have so far failed to find and treat the cause of this problem, I have temporarily set up a treatment for the symptom: after exporting to Excel, my database is running a procedure to locate all decimals in the problem field and replace them with the proper value (they're all predictable since they're constant). Thanks for everyone's help. I'm going to continue to consider this problem if I have some spare time in the future, but for now I suppose I have a temporary patch...

[sgrant]

As a work around you may want to export your Access data to a prn or text file, then import it into excel. This should allow you to use the Excel import parser to specify the field types. You can also automate this using a macro if you need to.

[jamescol]

This is a known issue for Excel. In some instances, a number followed by a P is converted to a time format. There is no fix for the issue, but a workaround is to insert an apostrophe in front of the value.

[sgrant]

That is correct! But consider that the apostrophe would be visible in Access as part of the text, but not shown in Excel as part of the Formatting for the cell in deciding on this option.

[eed]

This is a known issue for Excel. In some instances, a number followed by a P is converted to a time format. There is no fix for the issue, but a workaround is to insert an apostrophe in front of the value.

Jamescol, thank you for mentioning this -- I couldn't believe I would be the only person who ever experienced this problem. Now I know I'm not crazy.

sgrant -- thanks for the suggestions. For my purposes, the apostrophe in Access is not a convenient solution. I will try writing a macro to export through a prn or txt file and parse the field types. Hopefully that will solve my problem.

Thank you both very much!