473,714 Members | 2,599 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Switching between Access and Excel using code

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 "FindWindow A" (ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
lngHwnd = FindWindow("MSA CCESS", vbNullString) 'for finding Access
or
lngHwnd = FindWindow("XLM AIN", 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

Aug 13 '06 #1
1 5738

This whole concept is appalling.

Use a userform in excel with a listbox, fill the list programmaticall y 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.Act iveWorkbook.She ets(1).Cells(1) = Me.ListBox1
End Sub

Private Sub UserForm_Initia lize()
Call FillList
End Sub

Private Sub FillList()
Dim loCon As ADODB.Connectio n
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.Connectio n
With loCon
.ConnectionStri ng = CONN_STRING
.Open
Set rs = .Execute(SQL_BA SE)
End With

With rs
Do Until .EOF
Me.ListBox1.Add Item .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**********@n tlworld.comwrot e in message
news:11******** **************@ b28g2000cwb.goo glegroups.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 "FindWindow A" (ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
lngHwnd = FindWindow("MSA CCESS", vbNullString) 'for finding Access
or
lngHwnd = FindWindow("XLM AIN", 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

Aug 14 '06 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

13
3320
by: BigDaDDY | last post by:
Um yeah....In case you haven't figured it out, Microsoft sucks. I'm going to be kicked back in my chair eating popcorn and watching football 10 years from now, while all you clowns are scrambling to rewrite all your code because Microsoft upgraded all their crap and nothing you wrote 10 years earlier works. It doesn't take a rocket scientist to figure out that Microsoft is unreliable. Try opening an Excel 95 spreadsheet you wrote in...
6
18846
by: Paul | last post by:
I was wondering if anyone has had an issue where using vba code to read an excel file and import the data into an access table some records are not imported from the excel file. It seems looking at the data in the excel file that if the first character in the excel file cell is numeric it will read and write only numeric values only. If I sort the coloumn in the excel file and the first character in the cell read is alphanumeric then only...
0
4197
by: Mike Knight | last post by:
(I've also posted this problem on microsoft.public.excel.programming) I have a MS Access 2003 Database named "AS400 Fields.mdb". This database contains links to tables on an AS400. In MS Excel 2003, I have VBA code that creates and executes queries using the Access database, and returns the results to an Excel sheet. The first time the query is executed, results are returned to Excel in usually less than 10 seconds. However, if the...
0
2838
by: Jack | last post by:
Windows 2K Pro Access/Excel 2003 Hi there, I have a large number of password protected Excel Workbooks. The files are protected by code that runs when the workbook opens. The code asks the user to key in a password into an input box, the password is compared to a a partciular cell on a hidden (xlVeryHidden) sheet and if it matches the Workbooks opens. I have not used the usual Excel password protection as I need to provide for several...
37
5232
by: jasmith | last post by:
How will Access fair in a year? Two years? .... The new version of Access seems to service non programmers as a wizard interface to quickly create databases via a fancy wizard. Furthermore, why would you even continue to use Access as a backend when you have a much superior option in SQL express? What about as a future front-end development tool? Let's get serious. Microsoft continues to publish numerous articles and videos on how you...
7
7068
by: ddecoste | last post by:
I have a need to add a visual representation to some data in Access. I need to draw a matix of squares inside another square. I have all the data that I need in a record in Access. The data changes according to what the user inputs. I know that Access does not allow you to draw on forms so I decided to put the output in an Excel file for the user but I am having trouble drawing the rectange from Access. Here is my simple code so far: ...
0
18787
NeoPa
by: NeoPa | last post by:
Many of us have noticed that there are some very useful functions available to you when using Excel, but these same functions are not available, as standard, in Access. A particular issue I had recently (and I know others have come across this recently too) is rounding up and down. I know the Excel ROUNDUP() function rounds away from zero rather than upwards specifically, but is still useful in most circumstances. To allow an Access...
5
15133
ADezii
by: ADezii | last post by:
Periodically, the same or similar question appears in our Access Forum: How can I use Excel Functions within Access? For this reason, I decided to make this Subject TheScripts Tip of the Week. In order to demonstrate the use of Excel Functions within the context of Access, I performed the following steps in sequence: Created a Public Function called fStripNonPrintableCharacters() which will encapsulate the logic for executing the Excel...
16
5182
by: Phil Stanton | last post by:
I have a form with a button which is supposed to open an Excel file (With lots of Macros /VBA) in it. The Excel file gets it's data from the Access program Here is the code Private Sub Storage_Click() On Error GoTo Err_Storage_Click
0
9177
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9077
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9021
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7954
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
4465
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4727
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3159
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2524
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2113
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.