By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,501 Members | 2,681 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,501 IT Pros & Developers. It's quick & easy.

Number format change to text

P: 2
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.
Jul 19 '07 #1
Share this Question
Share on Google+
1 Reply


JConsulting
Expert 100+
P: 603
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.

Expand|Select|Wrap|Line Numbers
  1. Function ConvertXLS(strXLSFile As String) As String
  2. ' This function converts a Excel file to a CSV file
  3.     Dim xlApp As Object
  4.     Dim xlwb As Object
  5.     Dim strCSVFile As String
  6.     strCSVFile = Left(strXLSFile, Len(strXLSFile) - 3) & "csv"
  7.     Set xlApp = CreateObject("Excel.Application")
  8.     Set xlwb = xlApp.Workbooks.Open(strXLSFile, True)
  9.     xlApp.Visible = False
  10.     xlwb.SaveAs strCSVFile, xlCSV
  11.     xlwb.Close (False)
  12.     xlApp.Quit
  13.     Set xlApp = Nothing
  14.     ConvertXLS = strCSVFile
  15. End Function
  16.  
J
Aug 1 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.