addition of apostrophe in data in excel after access export

[Giz]

Hi,
I have exported a table from Access to a excel file. When I open the table
in excel all the data under Access text fields have had an apostrophe added
to the beginning of the value. The apostrophe is not visible on the sheet in
excel, just when I click on a cell and its value appears in the formula bar.

For example, if a cell in a text field in the datasheet view in access had a
value of dog, then after export and display as a spreadsheet in Excel that
cell still displays dog in the sheet, but in the formula bar it's value is
'dog. All cells that were imported have this condition. This is important
because it effects further manipulation of the data. How can I eliminate/and
or remedy this??
thanx

 

[John Nurick]

Here's an Excel VBA macro:

Sub RemoveApostrophes()
Dim C As Excel.Range
For Each C In Application.Selection.Cells
C.Formula = C.Formula
Next
End Sub

 

[Tsoying]

John,

The info you posted here was very useful. Only can this macro be run after
the transfer from Access to Excel is completed and assuming the Excel file
and macro were there prior to it was transferred. What should I do if the
Excel need to be created new everytime transferr was completed? Does that
mean I need to cut and paste your macro into the new Excel
tools->Macro->Visual Basic Editor? Also your macro takes one sheet a time,
is there a better way to take care all sheets in a whole workbook?

Thank you so much.
Tsoying

 

[John Nurick]

Again, you can do this by automating Excel from Access as in my post in
your other thread.

Modify the RemoveApostrophes() procedure to work on an entire worksheet
(warning: air code follows):

Sub RemoveApostrophesFromSheet(S as Excel.Worksheet)
Dim C As Excel.Range

For Each C in S.UsedRange.Cells
C.Formula = C.Formula
Next
End Sub

Then have a procedure that loops through the worksheets calling this sub
each time:


Sub RemoveAllApostrophes(W As ExcelWorkbook)
Dim S as Excel.Worksheet

For Each S in W.Worksheets
RemoveApostrophesFromSheet S
Next
End Sub

Finally you'd call it with something like

RemoveAllApostrophes oxlBook

 

[Tsoying]

John,

If I udnerstand your instruction correctly, this new RemoveAllApostrophes
procedure should be coded in VBA from Access side. Am I correct? If yes,
then where would I code this procedure from my VBA codes below because these
are still Access Tables and Excel workbook are not formed yet? Unless this
procedure needs to be on the Excel side. I am confused. Thanks,

I = 1
' Reports
Do While (I <= 4)
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, VpName(I),
(Path & "Business Adjustments 2005 - " & VpName(I) & ".xls"), False
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97,
DrName(1), (Path & "Business Adjustments 2005 - " & VpName(I) & ".xls"), False
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97,
DrName(2), (Path & "Business Adjustments 2005 - " & VpName(I) & ".xls"), False
I = I + 1
Loop

Tsoying

 

[John Nurick]

Tsoying,

You would do it after the loop below, at which point the workbooks have
been created.

The code could be along these lines (untested air code):

...
Do While I <= 4
'your existing loop

Loop

'Create instance of Excel
Dim oXLApp as Excel.Application
Dim oXLBook as Excel.Workbook
Set oXLApp = New Excel.Application

For I = 1 to 4
'Open workbook
Set oXLBook = oxlApp.Open(Path & _
"Business Adjustments 2005 - " & VpName(I) & ".xls")
'Remove apostrophes
RemoveAllApostrophes oxlBook
oXLBook.Close True
Next

oXLApp.Quit

The RemoveAllApostrophes() and RemoveApostrophesFromSheet() procedures
should be pasted into either the same module as your code, or into a
separate code module.