csehz
asked on
VBA Excel - Outer file Vlookup speed issues
Dear Experts,
Can I ask your experience about known performance issues if with VBA would like to vlookup another excel file.
In the attached Code part I am linking and outer file and that file is always around 3 Mb in size with around 3500 rows and 54 columns.Basically the source file so from where the vlookup macro is starting has the same parameters.
Somehow this vlookup is very slow, I am sure it depends on the vlookup range because if I change the target range from R1C12:R3500C12 to R1C12:R15C12 so only just checking 15 rows, it runs in some seconds.
My concrete questions would be
- does it matter whether the outer file is open, or better to leave it close?
- generally such vlookup used to be applied between files, or there are such disadvantages?
- do you have ideas how to be in balance and speed it up?
thanks,
Can I ask your experience about known performance issues if with VBA would like to vlookup another excel file.
In the attached Code part I am linking and outer file and that file is always around 3 Mb in size with around 3500 rows and 54 columns.Basically the source file so from where the vlookup macro is starting has the same parameters.
Somehow this vlookup is very slow, I am sure it depends on the vlookup range because if I change the target range from R1C12:R3500C12 to R1C12:R15C12 so only just checking 15 rows, it runs in some seconds.
My concrete questions would be
- does it matter whether the outer file is open, or better to leave it close?
- generally such vlookup used to be applied between files, or there are such disadvantages?
- do you have ideas how to be in balance and speed it up?
thanks,
Range("AU2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(RC[-35],'D:\Path\" & MilyenHonap & "\[OpenOrders " & _
FileNap & ".03.10.xls]Sheet1'!R1C12:R3500C12,1,0)),""Go"","""")"
Range("AU2").Copy Range(Cells(3, 47), Cells(LastRowWithValue, 47))
Columns("AU:AU").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The code would be:
Workbooks.Open "D:\Path\File.xls"
ASKER
Thanks very much I was not aware concretely
ASKER
Can I ask if we have D:\Path\File.xls, how to open it through VBA?