Number format change to text

[michaelw118]

Hi,

I hope someone can give me some solutions to these problems I faced.

I imported some data to Excel. Then in Access, I table-linked to this file. I have two fields which in Excel is in number format but in Access, these fields property is text and the values in these fields become "#Num!". Others number format fields were okay, only these two fields (or particular columns?).

But if I linked directly to the datasource, it seems the properties remains as number format. However, i do not want to linked directly to the main datasource as I only want specific fields (about 20 out of 100 over fields). The Access file size become very big after refresh.

Another issue with direct linked to the main datasource is I can't see all the fields although I can see all fields in Excel when I do a database query.
Any ideas on this?

Thank you in advance for your help.

[JConsulting]

Hi,

I hope someone can give me some solutions to these problems I faced.

I imported some data to Excel. Then in Access, I table-linked to this file. I have two fields which in Excel is in number format but in Access, these fields property is text and the values in these fields become "#Num!". Others number format fields were okay, only these two fields (or particular columns?).

But if I linked directly to the datasource, it seems the properties remains as number format. However, i do not want to linked directly to the main datasource as I only want specific fields (about 20 out of 100 over fields). The Access file size become very big after refresh.

Another issue with direct linked to the main datasource is I can't see all the fields although I can see all fields in Excel when I do a database query.
Any ideas on this?

Thank you in advance for your help.

Use this function to convert your .xls file to a .csv file prior to importing. Because it's now a comma delimited file, you can use TransferText to import the data while creating an import Specification. You can set the field types and names there, as well as saving the Specification as a name that you can use in code again and again.

Function ConvertXLS(strXLSFile As String) As String
' This function converts a Excel file to a CSV file
    Dim xlApp As Object
    Dim xlwb As Object
    Dim strCSVFile As String
    strCSVFile = Left(strXLSFile, Len(strXLSFile) - 3) & "csv"
    Set xlApp = CreateObject("Excel.Application")
    Set xlwb = xlApp.Workbooks.Open(strXLSFile, True)
    xlApp.Visible = False
    xlwb.SaveAs strCSVFile, xlCSV
    xlwb.Close (False)
    xlApp.Quit
    Set xlApp = Nothing
    ConvertXLS = strCSVFile
End Function
 

J