Auto run excel calculations in background - Help!

[SteveS]

Hi all,

I have set up an excel file (ExcelCalcs.xls) to pull data from Access via a
'Get External Data' query. The number or rows of data from Access changes
based on user input, so I have the excel sheet (Calcs1) set to automatically
replace all the data in the query with each refresh, and fill down the
adjacent formulas for all the rows. The summary results of that sheet are
calculated in a separate sheet (SumCalcs), which is a linked table within
Access. That table has a report based on the results (rptSumCalcs).

Ideally I would now like to automate this process as follows:
1) User clicks a button in access
2) Excel opens in the background (not visible to the user), and
automatically refreshes the query (Calcs1) in excel
3) This automatically updates the linked table (SumCalcs) and the excel file
is closed (again, user has never seen excel open)
4) The report (rptSumCalcs) opens to a preview pane for the user to see

Right now I have everything set-up to go, but have not tried to automate the
process yet due to some potential issues I'm still trying to figure out:

a) I have the excel file set to automatically refresh the query upon.
However, this means that every time I open it, I get a warning dialog box
asking if I want to enable automatic refresh - I need to find a way around
that dialog box so the user does not have to see it or click the button to
enable.

b) I do not know how to hide excel in the background so the user does not
see it. Also, what happens if the excel file is already open for some reason
- how do I accomodate for that in the code?

I would appreciate any help/suggestions for the "OnClick" event of the
button to make this all happen, as I'm relatively new to access (but quickly
becoming a convert from excel!) and learning all of this as I go. I have been
searching these posts but haven't seen anything yet to solve issue 'a',
though I think I'm getting closer on issue 'b'.

Thanks in advance!!!

 

[John Nurick]

Hi Steve,

Since you know your way round Excel, start by building the code as an
Excel macro (i.e. VBA procedure). Avoid using the Selection object (use
Range objects instead). Rather than having the query update
automatically as the workbook opens, initiate this from the macro.

Once it's working, port the code to Access VBA. This basically involves

1) In the Access VBA project, set a reference to the Excel object
library and insert a new module (making sure to inclde Option Explicit!)

2) In the module, a Sub procedure to launch a hidden instance of Excel
and open the workbook:

Dim oXL as Excel.Application
Dim oWBK As Excel.Workbook
...
Set oXL = CreateObject("Excel.Application")
Set oWBK = oXL.WorkBooks.Open("D:\folder\file.xls")

From here you pretty much continue your Excel VBA code with minor
changes such as
Application.Method becomes oXL.Method
ActiveWorkbook.Method becomes oWBK.Method

Since the instance of Excel will be invisible, you need to take care
when saving and closing the workbook so as not to leave anything that
may prevent Excel itself quitting.



One possible improvement (I've never tested the pros and cons) would be
to replace the bit where you get Excel to update the query (i.e. pull
the data from the database) with code that pushes the current data into
Excel from Access. The code would be something like this:

Dim rsCurrentData As DAO.Recordset

Set rsCurrentData = _
CurrentDB.OpenRecordset("NameOfQuery", dbSnapshot)
oWBK.Worksheets("Calcs1").Range("A1").CopyFromRecordset _
rsCurrentData

 

[Klatuu]

Here is an easy way to more data from Access to Excel:

Set qdf = CurrentDb.QueryDefs("qselSCCBrpt")
qdf.Parameters(0) = Me.cboResource
qdf.Parameters(1) = Me.cboPeriod
Set rstSCCB = qdf.OpenRecordset(dbOpenSnapshot, dbReadOnly)
xlSheet.Cells(intX + 3, 1).CopyFromRecordset rstSCCB

Also, to not only make the application invisible, but to also speed it up
and prevent users from doing anything that would cause a problem, I find
these 3 lines useful:

xlApp.DisplayAlerts = False 'You wont see the messages
xlApp.Interactive = False 'The user can't affect do anything to it
xlApp.ScreenUpdating = False 'Saves the time for the screen updating

You were wondering about what if excel is already open, this way of creating
the xl object works well for that.

Private Function CreateWorkbook() As Boolean

On Error Resume Next ' Defer error trapping.

Set xlApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
blnExcelWasNotRunning = True
Set xlApp = CreateObject("excel.application")
Else
DetectExcel
End If
Err.Clear ' Clear Err object in case error occurred.
'Set error trapping back on
On Error GoTo CreateWorkbook_Error
xlApp.DisplayAlerts = False
xlApp.Interactive = False
xlApp.ScreenUpdating = False
Set xlBook = xlApp.Workbooks.Add

********* Called by the code Above ***************
Put this code in a standard module.

' Declare necessary API routines:
Declare Function FindWindow Lib "user32" Alias _
"FindWindowA" (ByVal lpClassName As String, _
ByVal lpWindowName As Long) As Long

Declare Function SendMessage Lib "user32" Alias _
"SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, _
ByVal wParam As Long, _
ByVal lParam As Long) As Long

