Import Excel cells to a table but field names are different in both

[neelsfer]

Is there a way to import and "hard code" data from Excel where the cell(Excel column) and Access field names are different for the same data.

In Excel i have the following columns(cells)
Nickname;Surname; DOB; Identity_No
In the Access 2007 table(tblCyclists) the same fields in this sequence are called:
First_Name; Surname;Birthdate;IdNo.

I want to import the data from the "sheet 1" in the Excel spreadsheet (Cyclist.xls, into an Access table called tblCyclists. (the Excel column names will always stay the same and can be used as such in VBA)
I use this code but the field names must be the same then.

Dim strPath As String
 
    With Me
         strPath = Left(.lblFile.Caption, InStrRev(.lblFile.Caption, "\"))
        strPath = FSBrowse(strStart:=strPath, _
                           lngType:=msoFileDialogFilePicker, _
                           strPattern:="MS Excel,*.xls")
If strPath > "" Then
             .lblFile.Caption = strPath
             MsgBox "The data hase been successfully imported ", vbInformation, "Data imported"
           Call DoCmd.SetWarnings(False)
 
              Call DoCmd.TransferSpreadsheet(acImport, acSpreadsheetTypeExcel9, "tblCyclist", strPath, True, "")
 
 
 
            Call DoCmd.SetWarnings(True)
 
End If
     End With

Any suggestions please!

[zmbd]

There are a lot of ways to handle getting the data from excel into access. The biggest issue discussed when this comes up is not so much the fetching of the data in as much as it is the validation of the imported data against datatype and so forth.

So if you'll stipulate that there are some intrinsic hazards with data validation:

Transferworksheet method. Use this method to bring the data into a temporary table and then use and append
(http://bytes.com/topic/access/answer...te-access-info) see my post ... #4? I've also made a dozen posts going over this method in various other threads.


Another method (and I've also used this method; however, I now use the transfer worksheet more often):

1) Using the external data, create a linked table to the excel
2) Create an append query

So for example (I'm using really generic names here):

Excel workbook "Book1.xls" in "My documents"

Sheet 1, starting in Row 1 Column A
zt1    zt2    zt3
1    a    app
2    b    bck
3    c    cmd
(...)

Create a link to this table and the sheet (I just used the External data ribbon)

Say in our "Database1" we have "Table1" with:
[ID] (auto number - pk)
[boo] (just a text field)
[money] (just another text field)

Now create your append query

INSERT INTO Table1 ( ID, boo, [money] )
   SELECT linked_Sheet1.zt1 
          ,linked_Sheet1.zt2 
          ,linked_Sheet1.zt3
   FROM linked_Sheet1;
 

Run your query.

So long as "zt1" column data is numeric and doesn't repeat you can append/insert to your heart’s content.
You should be able to create the same link and append query within a VBA.
Using either method, you can always place constraints within the append SQL.

[Seth Schrock]

Try setting the Has Field Names argument to False and set your range to not include the top row of data (which is where I assume your field names are located).

Edit: Cross posted with Z. Try his first as he has more experience with this sort of thing.

[neelsfer]

I think the way to go as mentioned above, is to import the data to a "temp table",change some of the field types (txt to numbers as required - RaceNo1: IIf([RaceNo] Like "#*",Val([RaceNo]),[RaceNo])) in a query and then append it into the main table.