Importing excel files

[tar28un]

Hi there,

I am trying to import excel files in access via the following code:
CD.DialogTitle = "Select any one of the NOR files and press OPEN"
CD.ShowOpen
FileName = CD.FileName
DoCmd.TransferSpreadsheet acImportDelim, , "tbl_Nursery_Schools+Units_FT", FileName, True

I am running this code to import several excel files. The problem i am getting is that a few of them dont get the coloum name right . Instead of exact coloumn name,it comes as F1,F2.......etc. And also if the first record of any coloumn has no value in it , then it automatically takes them as 'text' type, whereas I want all of them as 'Number' type.

Is there is any way so that I can fix the coloumn name and their type by VB code.

Thanks in Advance

 

[CautionMP]

Yes, but ther might be an easier way. If the workbooks always have the same structure you could:[ol][li]Copy a corretly formatted workbook to a static loaction and call it [tt]ExcelData.xls[/tt] or something similar.[/li][li]Create a linked table in your database that points to this file with the correct field names and data types.[/li][li]Create an Append query (Query1) that moves the data from your new linked table into [tt]tbl_Nursery_Schools+Units_FT[/tt].[/li][li]Make the following changes to your code:

...
CD.ShowOpen
FileName = CD.FileName
[b]DoCmd.SetWarnings = False
FileCopy FileName, [i]ExcelData.xls[/i]
DoCmd.OpenQuery "[i]Query1[/i]"
DoCmd.SetWarnings = True[/b]

[/li][/ol]

Hope this helps,
CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[tar28un]

Hi there

Many thanks for the reply. I will try that

Thanks