Unlink Formula - slow calculation

[televisi]

Hi All,
Let's say I have 2 xls file (test1.xls & test2.xls); and test1 file referring the value from test2 file

TEST 1.xls

Cell (A1) => "Test"
Cell (A2) => formula:
=HLOOKUP(A1,'c:\[test2.xls]sheet1'!$H$2:$CF$125,124,FALSE)

Cell (B1) => "Blah"
Cell (B2) => formula:
=HLOOKUP(B1,'c:\[test2.xls]sheet1'!$H$2:$CF$125,124,FALSE)


Cell (C1) => "Blah again"
Cell (C2) => formula:
=HLOOKUP(C1,'c:\[test2.xls]sheet1'!$H$2:$CF$125,124,FALSE)

To do the above actions, I created a macro, but excel always frozen for couple of seconds during cell value set (.Cells(X, Y).FormulaR1C1 = tempvalue); as it will check the value from test2.xls

With Sheets("TEST2")
    Y = 2
    Do While (.Cells(X - 1, Y).Value <> "")
        tempvalue = "=HLOOKUP(R[-1]C,'C:\[TEST2.xls]Sheet1'!R2C8:R125C84,124,FALSE"
        [B].Cells(X, Y).FormulaR1C1 = tempvalue[/B]
        Y = Y + 1
    Loop
end with

I've tried to unlink the file beforehand:

astrLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
    If Not IsEmpty(astrLinks) Then
        ActiveWorkbook.BreakLink _
            Name:=astrLinks(1), _
            Type:=xlLinkTypeExcelLinks
    End If

as well as

Application.ScreenUpdating = false

and

Application.Calculation = xlCalculationManual

But no luck, it seems XLS keeps linking to that page everytime I put value (either manually / by macro)

Any idea?, as it slows down my xls

Thanks

 

[Jerry Sullivan]

Hello and Welcome,

I'm not sure what the problem is, but here are a few things to check...

The formula assumes that your workbook Test2.xls is not open....is it possible Text2.xls is open when you try to run the VBA Code?

The partial procedure code you posted doesn't define the value of X; have you set it to 2 earlier in the procedure?

Also, the code you posted is missing a parenthesis in the formula. I'm guessing that is a typo in your post and not from the code you are running, since that would generate a run-time error instead of just running slowly.


Nonetheless.... perhaps if you try this slightly modified version it will work better. It just takes a fraction of a second on my PC.

Option Explicit
Sub Add_Links()
    Dim X As Long, Y As Long
    Dim tempvalue As String
    Application.ScreenUpdating = False
    With Sheets("TEST2")
        X = 2
        Y = 1
        Do While (.Cells(X - 1, Y).Value <> "")
            tempvalue = "=HLOOKUP(R[-1]C,'C:\[TEST2.xls]Sheet1'!R2C8:R125C84,124,FALSE)"
             .Cells(X, Y).FormulaR1C1 = tempvalue
            Y = Y + 1
        Loop
    End With
End Sub

Good luck!