Sub DetectExcel()
' Procedure dectects a running Excel and registers it.
Const WM_USER = 1024
Dim hwnd As Long
' If Excel is running this API call returns its handle.
hwnd = FindWindow("XLMAIN", 0)
If hwnd = 0 Then ' 0 means Excel not running.
Exit Sub
Else
' Excel is running so use the SendMessage API
' function to enter it in the Running Object Table.
SendMessage hwnd, WM_USER + 18, 0, 0
End If
End Sub

**********
And last, to close it all down:

Private Function QuitXL() As Boolean

On Error GoTo QuitXL_Error

xlBook.Close
If blnExcelWasNotRunning = True Then
xlApp.Quit
Else
xlApp.DisplayAlerts = True
xlApp.Interactive = True
xlApp.ScreenUpdating = True
End If
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
QuitXL = True

QuitXL_Exit:

On Error Resume Next
Exit Function

QuitXL_Error:

QuitXL = False
MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure QuitXL of VBA Document Form_frmNonVAII", ,
conMsgHeader
GoTo QuitXL_Exit

End Function

There is one very important thing to always consider using Automation to
work in Excel from Access. You have to be very careful and very explicite in
establishing object references. If you objects are ambiguously referenced
and Access can't determine the relationship, Access will actually create an
additional instance of Excel that you don't know about. The result is that
when you Quit the application, you kill the instance you created, but the one
Access created will stay in memory.

Additionally, it is always a good idea to put you Excel object closing code
in a location in your code so that it executes even if an error occurs. This
means you should have error handling in your code to trap for errors. For
example:

cmdXl_Click_Error:

On Error Resume Next
QuitXL
MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure cmdXl_Click of VBA Document Form_frmNonVAII", ,
conMsgHeader
GoTo cmdXl_Click_Exit

End Sub

Should you end up with Excel still running, you will know it by these
symptoms. If you try to open Excel either from Access or from the desktop,
It will hang up. You may get the Excel window frame, but a spreadsheet will
not be displayed. You will need to check the task manager, but it will not
be in the Applications tab, it will be in the Processes tab. You can stop
it from there.

Now, with all that said --- Why are you going out to Excel to do your
calculation? Is it because you are comfortable with Excel? If you are
trying to make the transition, this would be a good learning opportunity.
You can do all the calculations it Access that you can in Excel.

 

[SteveS]

Hi John,

Thanks for the response. I was on vacation, so please excuse my delay in
getting back to you.

While I'd classify myself an advanced end user of excel, I'm more
self-taught and not really a programmer, so I'm not quite following
everything you suggested here. However, I'll try to tinker around with some
back-up copies of both my files and see if I can get this going...

Thanks again,
Steve

 

[Klatuu]

How do you intend to present these data to the user? In a report or a form?
Everything you defined can be done in Access; however, your assesment of the
difficulty is pretty much correct. What you want to do will take some pretty
good knowledge of VBA, queries, and reportwriting.

The field limit for both queries and tables is 255, which is also the number
of columns in an Excel Spreadsheet.

If you are more comfortable doing this in Excel, go ahead. Although it
would be easier and faster for your, it will do nothing for your Access
knowledge and experience. It will be an uphill struggle, but with help from
this site and other available resources, you can do it, and learn from it.

Either way, Good Luck

 

[SteveS]

Thank you for the input - sory for the delayed response but I was on
vacation. I'll start experimenting with this today to see if I can get it to
work.

First, however, I'd like to skip to your last point. If I can set up my
formulas in access, then I'll happily avoid transfering data back-and-forth
to excel. However, I've been reluctant to try it, due to the complexity and
repetition of the formulas (I'll have to set up over 84 calculation columns
in a query...) Let me explain.

