field types when linking data

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

Guest

I'm trying to link to an Excel 2000 spreadsheet in Access 2000. I'm getting frustrated at my inability to set file types as text. I added a dummy line of data on the first line after the column headers, but it's still definining just about all the fields as numbers, and any text from the excel sheet comes in as #Num

I could probably get around this by importing every time instead of linking, but I don't want to.

Is there any other way to make sure that the file type in a linked table is text?
 
Behaviour when linking is for some reason different from when importing.
Perversely, Access will only use a text field if there are no numeric
values in the rows it examines.

One way round this is to prefix numeric values in Excel with an
apostrophe '. This forces Excel and Access to treat them as text. The
apostrophes do not appear in the Access table. You can use code like the
samples below to add or remove the apostrophes under program control.
For more information, see the Knowledgebase articles below.

Sub AddApostrophes()
Dim C As Excel.Range
For Each C In Application.Selection.Cells
If IsNumeric(C.Formula) Then
C.Formula = "'" & C.Formula
End If
Next
End Sub

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

Convert Text to Numbers in Microsoft Excel 2002
http://support.microsoft.com/default.aspx?scid=kb;en-us;291047

How to Convert Text to Numbers
http://support.microsoft.com/default.aspx?scid=kb;en-us;181298

I'm trying to link to an Excel 2000 spreadsheet in Access 2000.
Click to expand...
I'm getting frustrated at my inability to set file types as text. I
added a dummy line of data on the first line after the column headers,
but it's still definining just about all the fields as numbers, and any
text from the excel sheet comes in as #Num!
 
Back
Top