Chirag R Raval
Member
Dear All,
just guideline require
I have a code that temp-open source file in background (Hidden Mode)
& vlookup & display result in active sheet's calling cell that already worked...
if same method use for store this result in variable to use this variable In further process.
"Object Dose Not Support This Property or Method"
error displayed
Help will be appreciated..
Regards,
Chirag Raval
just guideline require
I have a code that temp-open source file in background (Hidden Mode)
& vlookup & display result in active sheet's calling cell that already worked...
if same method use for store this result in variable to use this variable In further process.
"Object Dose Not Support This Property or Method"
error displayed
Code:
Sub Test_of_vlkp_closed_Result_Store_in_variable()
'ONLY FOR VLOOKUP ONCLOSED FILE WHICH TEMP OPEN & CLOSED
'Declare variables
Dim sPath As String
Dim sFile As String
Dim sSheet As String
Dim sRef As String
Dim sFullName As String
Dim wbSource As Workbook
Dim wksDest As Worksheet
Dim bWorkbookOpened As Boolean
Dim myname As String
Dim fname As String
Dim fpath As String
'If no worksheet is active, exit sub
If TypeName(ActiveSheet) <> "Worksheet" Then
MsgBox "No worksheet is active.!", vbExclamation
End If
'Assign the active worksheet to a variable
Set wksDest = ActiveSheet
'Specify path to source file (change accordingly)
sPath = "C:\BUYER MASTER\"
'Check if path exists
If Len(Dir(sPath, vbDirectory)) = 0 Then
MsgBox "Path does not exist.", vbInformation
Else
'Make sure path ends in back slash
If Right(sPath, 1) <> "\" Then
sPath = sPath & "\"
End If
End If
'Specify source file (change accordingly)
'BEWARE HERE FOR SHEET NAME
sFile = "SUITING-BUYER MASTER.xlsx"
sSheet = "BUY MASTER"
sRef = "$H$1:$I$500"
'Specify path and source file
sFullName = sPath & sFile
'Check if workbook exists
If Len(Dir(sFullName, vbNormal)) = 0 Then
MsgBox "Workbook does not exist.", vbInformation
End If
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
'Open specified workbook as read only
Set wbSource = Workbooks.Open(FileName:=sFullName, ReadOnly:=True)
bWorkbookOpened = True
'Do stuff
'myname = =vlookup(a2," & wbSource.Worksheets(sSheet).range(sRef).Address(, , , True) & ",{2,3,4},false)"
'example--Result = Application.WorksheetFunction.Vlookup ( lookup_value, table_array, col_index_num, [range_lookup] )
myname = Application.WorksheetFunction.vlookup(wksDest.range("A2").Value, wbSource(sFile).Worksheets(sSheet).range(sRef).Address, 2, False)
'If source workbook was opened, close without saving
If bWorkbookOpened Then
wbSource.Close savechanges:=False
End If
'Clear from memory
Set wbSource = Nothing
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
MsgBox myname
DisplayError:
End SubHelp will be appreciated..
Regards,
Chirag Raval