Ok, ive asked this already a bunch in other discussion places, but no one
knows. Or cant fix it. This is pretty much my last hope. Here it is. I am
trying to export a table from access into an excel page. I can do that, i got
that far. Now, my excel page is full of formulas that make a really good
summary sheet. All the data that im using in access is there, but when i put
it into the relevant cells, the calculations dont work. All of the text cells
have an apostrophe (') added to the beginning of the data. As well, all the
numerical data (which doesnt have the ' added) doesnt work either, but it
seems like normal data. Is there something i need to do? or am i pretty much
outa luck here?

Here is a Procedure that will change your text numbers into actual numbers.
It also removes the apostrophe from Text. Highlight the range you want to
convert and run the macro.

Private Sub Convert()
Dim rngCurrent As Range
Dim rngToSearch As Range

Set rngToSearch = Intersect(ActiveSheet.UsedRange, Selection)
If Not rngToSearch Is Nothing Then
Application.Calculation = xlCalculationManual
For Each rngCurrent In rngToSearch
If Left(rngCurrent.Value, 1) < "=" Then

If IsNumeric(rngCurrent.Value) Then
rngCurrent.NumberFormat = "0"
rngCurrent.Value = CDbl(rngCurrent.Value)
rngCurrent.Formula = rngCurrent.Value
Else
rngCurrent.Formula = rngCurrent.Value
End If
End If
Next
Application.Calculation = xlCalculationAutomatic
End If
End Sub

HTH

"Stuart Y." wrote:

Ok, ive asked this already a bunch in other discussion places, but no one
knows. Or cant fix it. This is pretty much my last hope. Here it is. I am
trying to export a table from access into an excel page. I can do that, i got
that far. Now, my excel page is full of formulas that make a really good
summary sheet. All the data that im using in access is there, but when i put
it into the relevant cells, the calculations dont work. All of the text cells
have an apostrophe (') added to the beginning of the data. As well, all the
numerical data (which doesnt have the ' added) doesnt work either, but it
seems like normal data. Is there something i need to do? or am i pretty much
outa luck here?

I export from Access into Excel regularly. There are times when a
numeric value stored in a text-formatted Access table field gets
exported to Excel. When that happens, I get a single quote prepended
to the number in the Excel worksheet.

To solve this problem, you define a range, then you loop through each
cell in the range and apply a number format and convert to double. But
is it the application of the format or the type conversion that removes
the quote?

I'm also curious why the value is assigned to a formula:

rngCurrent.Formula = rngCurrent.Value

And I assume you are recalculating any formulas with this:

Application.Calculation = xlCalculationAutomatic

once you complete the loop.

Is this correct?

Im not having any trouble with the number format, it was just the text format
gaining the '. With the numbers, i just select all the relevant cells, and
convert to number (with the tab that appears to the left of the cell) But i
think i might have to recalculate according you what you say there. Thanks
for the help guys!

Private Sub Convert()
Dim rngCurrent As Range
Dim rngToSearch As Range

Set rngToSearch = Intersect(ActiveSheet.UsedRange, Selection)
If Not rngToSearch Is Nothing Then
Application.Calculation = xlCalculationManual
For Each rngCurrent In rngToSearch
If Left(rngCurrent.Value, 1) < "=" Then

If IsNumeric(rngCurrent.Value) Then
rngCurrent.NumberFormat = "0"
rngCurrent.Value = CDbl(rngCurrent.Value)
rngCurrent.Formula = rngCurrent.Value
Else
rngCurrent.Formula = rngCurrent.Value
End If
End If
Next
Application.Calculation = xlCalculationAutomatic
End If
End Sub

Ive tried that macro, and all it seems to do is take the entire column to
the left of my selected column, and move into the selected column. Im not all
that great with the programming so im having trouble fixing that

I think what's happening in my case is Excel is doing some kind of automatic
parsing on the data it receives via export from Access. Excel inserts the
leading apostrophe into cells it thinks should be text.

The use of a leading apostrophe in a cell is a common trick to force a cell
to be treated as text so Excel will not automatically apply date or number
formatting to that cell.

As far as I know, there's no way to prevent the Excel from doing this
automatic parsing when receiving data via export from Access. The leading
apostrophe must be removed from the cells after the data is in the
worksheet.

Sub RemoveApostrophe()
For Each CurrentCell In Selection
If CurrentCell.HasFormula = False Then
CurrentCell.Formula = CurrentCell.Value
End If
Next
End Sub

I found this code he
http://support.microsoft.com/default...;en-us;Q124739

This essentially does the same thing as Mr. Thomlinson's code.