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.
Expand|Select|Wrap|Line Numbers
- 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