Chirag R Raval
Member
Dear All,
I have a working code for get data from closed file..
but this is very slow..
how to modify this code to run faster??
if use, how to use "Array", "Collection" or "dictionary" for make it fast?
or which from above 3 can do better? & how to use it?
hope little help ..will be appreciated..
Regards,
Chirag Raval
I have a working code for get data from closed file..
Code:
'Category: VBA Functions | [Item URL]
''VBA does not include a method to retrieve a value from a closed file. You can, however, take advantage of Excel's ability to work with linked files.
''This tip contains a VBA function that retrieves a value from a closed workbook. It does by calling an XLM macro.
''Note: You cannot use this function in a worksheet formula.
''The GetValue Function
''The GetValue function, listed below takes four arguments:
'•path: The drive and path to the closed file (e.g., "d:\files")
'•file: The workbook name (e.g., "budget.xls")
'•sheet: The worksheet name (e.g., "Sheet1")
'•ref: The cell reference (e.g., "C4")
Private Function GetValue(Path, File, sheet, Ref)
' Retrieves a value from a closed workbook
Dim Arg As String
' Make sure the file exists
If Right(Path, 1) <> "\" Then Path = Path & "\"
If Dir(Path & File) = "" Then
GetValue = "File Not Found"
Exit Function
End If
' Create the argument
Arg = "'" & Path & "[" & File & "]" & sheet & "'!" & _
range(Ref).range("A1").Address(, , xlR1C1)
' Execute an XLM macro
GetValue = ExecuteExcel4Macro(Arg)
End Function
'Using the GetValue Function
'To use this function, copy the listing to a VBA module. Then, call the function with the appropriate arguments.
'The Sub procedure below demonstrates. It simply displays the value in cell A1 in Sheet1 of a
'file named Budget.xls, located in the XLFiles\Budget directory on drive C:.
'Sub TestGetValue()
'Dim a As String
' p = "C:\TEST\"
' f = "SUITING-BUYER MASTER.xlsx"
' s = "BUY MASTER"
' a = "A1"
' MsgBox GetValue(p, f, s, a)
'End Sub
'Another example is shown below.
'This procedure reads 1,200 values (100 rows and 12 columns) from a closed file, and places the values into the active worksheet.
Sub TestGetValue2()
Dim p As String
Dim f As String
Dim s As String
Dim a As String
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
p = "C:\"
f = "SUITING-BUYER MASTER.xlsx"
s = "Sheet1"
a = "A1"
Application.ScreenUpdating = False
For r = 1 To 100
For c = 1 To 5
a = Cells(r, c).Address
Cells(r, c) = GetValue(p, f, s, a)
Next c
Next r
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub
'Caveat
'In order for this function to work properly, a worksheet must be active in Excel.
'It will generate an error if all windows are hidden, or if the active sheet is a Chart sheet.
but this is very slow..
how to modify this code to run faster??
if use, how to use "Array", "Collection" or "dictionary" for make it fast?
or which from above 3 can do better? & how to use it?
hope little help ..will be appreciated..
Regards,
Chirag Raval