I posted this in the Excel forum just in case....
If I have an Excel worksheet of data that matches the fields (columns) in an Access table, what's the best VBA code that lets you append the entire Excel range of data to the end of the matching Access table? I'm sure it involves defining a "recordset"
I'll have to be updating/appending Excel worksheet data to matching Access tables quite a bit and would like to find the best method.
Another question related: do you know the VBA code that can switch from Excel to the Access application and then activate a macro (and/or activate a "switchboard" form or some other form)?
I found out how to swtich from Excel to Word and back using code such as these examples:
Dim oWrd As Object
Set oWrd = CreateObject("Word.Application") 'Opens Word occurrence
oWrd.Visible = True 'makes Word visible
oWrd.Documents.Add 'adds a blank new Word doc...works
' This works to open the word document
oWrd.Documents.Open filename:="D:\Data\test.doc"
oWrd.Application.Run MacroName:="testmacro" runs the Word macro
AppActivate "Microsoft Excel" 'Activates (switches) to the open Excel app
I still need to figure out how best to test if an existing application is already
running, or a different method that won't open another copy if it's already running
I wasn't able to find similar commands to open/run Access...
I'll be creating Excel macros that automatically append a worksheets data to the Access data table, and then have it switch the user to an Access form or switchboard. Would like to be able to switch back and forth via macros as needed.
Thanks for any suggestions!
If I have an Excel worksheet of data that matches the fields (columns) in an Access table, what's the best VBA code that lets you append the entire Excel range of data to the end of the matching Access table? I'm sure it involves defining a "recordset"
I'll have to be updating/appending Excel worksheet data to matching Access tables quite a bit and would like to find the best method.
Another question related: do you know the VBA code that can switch from Excel to the Access application and then activate a macro (and/or activate a "switchboard" form or some other form)?
I found out how to swtich from Excel to Word and back using code such as these examples:
Dim oWrd As Object
Set oWrd = CreateObject("Word.Application") 'Opens Word occurrence
oWrd.Visible = True 'makes Word visible
oWrd.Documents.Add 'adds a blank new Word doc...works
' This works to open the word document
oWrd.Documents.Open filename:="D:\Data\test.doc"
oWrd.Application.Run MacroName:="testmacro" runs the Word macro
AppActivate "Microsoft Excel" 'Activates (switches) to the open Excel app
I still need to figure out how best to test if an existing application is already
running, or a different method that won't open another copy if it's already running
I wasn't able to find similar commands to open/run Access...
I'll be creating Excel macros that automatically append a worksheets data to the Access data table, and then have it switch the user to an Access form or switchboard. Would like to be able to switch back and forth via macros as needed.
Thanks for any suggestions!