how to export with format using TransferSpreadsheet

[Darizotas]

Hi,

Whenever I export a query (with just one text field) using
TransferSpreadsheet and I open the Excel sheet to see the result, I realize
that each field starts with an apostrophe. How can I get rid of it?

I've noticed that if I export this query my means of the option menu
"Export" + with format, the Excel sheet has another appearance and there's no
apostrophe.
Is there anyway I could set a format programmatically to that field to avoid
this issue?

Thanks,
Dario

 

[Jeanette Cunningham]

Darizotas,
here are my notes about this:

All text fields in Excel are getting a 'sign in front of the text.

This is normal, and necessary in order to force Excel to treat values as
text even if they can be interpreted as numbers.
Normally the apopostrophe isn't a problem, because Excel doesn't display
it in the worksheet, omits it from the Value and Formula properties of
the cell, and omits it from any reference to the contents of the cell.


If you do need to be rid of it, use code like this:


Public Sub DeApostrophise()
Dim C as Excel.Range
For Each C in Selection.Cells
C.Formula = C.Formula
Next
End Sub
John Nurick


Jeanette's notes:
Running the code above changes numbers as text to numbers and removes any
leading zeros.

To turn off the green error arrows use, this does it for the whole
application and if someone turns error checking back on, they show up again.

Application.ErrorCheckingOptions.BackgroundChecking = False


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Darizotas]

Hello Jeanette,

Sorry for the delayed response. I will try the snippet of code you included
on your answer.

But I am still intrigued on why using the right-click pop up menu ->
Export... -> export as Excel 97-2000 and check "Save with format" has a
different behaviour. And that behaviour cannot be reproduced
programmatically. Do you know if it could be related to excel default
templates?I don't have the foggiest idea.

Thanks,

Dario

 

[Jeanette Cunningham]

Yes, I tried as you said with export formatted.
I have excel 2003 and I have the option to export as Excel 97-2003.
I got the same result as you.
However in excel 2003, the apostrophe only appears in the formula bar, you
can't see it in any of the cells in the spreadsheet.
Can you see the apostrophe in cells of the spreadsheet in excel 2000?
I don't know why the difference between export formatted and not formatted.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Darizotas]

I've got Office 2000 and it's the same situation. I can see the apostrophe in
the formula bar or whilst editing the cell value.

But this is given me problems, there's another application (third party)
that reads from that data and it's unable to do it if it finds the apostrophe.

Thanks anyway,
Darío.