EXPORTING TO EXCEL : Get Rid of ---- ' SINGLE QUOTE that APPEARS IN CELLS AFTER OPENING XLS FILE

fordraiders,
what types of fields is this happenning to?  (field type as text/number)
I assume our previous discussion doesn't help here?
https://www.experts-exchange.com/questions/20578298/REMOVE-SINGLE-QUOTE-FROM-CELLS-FORMAT-CELLS-AS-TEXT.html

I'll certainly try and help if I can.

iczky,
its text and all the fields..
fordraiders

fordraiders,
I'm on A97 today, but have A2K at another office.  My A97 doesn't add the ' when exporting text.  I'll check it out on A2K and post back by tomorrow.

To further clarify, are you exporting using code - or does this happen even when you go to:

File
Save As/Export
External File or Database
Excel File
file.xls

I'll do my best to help, and get back to you tomorrow.

I assume that the A) the field type in access is set to text and B) the information is numerical in nature.  The single quote in the beggining of the excel cell indicates that what follows is text and is not to be interpreted as numerical values by excel.  A simple however dangerous aproach would be to change the field type in access to a numerical type that can accomidate the numerical values you have in that field. i.e. integer for whole numbers, or decimal for numbers with decimals in them.  Access will convert the text data (which appear as numbers) to numerical data for you.  Then try exporting them again.  I suggest you make a copy of your database file and work out of the copy for this, just in case something goes wrong in the conversion proccess.

Try creating a query that contains all of the fields you need from your table, and wrap the numeric fields in a conversion function, eg:

CDbl([MyField])
CLng([MyField])

for decimal and integer fields respectively. Then export the query rather than the table.

let point out the dangerous part of the solution I presented is that the conversion of a field from text to a numerical type could, but should not result in data loss.  Unless you have a really good reason for storring numerical values as text you should considder not only changing them to numerical types but leaving them as numerical types, as you can't do any calculations on these values while they are text. Not to mention you can't sort properly on a text field with numbers in it.

To All:
The problem is not numeric.
It Text fields.
fordraiders

izck,
Both ways.

fordraiders

ok in that case rather than export the data from access and importing to excel try pulling the data from access using excel

go to the "data" menu; trace to "get external data"; select "new database query" under databases select MS Access, then point it to your database and follow the instructions for building the querry.

How are you transferring the data - using TransferText or OutputTo ?

Does anyone else have this situation?

It does not matter how I export.
vba or menu system.
fordraiders

did you try this?

go to the "data" menu(in excel); trace to "get external data"; select "new database query" under databases select MS Access, then point it to your database .mdb file and follow the instructions for building the querry.  This method will use MS query to access the data and then allow you to drop it into the worksheet that is open.

Have you actually looked at the export file excel has been creating?  What exactly does the text look like inside of it? What type of file is it .csv, .xls?  Keep in mind that excel can import from many differant types of file formats, and access can export to many different types of file formats. Try a different format and see if you have the same results.  Also you have failed to mention where the single quote is in the cells in excel, is it at the begining of the cell, the end, between words, or maybee in all the cells that have no text?  

You know this is my first day posting here and I'm noticing a trend.  Many people seem to post very vague descriptions of their problems, and you happen to be one of them, and the more people post the more vague and less descriptive you seem to be.  Why don't you throw us a bone here.... try posting a record here as it appears in the export file so people can stop asking you questions and acctually see your problem.  Also how about a link to a screen shot of the data as it appears in excel?  

Almost forgot, have you installed the latest updates for MS office? cuz there's a bunch of them out there.

TransferSpreadsheet (which is what I meant to say, not TransferText) and OutputTo work differently. Have you tried both ?

Yes,
I have tried all forms of vba and macros.

Thanks
fordraiders

iczky,
Is there a vba object for "SAVE FORMATTED"
PROPERTY FOR METHOD?

Thanks
fordraiders

fordraiders, did you see my comment about the checkbox?
-just wanted to rule that out as a solution.

Ahh, you did see it.  I'm not aware of one, but I've been looking for it for you.  

Since, "Is there a vba object for "SAVE FORMATTED"
PROPERTY FOR METHOD?" seems like the actual question at hand, maybe one of the other experts here will know.

shanesuebsahakarn, chikenhead any thoughts?

I will test this on my A2K machine and get back to you. I *think* OutputTo is the same as Save Formatted but if not, you'll need a function to export to Excel. I've got one knocking about somewhere.

Export the data as a text file and use the default options in the wizard, but when you name the file specify the csv extension. I.E. filename.csv which will create a CSV (comma seperated values) type file.  Then simply open the file with excel.  you will have to set the file type in the open dialog box in excel to text files.  It worked for me hope it works for you

Great suggestions from all,
But I think as you know evertyhing as to be dummty proof.
I will probably have to vba this thing with no interaction from a user.
fordraiders

In that case you might considder running a procedure that checks every cell in the spreadsheet for for the ' character using the len() function, right after you perform the inport.  Then simply have it remove it.  Or considder making an ADO connection to the access database and querying the data into a recordset in vba and then dummping it right into your datasheet.  Keep in mind that there are many ways to accomplish what you are attempting it's just a matter of choosing a method that works.

shanesuebsahakarn ,
Did you test the "output to:"?
fordraiders

SAVE FORMATTED WORKED

fordraiders, were you ever able to solve this programmatically?

iczky,
No,.

I know what your trying to do, here's how I do it:

Export from access as a text file *.csv,  Create an export spec with (none) as the text qualifier.  If CSV files are associated with Excel, then excel will open it up and there will be no " ' ".  I assume, you want to store formulas and values in access fields as records, so you can dynamically build spreadsheets from access.  This works for me, I wish there was another way.

Ross