I'm doing financial forecasting based on variable events that users input.
So, the user can enter an event that takes place six months from now, and it
will calculate the projected up-front and recurring revenue flow by month for
the length of resulting contracts (typically no longer than 5 years.) The
user also inputs a start and end date range for tracking these events - so
the months themselves (which reside in the header row) are variable. (I also
apply an Net Present Value Calculation to each event, based on when the event
occurs, but I'll save that for another posting)

So, Event A takes place on 12/1/06. Based on a number of user-entered
assumptions, the up-front revenue is recognized in 5/07, with the recurring
revenue coming in for the remaining months in the contract. Event B takes
place on 2/15/07, and based on assumptions all the revenue is recognized up
front in 7/07. Event C takes place 10/15/06 with only recurring revenue
starting 2/07. This all results in something like this:

EVENT NAME 1/07 2/07 3/07 4/07 5/07 6/07 7/07 8/07 ...
Event A $0 $0 $0 $0 $5000 $200 $200 $200 ...
Event B $0 $0 $0 $0 $0 $0 $6500 $0
...
Event C $0 $50 $50 $50 $50 $50 $50 $50
....
....

The number of events are limitless, and I need to summarize the results by
month.

The main problem in access is that I don't know how to account for the
variable months, which are calculated in the header row in the excel file,
based on the start of the date range entered by the user. Those months in the
header row are used to determine if what columns get the up-front or
recurring revenues.

The other potential issue (that I have not explored yet) is the number of
columns in an Access query. I already have around 30 in my main query (that
gets imported by excel), and I would have to add 84 (for 7 years) and then I
would have to add another 60 columns for my NPV calculation. Will I run into
issues with that?

Does all of that make any sense the way I explained it? Am I complicating
all this too much? Again, if I can use Access instead of Excel I gladly will,
but I'm still too new to Access to know if all these issues can be solved.

Thanks again for your help!!!

 

[SteveS]

Thanks for the input.

- the data will be presented in an unbound report containing a mix of other
reports and charts that will be pulled from this data and other existing
queries as well.
- the formulas in excel are set up already, but I'm looking at those to see
if I can recreate the same results in an existing query that tracks the
'active' events for the report. I've put the data range start into a column
for all events, and I think if I use the DateSerial function in the code I
might be able to get it to recognize the up front and recurring revenues in
the right months. It's just going to be a lot of repetition to duplicate the
forumlas across 84 columns with only minor adjustments, and the months will
be set up as "Month1, Month2, Month3, etc.
- What I haven't thought about yet is how I can align the resulting
calculations with the right date in the charts (so the correct date appears
with the correct data)... but guess I'll have to tackle it (with the help
from this forum) when I get to it.

 

[Klatuu]

If you are going to do this in a report, put as much of the work in the
report as you can - specifically the calculations and the headers that show
the month/year. Try to keep the queries as stupid as possible. It is easier
to put together and it will run light years faster.

 

[SteveS]

That's an interesting concept, and one I haven't come across or considered
yet. So, I pull my base information from the query that I need to run the
calculations, then I set up the calculations in control boxes within the
report itself. I've only set up some fairly straight-forward reports so far,
so have a few more questions if you don't mind...

- I'm only going to display the summary data in the report, probably rolled
up by year. So, I could create the calculations in the detail for each event
across all months but make them not visible, and then only display the
calculations which total them by year in the group footer?

- I need to also calculate the monthly totals as the basis for several
charts. If I run the calculations in the report instead of the query would I
be able to do that somehow directly from the report calculations?

This is great help - thanks again!

 

[Klatuu]

That's an interesting concept, and one I haven't come across or considered
yet. So, I pull my base information from the query that I need to run the
calculations, then I set up the calculations in control boxes within the
report itself. I've only set up some fairly straight-forward reports so far,
so have a few more questions if you don't mind...

- I'm only going to display the summary data in the report, probably rolled
up by year. So, I could create the calculations in the detail for each event
across all months but make them not visible, and then only display the
calculations which total them by year in the group footer?

That is correct. You are on to the concept. Note that in some cases, the
calculations may be complex enought they require user defined function. You
can do this in a report.

- I need to also calculate the monthly totals as the basis for several
charts. If I run the calculations in the report instead of the query would I
be able to do that somehow directly from the report calculations?

It depends. I am not really familiar with using Access Charts, but the
concept would be the same. You would pull the base data and do the
calculations to create the data for the charts. Since you may be doing the
calculations in more than one place, the idea would be to make your
calculations in functions and put the functions in a standard module. That
way you can call the functions from multiple locatoins.

 

[SteveS]

"Since you may be doing the calculations in more than one place, the idea
would be to make your calculations in functions and put the functions in a
standard module. That way you can call the functions from multiple
locations."

Can you expand on this sentence a bit - ideally I would like to run the
calculations one time and then use that data in multiple ways, and it sounds
like that is what you are getting at. As I'm still relatively new to Access,
I'm not sure what you mean by "make your calculations in functions and put
the functions in a standard module." How do I do that?

P.S. I've found a way to simplify my formulas so they will flow better in
Access - now it's just a matter of replicating across all the months. This is
the direction I'm going in now rather than connecting to excel...

 

[Klatuu]

A standard module is one of those you will see when you select Modules from
the database window. It is the correct place to put VBA code that is not
specific to a form or report. Routines in standard modules have the ability
to be visible anywhere in the application if you declare them as Public.

Functions come in a couple of flavors. Intrinsic functions are those that
are part of Access and User Defined Functions are those written by a
developer for use with an application. Read up on functions. They are a
very valuable part of VBA. Here is a very simple example. This example
function divides the first argument by the second argument, adds 10% to the
result and passes it back to the calling code:

Public Function UselessMath(sngFirst As Single, sngSecond As Single) As Single
Dim sngResult As Single

sngResult = sngFirst / sngSecond
sngResult = sngResult * 1.1
UselessMath = sngResult

End Function