Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
export data from Excel to MS Access (ADO) using VBA
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Export from Access to Excel | Excel Discussion (Misc queries) | |||
How can I Export data from to Access to an Excel Template | Excel Discussion (Misc queries) | |||
Export memo field in Access to Excel - data gets cut off | Excel Discussion (Misc queries) | |||
Export Excel Data to Access With a Macro!!! | Excel Discussion (Misc queries) | |||
Export Excel data to Access Security problem | Excel Programming |