Excel 2007 - Manage update/refresh of data tables and vlookups 1

[BxWill]

Hopefully, this is a sensible request/question;

Currently have a MS Excel 2007 workbook with over 20 worksheets. On 12 of the worksheets, I have vlookup formulae from row 2 down to row 3000 and/or data tables that extract data from sql server. On 4 of the worksheets, there are pivot tables that are based on the data within the Oracle or sql server data worksheets. Also, have vba code behind 3 sheets (with dynamic range names) to automatically extract data from a Oracle database.

Currently, I access each worksheet and perform a manual refresh to update the data.

Questions:
1. Using Excel vba, is it possible and/or feasible to perform refresh and vlookup on multiple worksheets once per day and then maybe "turn the refresh and vlookups off" and then the following day, perform the refresh and vlookups and then turn off and so on and so on?

2. Can anyone provide code snippets that will assist?

Just trying to manage a increasingly tedious process that may result in a large bloated worksheet and appreciate any insight.

Current thoughts include the use of a form that will display all of the worksheets and allow me to refresh specific worksheets and display the date/time that the worksheet was last refreshed.??

Note, per Supervisor, obtaining a solution using MS Access is not an option...

 

[Gavona]

Can you switch calculation to manual?
If so then do so and have a VBA routine that:
Refreshes each table
Uses Range(.....).calculate to refresh each range of lookups


Gavin

 

[BxWill]

Maybe, initially have it where the calculation is set on worksheet change and then automatically set to manual after the first calculation for the day and tomorrow, reset to worksheet change and so on???

That's a thought...

If so, I am thinking that I will have to store the current date in the code...???

Any further thoughts?

 

[Gavona]

1. Have a look at the Ontime method

2. If you need to store the date then do so on a separate (hidden?) worksheet, in a named range.

3. As an alternative to switching off calculation, make sure that you only have one copy of each formula for each column and that to re-calculate you copy it to the rest of the rows then immeditely convert to values. I store the formula two rows above the title (field heading). You could of course store it in the first data row, or in a cell comment.

Here is my code for doing this. The essential bit is actually FormulaCopy3, the last sub.
To use standalone one:
a) opens any linked files used by the formulae (I often look up to other workbooks)
b) selects the titlecells (field headings) for the columns to be re-calculated
c) runs the formulacopy macro
To build into your routine call Formulacopy1

Option Explicit

'########################################################################## _
 #General Purpose Macro to copy formulae down to the rows below and       # _
 #then convert to values                                                  # _
 ##########################################################################

Sub FormulaCopy()
'######################################################################## _
 # Choose a value for myVerbose.  Setting to False eliminates prompts   # _
 # and is intended for use when modifying this routine for specific     # _
 # applications.                                                        # _
 # MAKE SURE LINKED WORKBOOKS ARE OPENED IF YOU CHOOSE fALSE!!!         # _
 ########################################################################
Dim myVerbose As Boolean
myVerbose = True  'Prompts will appear
'myVerbose = False 'no prompts use when modifying for specific applications
Call FormulaCopy1(True, Selection)
End Sub

Private Sub FormulaCopy1(myVerbose As Boolean, myRange As Range)
Dim MyPrompt As String, Response As String, Style As Integer
Dim c As Range

If myVerbose = False Then GoTo Label1
MyPrompt = "Have you Opened any linked workbooks??"
Style = vbOKCancel + vbCritical + vbDefaultButton2    ' Define buttons.
Response = MsgBox(MyPrompt, Style, "Copy Formulae Macro")
If Response = vbCancel Then Exit Sub

MyPrompt = "For each cell in current selection the macro copies formulae in the " & _
"second row above the selected cell to all cells below the selected cell in the CurrentRegion. " & _
"calculates the pasted cells (only) and then pastes them to values" & _
"Calculation is set to Automatic at the end of theroutine irrespective of the original state."

Style = vbOKCancel + vbCritical + vbDefaultButton2    ' Define buttons.
Response = MsgBox(MyPrompt, Style, "Copy Formulae Macro")
If Response = vbCancel Then Exit Sub

Label1:

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

For Each c In myRange
Application.StatusBar = "Applying Formulae to " + Str(c.Value)
Call FormulaCopy3(c)
Next c

MyPrompt = "Macro Finished - calculation set to automatic"
Style = vbOK + vbCritical + vbDefaultButton2   ' Define buttons.
Response = MsgBox(MyPrompt, Style, "Copy Formulae Macro")
If Response = vbCancel Then Exit Sub
Application.StatusBar = "Setting calculation to automatic"
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.StatusBar = False
End Sub



Private Sub FormulaCopy3(MyTitleCell As Range)
'This sub is used by the FormulaCopy macro _
It copies formulae from two rows above the "TitleCell" passed to it from the calling macro _
to all cells below the TitleCell to the bottom of the "CurrentRegion" _
Ensure your database follows best design practice - separated from anything else _
on the sheet by blank rows and columns.

Dim myRange As Range
Set myRange = Range(MyTitleCell.Offset(1, 0), MyTitleCell.Offset(MyTitleCell.CurrentRegion.Rows.Count - 1, 0))
MyTitleCell.Offset(-2, 0).Copy Destination:=myRange
If Application.Calculation = xlCalculationManual Then myRange.Calculate
myRange.Copy
myRange.PasteSpecial _
    Paste:=xlPasteValues, _
    Operation:=xlNone, _
    SkipBlanks:=False, _
    Transpose:=False
Application.CutCopyMode = False
End Sub

Gavin

 

[BxWill]

Thanks Gavona!

I will try the code.

Didn't realize that something like this could be done.

Regarding the need to store the data, are you implying that it is possible to maybe have a "audit trail" whereby one could store the refreshed data on separate worksheets? In other words, say if I have two hidden worksheets labeled "Refreshed 2/1/11", Refreshed 2/2/11, etc. Is this possible? Would reckon that this would lead to bloat but the benefit of a audit trail sounds quite interesting...


Would it be possible to provide a sample workbook?

 

[Gavona]

I was actually thinking more of creating a named range "LastRefreshed". Then at the end of the code that refreshes everything have the line

Range("LastRefreshed").value=now()

If your workbook is always open then follow it with the code to re-run the procedure but check with someone else here if that is your intent as procedures that call themselves (recursion) have confused me in the past.

If on the other hand the file is opened then the WorkbookOpen event could include something like:

Application.OnTime EarliestTime:=
Range("LastRefreshed").value + TimeValue("24:00:00"), _
Procedure:="my_Procedure", Schedule:=False

are you implying that it is possible to maybe have a "audit trail" whereby one could store the refreshed data on separate worksheets? In other words, say if I have two hidden worksheets labeled "Refreshed 2/1/11", Refreshed 2/2/11, etc. Is this possible? Would reckon that this would lead to bloat but the benefit of a audit trail sounds quite interesting...

Possible, yes but maybe not the way to go. How many days history would you want? If only some of the records change each day then I would have thought you would want to save just the changed records. That said you could append every record to an archive sheet with a date stamp and sort out removing those that hadn't changed as a seperate operation. How would you want to report on/interrogate the history? How often (you could just write the records to a text file to avoid bloat of your operational workbook).

Gavin