Appending Excel data to an Access Table

[sagain2k]

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!

 

[bat17]

Look up TransferSpreadsheet in Access Help.

I will have to have a think about the second part I seem to recal that there is a problem opening Access like that from EXcel in that as soon as the variable goes out of scope Access closes. You may be able to get around it by declaring it as a Global though.

Edit: See http://www.mrexcel.com/board2/viewtopic.php?t=103763&highlight=access

 

[sagain2k]

Bat17...thanks much! I tried this hyperlink method you suggested:

ActiveWorkbook.FollowHyperlink Address:="C:\db1.mdb", NewWindow:=True

And it worked! I'm using a Switchboard in Access so it's a good solution for this project. However, one question:

- When I open up my Access database just via Access itself, it opens up the Access window Maximized, and with the Switchboard form box centered in the screen. When I use the above hyperlink method, it opens up the Access window minimized to a smaller size, with the Switchboard form window at the top left corner of the screen.

Any way to add a "NewWindow:=Maximized" and something else to control it's position?

Thanks much for your response...very useful!

 

[bat17]

Have a look at this link which will let you force Access to open to a full screen.

http://www.mvps.org/access/api/api0019.htm

HTH

Peter

 

[sagain2k]

Thanks for the suggestion(s)! A couple questions...in the example (e.g.):

Global Const SW_HIDE = 0
Global Const SW_SHOWNORMAL = 1
Global Const SW_SHOWMINIMIZED = 2
Global Const SW_SHOWMAXIMIZED = 3


Private Declare Function apiShowWindow Lib "user32" _
Alias "ShowWindow" (ByVal hwnd As Long, _
ByVal nCmdShow As Long) As Long

Function fSetAccessWindow(nCmdShow As Long)
'Usage Examples
'Maximize window:
' ?fSetAccessWindow(SW_SHOWMAXIMIZED)
'Minimize window:
' ?fSetAccessWindow(SW_SHOWMINIMIZED)
'Hide window:
' ?fSetAccessWindow(SW_HIDE)
'Normal window:
' ?fSetAccessWindow(SW_SHOWNORMAL)


1. When I call this function, if I just want to maximize the window do I use this command:
3fSetAccessWindow(SW_SHOWMAXIMIZED)? Do I need to put the "3" in?

2. If using the hyperlink method to open Access, do I just add this command after that one like this:

ActiveWorkbook.FollowHyperlink Address:="C:\db1.mdb", NewWindow:=True

3fSetAccessWindow(SW_SHOWMAXIMIZED)

3. Any way to have it then run a macro too? I can have it run a macro by using the other method:

Dim appAccess As Object
Set appAccess = GetObject("C:\db1.mdb")
appAccess.Visible = True
appAccess.DoCmd.RunMacro "mcrImportCourses"

Can I use the above function to maximize the window with this method too? The problem I'm having with this "GetObject" method is that if Access is already open it has an error. Is there a way to have it test if Access is open, and if so still be able to run the macro? If there was a way to run commands/macros via the hyperlink method, that would be slick.

By the way, does this maximize the Access application window or just the active form window? Also... why would the position of the form window be shifting to the top left when opening Access with these macros...is there a "position" parameter also that can be added? Lots of questions! Getting this figured out will be a very useful tool! Thanks!

 

[bat17]

This looked fun so I had a play with it

Option Explicit
   Declare Function SetForegroundWindow Lib "User32" _
     (ByVal hWnd As Long) As Long
   Declare Function IsIconic Lib "User32" _
     (ByVal hWnd As Long) As Long
   Declare Function ShowWindow Lib "User32" _
     (ByVal hWnd As Long, ByVal nCmdShow As Long) As Long
   Const SW_NORMAL = 1     'Show window in normal size
   Const SW_MINIMIZE = 2   'Show window minimized
   Const SW_MAXIMIZE = 3   'Show window maximized
   Const SW_SHOW = 9       'Show window without changing window size

   Dim objAccess As Object 'module-level declaration

   '----------------------------------------------------------------------
   'This procedure brings the instance of Microsoft Access referred to
   'as "instance" into view. The instance's window size can be SW_NORMAL,
   'SW_MINIMIZE, SW_MAXIMIZE, or SW_SHOW. If size is omitted, the window is
   'not changed (SW_SHOW). To call this function, use this syntax:
   '   ShowAccess instance:=objAccess, size:=SW_SHOW
   '----------------------------------------------------------------------

   Sub ShowAccess(instance As Object, Optional size As Variant)
     Dim hWnd As Long, temp As Long

     If IsMissing(size) Then size = SW_SHOW
     On Error Resume Next
         If Not instance.UserControl Then instance.Visible = True
         On Error GoTo 0 'turn off error handler
         hWnd = instance.hWndAccessApp
         temp = SetForegroundWindow(hWnd)
         If size = SW_SHOW Then 'keep current window size
              If IsIconic(hWnd) Then temp = ShowWindow(hWnd, SW_SHOW)
         Else
              If IsIconic(hWnd) And size = SW_MAXIMIZE Then _
                temp = ShowWindow(hWnd, SW_NORMAL)
              temp = ShowWindow(hWnd, size)
         End If
   End Sub
Sub OpenAccess()

Set objAccess = GetObject("C:\db1.mdb")
ShowAccess objAccess, SW_MAXIMIZE
objAccess.DoCmd.RunMacro "mcrImportCourses"
End Sub

I haven't played with maximize/position yet. If you just want the forms Maximized you can add docmd.maximize to the forms open events
As for position lookup Auto Center and Auto Resize in Access help

HTH

Peter