Linking Excel into Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am linking an Excel spreadsheet into Access. I was going to use an Append
query the first time I brought the excel file over then update after that. I
noticed that after trying to run the append query failed. I looked at the
columms and seen #NUM! where the values were all numbers. I checked and
seen the excel columm format was GENERAL so I changed the format to TEXT. I
tried it again but with the same result. I understand that the Text format
cells are treated as text even when a number is in the cell. The cell is
displayed exactly as entered. What can I do to get the numbers to react as a
text?
 
Tom,

I have found that you cannot depending the Excel formatting to get it right.
I have seen where the format was General, numbers were entered, then the
format changed to text, but the data in the cells continued to act like
numbers.

The easiest way around this is to wrap all columns that could be numbers
with the Nz function in your append query. For example, in the Field row of
the column that is numeric:

Exp1: Nz([Excel]![NumberCell],0)
 
Hi Tom,

The rules that the Jet database engine (and therefore Access) applies
when importing or linking Excel data are complicated and confusing. See
http://www.dicks-blog.com/excel/2004/06/external_data_m.html for
details.

One guaranteed way to ensure that Jet treats numbers as text is to
preface them with apostrophes, e.g. '999 instead of 999. This forces
Excel to treat them as text regardless of the cell format. I've pasted
Excel VBA functions to add or remove apostrophes at the end of this
message.


I am linking an Excel spreadsheet into Access. I was going to use an Append
query the first time I brought the excel file over then update after that. I
noticed that after trying to run the append query failed. I looked at the
columms and seen #NUM! where the values were all numbers. I checked and
seen the excel columm format was GENERAL so I changed the format to TEXT. I
tried it again but with the same result. I understand that the Text format
cells are treated as text even when a number is in the cell. The cell is
displayed exactly as entered. What can I do to get the numbers to react as a
text?
Click to expand...

Sub AddApostrophesNumericToSelection()
'adds apostrophes to numeric values only
Dim C As Excel.Range
For Each C In
Application.Selection.SpecialCells(xlCellTypeConstants).Cells
If IsNumeric(C.Formula) Then
C.Formula = "'" & C.Formula
End If
Next
End Sub

Sub AddApostrophesAllToSelection()
Dim C As Excel.Range
With ActiveWorkbook.ActiveSheet
For Each C In Intersect(Selection,
..UsedRange).SpecialCells(xlCellTypeConstants).Cells
C.Formula = "'" & C.Formula
Next
End With
End Sub

Sub AddApostrophesAllToColumn( _
ByVal TheColumn As Long _
)
'Add apostrophes to all cells in specified column
Dim C As Excel.Range
With ActiveWorkbook.ActiveSheet
For Each C In Intersect(.Columns(TheColumn), _
.UsedRange).SpecialCells(xlCellTypeConstants).Cells
C.Formula = "'" & C.Formula
Next
End With
End Sub

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