export data from Excel to MS Access (ADO) using VBA
Not just 100% sure exactly what you are looking for but here is a fairly
generic procedure to write to an Access Database. Private Const m_cDBLocation As String = "C:\Stuff.mdb" Private Const m_cLogFile As String = "tblLogFile" Public Sub LogIn() Dim cnt As New ADODB.Connection Dim rst As New ADODB.Recordset On Error GoTo ErrorHandler 'Open connection to the database cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & m_cDBLocation & ";" rst.Open m_cLogFile, cnt, adOpenKeyset, adLockOptimistic, adCmdTableDirect rst.AddNew rst.Fields("User Name") = "Bob" rst.Fields("Logged") = "In" rst.Fields("Time") = Now() rst.Update ' Close ADO objects rst.Close cnt.Close Set rst = Nothing Set cnt = Nothing End If Exit Sub ErrorHandler: 'Add error handling End Sub HTH "Peter Brom" wrote: I'm looking for an example VBA Code to transfer data from Excel to MS Access 2000. The issue is that the spreadsheet contains more then 100 hundred named cells which needs to be transferred to the corresponding Field Name in MS Access 2000. I'm not a beginner programmer but i don't want to code all the separate named cells into MS Access so i'm looking for some smart code to do this. Does anyone know wehere i can find some example VBA code to accomplish this. Thank you in advance Peter Brom |
export data from Excel to MS Access (ADO) using VBA
Have you tried using the Import Spreadsheet Wizard in Access? Depending on
how your data is structured in Excel, you may be able to import it without programming at all. "Peter Brom" wrote: I'm looking for an example VBA Code to transfer data from Excel to MS Access 2000. The issue is that the spreadsheet contains more then 100 hundred named cells which needs to be transferred to the corresponding Field Name in MS Access 2000. I'm not a beginner programmer but i don't want to code all the separate named cells into MS Access so i'm looking for some smart code to do this. Does anyone know wehere i can find some example VBA code to accomplish this. Thank you in advance Peter Brom |
All times are GMT +1. The time now is 11:42 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com