I'll explain what I'm trying to accomplish and possibly somebody out there can give me some guidance on where to start. I have a VB front end that pulls data from an Access 2000 database. Periodically users are supplied with a soft copy of an Excel spreadsheet with data that needs to be update to the Access database. The users would like the ability to update the database through the VB client. I know in Access I can "get external data" and import an Excel spreadsheet, but I'm not sure how to do this without the user having to go into Access. I'm still learning with this, so any advice would be greatly appreciated. Thank you in advance.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Excel into Access import using VB
- Thread starter kennajo
- Start date
Hi kennajo
i assume that the excel files will always contain data in a particular format and always the first row is the record header and i also assume that you know how to use ADO Objects.
To open a excel file use a ADO Connection object and set its Provider as "Microsoft.Jet.OLEDB.4.0" and set Data Source property to the excel file path.
since Jet Providers can open only Access databases set another property "Extended Properties" to "Excel 8.0"
To open the worksheet, use recordset's Open method and specify worksheet name say Sheet1 as [Sheet1$]. This is required because ADO cannot understand $ sign which will be returned along with sheet names
Dim cnExcelSource As New Connection
Dim rsDataSheet As New Recordset
Private Sub Form_Load()
cnExcelSource.Provider = "Microsoft.Jet.OLEDB.4.0"
cnExcelSource.ConnectionString = "Data Source = d:\Customer.xls; Extended Properties=Excel 8.0"
cnExcelSource.Open
rsDataSheet.Open "select * from [Sheet1$]", cnExcelSource, adOpenStatic, adLockOptimistic
End Sub
good luck
i assume that the excel files will always contain data in a particular format and always the first row is the record header and i also assume that you know how to use ADO Objects.
To open a excel file use a ADO Connection object and set its Provider as "Microsoft.Jet.OLEDB.4.0" and set Data Source property to the excel file path.
since Jet Providers can open only Access databases set another property "Extended Properties" to "Excel 8.0"
To open the worksheet, use recordset's Open method and specify worksheet name say Sheet1 as [Sheet1$]. This is required because ADO cannot understand $ sign which will be returned along with sheet names
Dim cnExcelSource As New Connection
Dim rsDataSheet As New Recordset
Private Sub Form_Load()
cnExcelSource.Provider = "Microsoft.Jet.OLEDB.4.0"
cnExcelSource.ConnectionString = "Data Source = d:\Customer.xls; Extended Properties=Excel 8.0"
cnExcelSource.Open
rsDataSheet.Open "select * from [Sheet1$]", cnExcelSource, adOpenStatic, adLockOptimistic
End Sub
good luck
- Status
Similar threads
