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;Surnam e; DOB; Identity_No
In the Access 2007 table(tblCyclis ts) the same fields in this sequence are called:
First_Name; Surname;Birthda te;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.
Any suggestions please!
In Excel i have the following columns(cells)
Nickname;Surnam e; DOB; Identity_No
In the Access 2007 table(tblCyclis ts) the same fields in this sequence are called:
First_Name; Surname;Birthda te;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.
Code:
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
Comment