While Excel Sheet Is Open

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

What I aim to have is, on the double click of my control, the appropriate
excel sheet opens (done this bit), the user enters the data, then the final
total (not always in the same cell, but always at the bottom of the same
column) is passed into the control I originally dbl clicked.

The only way I can think of to achieve this is by stopping the users from
soing anything in my db app until the excel sheet has been closed. In the
close event of excel, copy the value to the clipboard. When the sheet is
closed, my app can continue, its first step being to paste the value from the
clipboard. Any ideas how I can stop my code whilst the excel sheet is open?

Thanks,

Dave
 
Hi Dave,

You can't stop and re-start a VBA procedure like that: you need either
to have a loop that repeats until the other thing has finished
happening, or use two separate procedures.

In fact no elegant way of doing what you want; the only ways I can think
of doing it would need serious VBA skills. E.g., in pseudocode:

Open Excel and workbook, activate the relevant sheet
Set Excel.Application.UserControl = True
Make the workbook visible
Release all objects used in the above to hand over
to the user
Do
Wait a second or two
Loop if there's an instance of Excel running whose
Workbooks collection includes one with the right name

'at this point the user must have closed the workbook
Re-open Excel and the workbook
Get value from last cell in relevant column of worksheet

but it would be difficult or impossible to prevent the user quitting
Windows part way through.

Or with two procedures you could have the procedure terminate after
opening the workbook, and use Excel code in the workbook's Close event,
to poke the value into Access and launch the second procedure. This
would be chancy unless it was always the same workbook, or always
created from the same template.

Instead, can you have the user enter the data in Access? If necessary
you could write VBA code to update the spreadsheet.
 
Thanks for your input.

I would love to have the user enter the data in Access, but I can't get
Access to handle the input and display as well as Excel.

In Excel, I have a VBA procedure setup such that every two values entered in
a certain column, get multiplied and stored in the column to the right. These
multiplied values then get summed at the bottom. This visual representation
is very important to the end-users, since it mimics the way they work by
hand, and allows very fast data input. I need data to be saved and formatted
exactly as it was entered, but the sum at the bottom needs to be passed into
Access.

I've just thought of another way of achieving the same thing though. Since
the value calculated by Excel is not always the value you end up storing in
Access (for a variety of reasons), I only need to be able to ensure the
relevant Excel file is opened (easy), then on a button click, a VBA
procedure passes the sum into the correct record in Access, then save's the
sheet, gives focus to Access, and closes.

New question, how do you pass values between Excel and Access?

Thanks,

Dave
 
On Tue, 1 Aug 2006 00:42:01 -0700, David M C

[snip]
I've just thought of another way of achieving the same thing though. Since
the value calculated by Excel is not always the value you end up storing in
Access (for a variety of reasons), I only need to be able to ensure the
relevant Excel file is opened (easy), then on a button click, a VBA
procedure passes the sum into the correct record in Access, then save's the
sheet, gives focus to Access, and closes.

New question, how do you pass values between Excel and Access?
Click to expand...

Using Automation - though what you automate depends on what you want to
do. The two demo functions below work in Excel VBA.

If you want to add a record to a table, use the first,
ExecuteDAOQuery(): it executes a SQL statement against an Access
database: the idea is to write code that builds a single-record INSERT
INTO statement (append query) that contains the value(s) from the
worksheet. The syntax for the query is like this:

INSERT INTO ABC (Field1, Field2) VALUES ('Hello', 1234.5);

The second function, PokeItemIntoAccessForm(), puts a value into a
control on an Access form - assuming the database and form are open.


'---------------------------------------------------------
Function ExecuteDAOQuery(SQL As String, DBName As String) As Long

Dim oJet As Object 'DAO.DBEngine.36
Dim oDB As Object 'DAO.Database

ExecuteDAOQuery = 0
'On Error GoTo Err_Handler:

'Open the database
Set oJet = CreateObject("DAO.DBEngine.36")
Set oDB = oJet.OpenDatabase(DBName)

'Execute the query/ies
oDB.Execute SQL, 128 'dbFailOnError
oDB.Close
Exit Function

Err_Handler:
ExecuteDAOQuery = Err.Number

End Function

'------------------------------------------------------

Function PokeItemIntoAccessForm( _
DBName As String, _
FrmName As String, _
CtlName As String, _
ItemValue As Variant) As Long

'Demo function to poke value into a control on an Access form.
'Assumes that the database and the form are open.
'Returns 0 on success, an error number on failure.

Dim DB As Object 'Access.Application

PokeItemIntoAccessForm = 0

'On Error GoTo Err_Handler:

Set DB = GetObject(DBName)
DB.Forms(FrmName).Controls(CtlName).Value = ItemValue

Exit Function

Err_Handler:
PokeItemIntoAccessForm = Err.Number

End Function
 
Brilliant. Exactly what I wanted.

Thanks,

Dave

John Nurick said:
On Tue, 1 Aug 2006 00:42:01 -0700, David M C

[snip]
I've just thought of another way of achieving the same thing though. Since
the value calculated by Excel is not always the value you end up storing in
Access (for a variety of reasons), I only need to be able to ensure the
relevant Excel file is opened (easy), then on a button click, a VBA
procedure passes the sum into the correct record in Access, then save's the
sheet, gives focus to Access, and closes.

New question, how do you pass values between Excel and Access?
Click to expand...

Using Automation - though what you automate depends on what you want to
do. The two demo functions below work in Excel VBA.

If you want to add a record to a table, use the first,
ExecuteDAOQuery(): it executes a SQL statement against an Access
database: the idea is to write code that builds a single-record INSERT
INTO statement (append query) that contains the value(s) from the
worksheet. The syntax for the query is like this:

INSERT INTO ABC (Field1, Field2) VALUES ('Hello', 1234.5);

The second function, PokeItemIntoAccessForm(), puts a value into a
control on an Access form - assuming the database and form are open.


'---------------------------------------------------------
Function ExecuteDAOQuery(SQL As String, DBName As String) As Long

Dim oJet As Object 'DAO.DBEngine.36
Dim oDB As Object 'DAO.Database

ExecuteDAOQuery = 0
'On Error GoTo Err_Handler:

'Open the database
Set oJet = CreateObject("DAO.DBEngine.36")
Set oDB = oJet.OpenDatabase(DBName)

'Execute the query/ies
oDB.Execute SQL, 128 'dbFailOnError
oDB.Close
Exit Function

Err_Handler:
ExecuteDAOQuery = Err.Number

End Function

'------------------------------------------------------

Function PokeItemIntoAccessForm( _
DBName As String, _
FrmName As String, _
CtlName As String, _
ItemValue As Variant) As Long

'Demo function to poke value into a control on an Access form.
'Assumes that the database and the form are open.
'Returns 0 on success, an error number on failure.

Dim DB As Object 'Access.Application

PokeItemIntoAccessForm = 0

'On Error GoTo Err_Handler:

Set DB = GetObject(DBName)
DB.Forms(FrmName).Controls(CtlName).Value = ItemValue

Exit Function

Err_Handler:
PokeItemIntoAccessForm = Err.Number

End Function
Click to expand...
 
Back
Top