Linked Workbooks, Extreme Lag/Processing Time

JSpectrum · Jul 11th 2011

Hello all,

I have been working on this linked project database through Excel and was curious if a more efficient formula could be created. My current formlua is below...

=IF($A5>0,INDEX('C:\[Example.xlsm]Sheet2'!$A5:$P5,
MATCH($A5, 'C:\[Example.xlsm]Sheet2'!$A5:$A$200000, 2),0), "")

It essentially takes the data, cell by cell, from one workbook to another. However, having thousands of rows, with range A-P having this formula in each cell, it takes over 4 minutes to process.

Any help would be appreciated! Thank you.

JSpectrum · Jul 12th 2011

Re: Linked Workbooks, Extreme Lag/Processing Time

Quote from JSpectrum;564351
Hello all,

I have been working on this linked project database through Excel and was curious if a more efficient formula could be created. My current formlua is below...
Code
=IF($A5>0,INDEX('C:\[Example.xlsm]Sheet2'!$A5:$P5,
       MATCH($A5, 'C:\[Example.xlsm]Sheet2'!$A5:$A$200000, 2),0), "")
It essentially takes the data, cell by cell, from one workbook to another. However, having thousands of rows, with range A-P having this formula in each cell, it takes over 4 minutes to process.

Any help would be appreciated! Thank you.
Display More

Bump. ADDED BY ADMIN;

See Excel best practices

JSpectrum · Jul 13th 2011

Re: Linked Workbooks, Extreme Lag/Processing Time

I'm curious if anyone has any input on simplfiying this formula to reduce lag. Thank you.

dbrown14 · Jul 21st 2011

Re: Linked Workbooks, Extreme Lag/Processing Time

How is does your formula work? Because if you match returns a 17 or higher the index fails and there is no "2" option for the third option of match.

JSpectrum · Jul 21st 2011

Re: Linked Workbooks, Extreme Lag/Processing Time

Ranges A:P1 to A:P2000 includes numbers from 1 to 2000 in both documents. This formula pulls whatever matches for each number and then I can sort through what is needed. However, since every cell has a formula applied, it gets rather heavy on file size and processing time.

dbrown14 · Jul 21st 2011

Re: Linked Workbooks, Extreme Lag/Processing Time

Can you post an example workbook?

JSpectrum · Jul 21st 2011

Re: Linked Workbooks, Extreme Lag/Processing Time

Here is an attached example.

dbrown14 · Jul 21st 2011

Re: Linked Workbooks, Extreme Lag/Processing Time

So this workbook is trying to access a closed workbook? Can you use macros?

JSpectrum · Jul 21st 2011

Re: Linked Workbooks, Extreme Lag/Processing Time

It's linked to a document that updates every day. It would pull the data from the workbook, serving as a 'middle' document. It's sorted based on what we need, but there is a third document that keeps the data as a form of 'master list' utilizing the same fomula.

I am allowed to use macros on this one.

dbrown14 · Jul 21st 2011

Re: Linked Workbooks, Extreme Lag/Processing Time

Do you have a copy of "Example.xlsm" that I could have? (Example.xlsm referring to the document you mentioned in your formula)

JSpectrum · Jul 21st 2011

Re: Linked Workbooks, Extreme Lag/Processing Time

A few posts up, I did include a copy of my example. Did you need another document as well?

dbrown14 · Jul 21st 2011

Re: Linked Workbooks, Extreme Lag/Processing Time

Well I was just wondering what you formulas were linking to because as far as I can see every row of your formula will have the exact same data

JSpectrum · Jul 21st 2011

Re: Linked Workbooks, Extreme Lag/Processing Time

Ahhh, well, here is 'Test', they should link together to show you what is happening.

dbrown14 · Jul 21st 2011

Re: Linked Workbooks, Extreme Lag/Processing Time

So exactly what data are we looking for then extracting from Text.xlsx?

JSpectrum · Jul 21st 2011

Re: Linked Workbooks, Extreme Lag/Processing Time

'Example' would extract everything from 'Test' and then we would sort the data that is needed. I forgot to mention that each row is a line of data that corresponds. The entire row 1 is relevant, row 2, and so on.

dbrown14 · Jul 21st 2011

Re: Linked Workbooks, Extreme Lag/Processing Time

Ok. So since Test is already in order 1-Number and Example is in order 1-Number, would a macro that just copied the whole sheet to the open workbook should work, right?

JSpectrum · Jul 21st 2011

Re: Linked Workbooks, Extreme Lag/Processing Time

That would make more sense, and definately save on processing time. With security problems, it would have to be assigned to a key, not an automatic macro.

dbrown14 · Jul 21st 2011

Re: Linked Workbooks, Extreme Lag/Processing Time

Here is some VBA code. Hopefully it's pretty straight forward. As far as the security goes there is a password to activate the macro. You also need to put a password on the macro itself so people can't look at the password.

If a person really wanted to they could use a hex editor and decipher the code, but the way your code looks this way is more secure.

Code

Option Explicit


Sub GetMaster()
Dim ex As Excel.Application
Dim wrkbk As Workbook
Dim sht As Worksheet
Dim password, filePath, ans, CopySheet, MasterSheet, fileName As String
Dim tries, maxTries As Integer
Dim correct As Boolean
Dim a As Variant


Application.ScreenUpdating = False
Application.DisplayAlerts = False


fileName = ThisWorkbook.Name
password = "password"
maxTries = 3
filePath = "C:\Users\username\Desktop\Master.xlsx"
MasterSheet = "Sheet1"
CopySheet = "Sheet1"
tries = 1
correct = False


While correct = False
    ans = InputBox("What is the password?", "Verification")
    If ans = password Then
        correct = True
    ElseIf tries = maxTries Then
        ThisWorkbook.Close
    End If
    tries = tries + 1
Wend


Set ex = New Excel.Application
On Error GoTo errHandler:
Set wrkbk = ex.Workbooks.Open(filePath)
Set sht = wrkbk.Sheets(MasterSheet)
a = sht.Range("A1").CurrentRegion.Value
wrkbk.Close
ex.Quit
Sheets(CopySheet).Cells.Clear
Sheets(CopySheet).Range("A1").Resize(UBound(a, 1), UBound(a, 2)).Value = a
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Exit Sub


errHandler:
MsgBox "That file doesn't exist!"
ex.Quit
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Display More

JSpectrum · Jul 21st 2011

Re: Linked Workbooks, Extreme Lag/Processing Time

That works perfectly! Thank you.

JSpectrum · Aug 2nd 2011

Re: Linked Workbooks, Extreme Lag/Processing Time

Ok, a new question for you, rather, a bit of evolution from the current macro code.

Workbook1 has all the data and it needs to be copied/paste to Sheet2 of Workbook1, but also Copy/paste to Workbook2 Sheet1 as well. Is this an easy modification? The security password could also be removed since the documents are placed in a hidden drive.

Thank you for your help!

Linked Workbooks, Extreme Lag/Processing Time

Participate now!

Share