Switching between Access and Excel using code

[garry.oxnard]

Can anyone help me to solve a problem which involves switching from
Access to Excel (then back to Access) programatically please?

I have an Excel template which, on open, also opens an Access database
- containing a list of addresses.

Sequence of events is =
(1) Excel template opens in its default XXX.xls filename.
(2) Code runs to save the spreadsheet as XXX.xls.
(3) User clicks a button to open an Access database containing an
address list.
(4) When Access opens, a form generates a unique number and the OnOpen
event fires the unique reference number to a cell in the XXX.xls
spreadsheet.
(5) [[[[User has to click Excel on the taskbar to restore the
spreadsheet.]]]]
(6) A message box with an OK button opens in the spreadsheet and when
user clicks the button, code runs to save the spreadsheet using the
unique ref as a filename (e.g 3478.xls).
(7) [[[[User must then click Access on the taskbar to restore the
database. He can then choose an address and click the ok button. This
fires the address into the now saved spreadsheet (e.g 3478.xls,]]]]]
(8) Access closes and the spreadsheet remains open.

Note - the bits above in square brackets are the bits I can't make work
(ie automating the movement back and forth between the applications).

Although this seems convoluted, the only way I can fire data from
Access to Excel is by saving the spreadsheet in a default name first -
so that Access has a path to which it can then fire the ref number
(otherwise it crashes). I need the user to switch to Excel in order to
make it active, then re-save the sheet using the ref number as a file
name. The user must then go back to Access to choose the address.

Problem is that I can't make the code work to switch the user
automatically from Access to Excel, then back again. The rest of the
code works ok if I click the applications on the taskbar manually. My
users will not know to do this.

I found a posting on Google from Ed Wagner - which sounds as if it will
do exactly the job I need (post now closed) but the code is beyond me.
I include the block below in the hope that someone can give me a bit of
help. I really need to know where to place the code in order to make it
work. If it needs to be called - how do I do this please? If bits are
missing (e,g End Function) I'd much appreciate it if you could let me
know.

Here is the relevant part of Ed's posting (I haven't missed any of his
code out - if there are bits missing he missed them out): Many thanks
for your possible help.

Garry.

"As far as switching between apps goes, it is necessary to have Windows
do this
for you via the API. Windows keeps track of open windows by assigning
each one
a handle. This is a long integer that uniquely identifies the window.
The
following declaration and statement will obtain the handle for a
specific class
of window":
Private Declare Function FindWindow Lib "USER32" _
Alias "FindWindowA" (ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
lngHwnd = FindWindow("MSACCESS", vbNullString) 'for finding Access
or
lngHwnd = FindWindow("XLMAIN", vbNullString) 'for finding Excel
"The variable lngHwnd will then be assigned the handle to the window.
If the
specified window is not found, lngHwnd will be given a value of zero.
The
following declaration and statement will then set the focus to the
specified
window: "
Private Declare Sub SetFocus Lib "USER32" _
(byval hwnd as long)
SetFocus lngHwnd

[Terry Kreft]

This whole concept is appalling.

Use a userform in excel with a listbox, fill the list programmatically from
the Access database and then call the userform from your excel spreadsheet.

I don't code in Excel and it took me about 10 minutes to knock this up and
it just works.

In Excel
-----------
Userform (UserForm1) with a listbox (ListBox1) and a command button
(CommandButton1) on it.

Code
Option Explicit

Private Sub CommandButton1_Click()
Application.ActiveWorkbook.Sheets(1).Cells(1) = Me.ListBox1
End Sub

Private Sub UserForm_Initialize()
Call FillList
End Sub

Private Sub FillList()
Dim loCon As ADODB.Connection
Dim rs As ADODB.Recordset

' don't forget to to double up any double quotes in the connection string
' you can get the connection string for your db easily with
' ?currentproject.Connection in the debug window in Access

Const CONN_STRING = "<put a valid connection string here>"
Const SQL_BASE = "<Put a valid select statement here>"

Set loCon = New ADODB.Connection
With loCon
.ConnectionString = CONN_STRING
.Open
Set rs = .Execute(SQL_BASE)
End With

With rs
Do Until .EOF
Me.ListBox1.AddItem .Fields(0)
.MoveNext
Loop
.Close
End With
Set rs = Nothing
loCon.Close
Set loCon = Nothing
End Sub

Module with the following
------------------------------
Sub Macro1()
UserForm1.Show 1
End Sub

Put a button in the worksheet or on a toolbar and select Macro1 as the code
to run behind it, that's it sorted except for some titivating up.
--

Terry Kreft
<ga**********@ntlworld.comwrote in message
news:11**********************@b28g2000cwb.googlegr oups.com...

Can anyone help me to solve a problem which involves switching from
Access to Excel (then back to Access) programatically please?

I have an Excel template which, on open, also opens an Access database
- containing a list of addresses.

Sequence of events is =
(1) Excel template opens in its default XXX.xls filename.
(2) Code runs to save the spreadsheet as XXX.xls.
(3) User clicks a button to open an Access database containing an
address list.
(4) When Access opens, a form generates a unique number and the OnOpen
event fires the unique reference number to a cell in the XXX.xls
spreadsheet.
(5) [[[[User has to click Excel on the taskbar to restore the
spreadsheet.]]]]
(6) A message box with an OK button opens in the spreadsheet and when
user clicks the button, code runs to save the spreadsheet using the
unique ref as a filename (e.g 3478.xls).
(7) [[[[User must then click Access on the taskbar to restore the
database. He can then choose an address and click the ok button. This
fires the address into the now saved spreadsheet (e.g 3478.xls,]]]]]
(8) Access closes and the spreadsheet remains open.

Note - the bits above in square brackets are the bits I can't make work
(ie automating the movement back and forth between the applications).

Although this seems convoluted, the only way I can fire data from
Access to Excel is by saving the spreadsheet in a default name first -
so that Access has a path to which it can then fire the ref number
(otherwise it crashes). I need the user to switch to Excel in order to
make it active, then re-save the sheet using the ref number as a file
name. The user must then go back to Access to choose the address.

Problem is that I can't make the code work to switch the user
automatically from Access to Excel, then back again. The rest of the
code works ok if I click the applications on the taskbar manually. My
users will not know to do this.

I found a posting on Google from Ed Wagner - which sounds as if it will
do exactly the job I need (post now closed) but the code is beyond me.
I include the block below in the hope that someone can give me a bit of
help. I really need to know where to place the code in order to make it
work. If it needs to be called - how do I do this please? If bits are
missing (e,g End Function) I'd much appreciate it if you could let me
know.

Here is the relevant part of Ed's posting (I haven't missed any of his
code out - if there are bits missing he missed them out): Many thanks
for your possible help.

Garry.

"As far as switching between apps goes, it is necessary to have Windows
do this
for you via the API. Windows keeps track of open windows by assigning
each one
a handle. This is a long integer that uniquely identifies the window.
The
following declaration and statement will obtain the handle for a
specific class
of window":
Private Declare Function FindWindow Lib "USER32" _
Alias "FindWindowA" (ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
lngHwnd = FindWindow("MSACCESS", vbNullString) 'for finding Access
or
lngHwnd = FindWindow("XLMAIN", vbNullString) 'for finding Excel
"The variable lngHwnd will then be assigned the handle to the window.
If the
specified window is not found, lngHwnd will be given a value of zero.
The
following declaration and statement will then set the focus to the
specified
window: "
Private Declare Sub SetFocus Lib "USER32" _
(byval hwnd as long)
SetFocus lngHwnd