PDA

View Full Version : Updating Linked Data



vichisov
06-08-2012, 10:03 AM
I have a master document that draws on raw data in a couple other documents. It does so using formulas (vlookup, sumifs, etc) The master has a simple drop down menu that allows me to select a person. How do I get it to update the report based on the person I selected? I jury rigged it with the code below:

Dim WS As Worksheet
Dim QT As QueryTable
Application.ScreenUpdating = False
Workbooks.Open ThisWorkbook.Path & "\Raw Productivity_v2.xlsm"
For Each WS In ActiveWorkbook.Worksheets
For Each QT In WS.QueryTables
QT.Refresh BackgroundQuery:=False
Next
Next
ActiveWorkbook.Save
ActiveWorkbook.Close False
Application.ScreenUpdating = True
It works, but is slow when it has to do this 4 times to draw on all the data. Is there a better way of doing this? I can not seem to find a better solution but there is no way I am the only one dealing with this.

Thank you,

fredlo2010
06-08-2012, 04:16 PM
Hi,

Can you post a copy of your document? That could help us understand better what's happening. You can create a fake one.

I ran into something similar a couple of weeks ago and here is what I did.

1. I turned the automatic calculations off and then back on like this.

Dim WS As Worksheet
Dim QT As QueryTable

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Workbooks.Open ThisWorkbook.Path & "\Raw Productivity_v2.xlsm"

For Each WS In ActiveWorkbook.Worksheets
For Each QT In WS.QueryTables
QT.Refresh BackgroundQuery:=False
Next
Next

ActiveWorkbook.Save
ActiveWorkbook.Close False

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

2. I made my master document a normal range instead of a table. That also helped.

Hope it helps

vichisov
06-11-2012, 07:28 AM
Thank you for your response. See attached file. It would draw on the file attached in the next message and 4 others as described in the code. I did not see any value of posting them all. Any ideas on how to manage their relations would be appreciated.

vichisov
06-11-2012, 07:28 AM
This one.

fredlo2010
06-11-2012, 06:23 PM
Hi,

Ok I was looking at your work book.

A couple of things

1. You only turn off application updating and set calculations to manual at the beginning and reverse the change at the end of the code.

2. if you see patterns, things that you have to more than once in a macro then consider taking it aside and create a procedure that has one variable that you can modify all the time. In this case I did it with "RefreshData" the code stays always the same and what changes is the workbook name, to that I give a variable (vName in this case)

So here you have it. It not fully tested because I am missing the rest of the workbooks. I am pretty sure you get the idea.

This piece of code goes under a Module in the main workbook

Sub DataRefresh(vName As String)

Dim WS As Worksheet
Dim QT As QueryTable

With ActiveWorkbook

Workbooks.Open ThisWorkbook.Path & "\" & vName

For Each WS In .Worksheets
For Each QT In WS.QueryTables
QT.Refresh BackgroundQuery:=False
Next
Next

.Save
.Close False

End With

End Sub

This is the code under "ThisWorkbook"

Private Sub Workbook_Open()

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual


With Sheet1.DoctorList
.AddItem "Doc, A"
.AddItem "Doc, B"
End With

Call DataRefresh("Raw Productivity_v2.xlsm")
Call DataRefresh("Raw Quality Measures.xlsx")
Call DataRefresh("Raw Compliance.xlsx")


.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

End Sub


...and finally this will go in the report sheet



With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual

Call DataRefresh("Raw Productivity_v2.xlsm")
Call DataRefresh("Raw Quality Measures.xlsx")

'Workbooks.Open ThisWorkbook.Path & "\Raw Compliance.xlsx", Password:="0987" <=====I dont know what this is for
'ActiveWorkbook.Close SaveChanges:=True
Call DataRefresh("Raw Compliance.xlsx")


.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

End Sub

A final note, use indentation in your code thats gonna make it easier to read and understand later. You can indent by pressing the "tab" key. Also under VBE-->Tools-->Options make sure autoindent is checked.

Here is the copy of the workbook 8241

vichisov
06-11-2012, 08:28 PM
Thank you!

Its maddening to be able to only get the general gist of things but not have anything polished. Haven't touched VBA in 7 years but have been coding in other languages all along on a basic level.

Last question if I may: This code works, but I think there will have to be an If statement built in to keep the raw data workbooks open if they are already open and closed if they are being opened by the code in the first place. Maybe an ignore worksheet if it is already open. It is annoying that if I am working on something by changing the doc everything gets closed. Did you bother with this when you set your thing up a couple weeks ago?

Regards,

vichisov

fredlo2010
06-12-2012, 12:24 AM
Its ok. Two weeks ago I was totally lost. I am no genius now, but at least I feel I have a little more control over VBA.

Well I still need to work on my if statements skills (a hard thing to learn in my opinion) but see if this works. I am using a function from this link (http://www.exceltip.com/st/Determine_if_a_workbook_is_already_open_using_VBA_in_Microsoft_Excel/472.html)


Sub DataRefresh(vName As String)

Dim WS As Worksheet
Dim QT As QueryTable

With ActiveWorkbook
If WorkbookIsOpen(vName) = True Then
Workbooks(vName).Activate
Else
Workbooks.Open ThisWorkbook.Path & "\" & vName

For Each WS In .Worksheets
For Each QT In WS.QueryTables
QT.Refresh BackgroundQuery:=False
Next
Next

.Save
.Close False
End If
End With

Workbooks("Good Citizenship_v3.xlsm").Activate
End Sub

Private Function WorkbookIsOpen(vbname) As Boolean
' Returns TRUE if the workbook is open
Dim x As Workbook
On Error Resume Next

Set x = Workbooks(vbname)

If Err = 0 Then WorkbookIsOpen = True _
Else WorkbookIsOpen = False

End Function