Vlookup from closed excel file

[Len]

Hi,

After running the codes below, it prompts error message Application
defined or object defined error and unable to solve : -

Codes extract

Sub vbVlookup()
Dim strPath As String
Dim strFilename As String
Dim strLookupSheet As String
Dim strLookupRange As String
Dim strLookupValue As String



strLookupValue = "A$3"
strPath = "D:\My Documents\P\ManagementAcct\Apr10\PYY\"
strFilename = "PYY PL Co compare1.Apr'10.xls"
strLookupSheet = "P&L - COMPANY (compare 1)"
strLookupRange = "A3:O60"
Application.ScreenUpdating = False
Workbooks.Open strPath & strFilename 'you should also trap the
case where the book is already open.

With Workbooks("PYY & PHV Monthly analysis- Apr'10.xls")
.Sheets(3).Range("B3").Formula = "=VLOOKUP(""" & strLookupValue &
""", " & "'" & strPath & "[" & strFilename & "]" & strLookupSheet &
"'!" & strLookupRange & ", 2, False)"
.Sheets(3).Range("B3").Value = .Sheets(3).Range("B3").Value
'End With
Workbooks(strFilename).Close savechanges:=False
Application.ScreenUpdating = True


End Sub

Appreciate any help

Thanks & Regards
Len

 

[Jacob Skaria]

Try

..Sheets(3).Range("B3").Formula = "=VLOOKUP(" & strLookupValue & "," & _
"'" & strPath & "[" & strFilename & "]" & strLookupSheet & "'!" & _
strLookupRange & ", 2,False)"

 

[Len]

Jacob,

Thanks for your help, it's still the same error message after replaced
and it is quite difficult to get the correct syntax on

..Sheets(3).Range("B3").Formula = "=VLOOKUP(" & strLookupValue & "," &
_
"'" & strPath & "[" & strFilename & "]" & strLookupSheet & "'!" &
_
strLookupRange & ", 2,False)"

After several attempts, it fails again

Please help

Thanks

Regards
Len

 

[Dave Peterson]

Since the filename contains an apostrophe, you'll want to modify the string:

strFilename = "PYY PL Co compare1.Apr'10.xls"
Add another line right after it:
strFilename = replace(strfilename, "'","''")

If this doesn't work, then build the formula in a worksheet cell that works.
Just do it manually.

Then share that formula in your followup post.

You're be trying to build that same string in code. And knowing a formula
string that works will help any responder.

Jacob,

Thanks for your help, it's still the same error message after replaced
and it is quite difficult to get the correct syntax on

.Sheets(3).Range("B3").Formula = "=VLOOKUP(" & strLookupValue & "," &
_
"'" & strPath & "[" & strFilename & "]" & strLookupSheet & "'!" &
_
strLookupRange & ", 2,False)"

After several attempts, it fails again

Please help

Thanks

Regards
Len

 

[Len]

Hi Dave,

Thanks for your reply and your advice

It works perfectly after adding another line of code to replace
apostrophe
Now, I noted that file name and even path name should not contain
apostrophe or may be special character in vba codes


Cheers
Len

 

[Dave Peterson]

The apostrophe is a valid character in paths and filenames.

I think I'd use code to adjust the string rather than forcing users to remember
rules.

But, like you, I don't use them in my path's, filenames, or even sheet names!

 

[Len]

Hi Dave,

Thanks for your advice


Regards
Len

 

[Len]

Hi,


Thanks to Dave for his kind advice, I manage to obtain the result of
vlookup for cell B3
Now , I modified the codes and wish to copy down vlookup formula based
on adjacent cell ( ie using helper column A ) and this process will
slow down very much when this applies to multiple columns, is there
anyway to speed up the loop in order to achieve the required result

Sub vbVlookup()
Dim strPath As String
Dim strFilename As String
Dim strLookupSheet As String
Dim strLookupRange As String
Dim strLookupValue As String
Dim strLastRow As String
Dim tgLastRow As String
Dim tLrow As Integer
Dim i As Long

    strLookupValue = "A$3"
    strPath = "D:\My Documents\P\ManagementAcct\Apr10\PYY\"
    strFilename = "PYY PL Co compare1.Apr'10.xls"
strFilename = replace(strfilename, "'","''")
    strLookupSheet = "P&L - COMPANY (compare 1)"
    strLookupRange = "$A$3:$O$60"
strLastRow = "B$60"
tgLastRow = "C$60"
    Application.ScreenUpdating = False
    Workbooks.Open strPath & strFilename     'you should also trap the
case where the book is already open.

With Workbooks("PYY & PHV Monthly analysis- Apr'10.xls")
tLrow = .Sheets(3).Range("A" & Rows.Count).End(xlUp).Row
For i = 3 To tLrow

If .Sheets(3).Cells(i, 1).Value = "a" Then
.Sheets(3).Cells(3, 3).Formula = "=VLOOKUP( " & strLookupValue
& " ,'" & strPath & "[" & strFilename & "]" & _
strLookupSheet & "'!" & strLookupRange & ", 2,FALSE)"
.Sheets(3).Cells(3, 3).Copy .Sheets(3).Cells(i, 3)
.Sheets(3).Cells(tLrow + 4, 3).Formula = "= '" & strPath & "[" &
strFilename & "]" & _
strLookupSheet & "'!" & strLastRow & "-" & tgLastRow
End If
Next
End With
Workbooks(strFilename).Close savechanges:=False
Application.ScreenUpdating = True

End Sub

Any help will be much appreciated and thanks in advance


Regards
Len

 

[Dave Peterson]

In this loop:

For i = 3 To tLrow

If .Sheets(3).Cells(i, 1).Value = "a" Then
.Sheets(3).Cells(3, 3).Formula _
= "=VLOOKUP( " & strLookupValue & " ,'" & strPath _
& "[" & strFilename & "]" & _
strLookupSheet & "'!" & strLookupRange & ", 2,FALSE)"

.Sheets(3).Cells(3, 3).Copy .Sheets(3).Cells(i, 3)

.Sheets(3).Cells(tLrow + 4, 3).Formula _
= "= '" & strPath & "[" & strFilename & "]" & _
strLookupSheet & "'!" & strLastRow & "-" & tgLastRow
End If
Next i

You're populating the formula in C3 each time. You could move that out of the
loop and just do it once.

You may want to turn calculation off, insert the formulas, and then turn the
calculation on. I'm not sure if that will help, but it can't hurt to test.

Hi,

Thanks to Dave for his kind advice, I manage to obtain the result of
vlookup for cell B3
Now , I modified the codes and wish to copy down vlookup formula based
on adjacent cell ( ie using helper column A ) and this process will
slow down very much when this applies to multiple columns, is there
anyway to speed up the loop in order to achieve the required result

Sub vbVlookup()
Dim strPath As String
Dim strFilename As String
Dim strLookupSheet As String
Dim strLookupRange As String
Dim strLookupValue As String
Dim strLastRow As String
Dim tgLastRow As String
Dim tLrow As Integer
Dim i As Long

strLookupValue = "A$3"
strPath = "D:\My Documents\P\ManagementAcct\Apr10\PYY\"
strFilename = "PYY PL Co compare1.Apr'10.xls"
strFilename = replace(strfilename, "'","''")
strLookupSheet = "P&L - COMPANY (compare 1)"
strLookupRange = "$A$3:$O$60"
strLastRow = "B$60"
tgLastRow = "C$60"
Application.ScreenUpdating = False
Workbooks.Open strPath & strFilename 'you should also trap the
case where the book is already open.

With Workbooks("PYY & PHV Monthly analysis- Apr'10.xls")
tLrow = .Sheets(3).Range("A" & Rows.Count).End(xlUp).Row
For i = 3 To tLrow

If .Sheets(3).Cells(i, 1).Value = "a" Then
.Sheets(3).Cells(3, 3).Formula = "=VLOOKUP( " & strLookupValue
& " ,'" & strPath & "[" & strFilename & "]" & _
strLookupSheet & "'!" & strLookupRange & ", 2,FALSE)"
.Sheets(3).Cells(3, 3).Copy .Sheets(3).Cells(i, 3)
.Sheets(3).Cells(tLrow + 4, 3).Formula = "= '" & strPath & "[" &
strFilename & "]" & _
strLookupSheet & "'!" & strLastRow & "-" & tgLastRow
End If
Next
End With
Workbooks(strFilename).Close savechanges:=False
Application.ScreenUpdating = True

End Sub

Any help will be much appreciated and thanks in advance

Regards
Len

 

[Len]

Hi Dave,

With your suggestion, It works fine
Thanks for your advice again

Is there any alternative to speed up the loop ?


Regards
Len

 

[Dave Peterson]

With no testing at all.

Maybe you can drop the check to see if the value in column A is an "a" by moving
it into the formula. And then you could just plop the formula into all the
cells.

But this would break if you had values/formulas in those cells that need to be
kept.

 

[Don Guillett]

I haven't followed this but there I did one recently for a client where I
had formulas such as yours referring to a defined named range which had the
workbook desired. Then a macro to change the workbook reference within the
defined name...

 

[Len]

Hi Dave, Don,

Thanks alot and stay with me

Sorry for late reply as I was busy with month end closing

"a" is another lookup string in helper column to assist vlookup
formula ( ie when there is "a" in column A3, vlookup formula will be
placed on cell C3 to lookup value in B3 from excel source file )

After several attempts to workaround, I came up with another codes
below to replace the earlier one that try to achieve the similar
required result

I just explore the way to speed up the loop but it fails to change
lookup value $B3 ( ie relative reference ) when populating the vlookup
formula in C3 each time


Dim k As Long
Dim rNa As Variant
Dim iLoop As Integer

With Workbooks("PYY & PHV Monthly analysis- Apr'10.xls")
tLrow = .Sheets(3).Range("A" & Rows.Count).End(xlUp).Row
iLoop = WorksheetFunction.CountIf(.Sheets(3).Columns(1), "a")
Set rNa = .Sheets(3).Range("A1")
For i = 3 To iLoop
Set rNa = .Sheets(3).Columns(1).Find(What:="a", After:=rNa, _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows,
_
SearchDirection:=xlNext, MatchCase:=True)

rNa.Offset(0, 2).Formula = "=VLOOKUP( " & strLookupValue &
" ,'" & strPath & "[" & strFilename & "]" & _
strLookupSheet & "'!" & strLookupRange & ", 2,FALSE)"
.Sheets(3).Cells(tLrow + 4, 3).Formula = "= '" & strPath & "["
& strFilename & "]" & _
strLookupSheet & "'!" & strLastRow & "-" & tgLastRow
Next i

Any idea how to rectify the above codes and thanks again


Regards
Len

 

[Dave Peterson]

I'm not sure what you're doing with that last formula in that loop. (Maybe just
checking????)

But how about:

Option Explicit
Sub vbVlookup()
Dim strPath As String
Dim strFilename As String
Dim strLookupSheet As String
Dim strLookupRange As String
Dim strLookupValue As String
Dim strLastRow As String
Dim tgLastRow As String
Dim tLrow As Long
Dim i As Long
Dim iLoop As Long
Dim rNA As Range

strLookupValue = "A$3"
strPath = "D:\My Documents\P\ManagementAcct\Apr10\PYY\"
strFilename = "PYY PL Co compare1.Apr'10.xls"
strFilename = Replace(strFilename, "'", "''")
strLookupSheet = "P&L - COMPANY (compare 1)"
strLookupRange = "$A$3:$O$60"
strLastRow = "B$60"
tgLastRow = "C$60"
Application.ScreenUpdating = False
Workbooks.Open strPath & strFilename

With Workbooks("PYY & PHV Monthly analysis- Apr'10.xls")
tLrow = .Sheets(3).Range("A" & Rows.Count).End(xlUp).Row
iLoop = WorksheetFunction.CountIf(.Sheets(3).Columns(1), "a")
Set rNA = .Sheets(3).Range("A1")
'look for all the A's, don't start with 3.
For i = 1 To iLoop
Set rNA = .Sheets(3).Columns(1).Find(What:="a", After:=rNA, _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True)

rNA.Offset(0, 2).Formula = "=VLOOKUP(" _
& rNA.Offset(0, 1).Address(external:=True) _
& " ,'" & strPath & "[" & strFilename & "]" & _
strLookupSheet & "'!" & strLookupRange & ", 2,FALSE)"

.Sheets(3).Cells(tLrow + 4, 3).Formula = "= '" & strPath _
& "[" & strFilename & "]" _
& strLookupSheet & "'!" & strLastRow & "-" & tgLastRow

Next i
End With



Workbooks(strFilename).Close savechanges:=False
Application.ScreenUpdating = True

End Sub

Hi Dave, Don,

Thanks alot and stay with me

Sorry for late reply as I was busy with month end closing

"a" is another lookup string in helper column to assist vlookup
formula ( ie when there is "a" in column A3, vlookup formula will be
placed on cell C3 to lookup value in B3 from excel source file )

After several attempts to workaround, I came up with another codes
below to replace the earlier one that try to achieve the similar
required result

I just explore the way to speed up the loop but it fails to change
lookup value $B3 ( ie relative reference ) when populating the vlookup
formula in C3 each time


Dim k As Long
Dim rNa As Variant
Dim iLoop As Integer

With Workbooks("PYY& PHV Monthly analysis- Apr'10.xls")
tLrow = .Sheets(3).Range("A"& Rows.Count).End(xlUp).Row
iLoop = WorksheetFunction.CountIf(.Sheets(3).Columns(1), "a")
Set rNa = .Sheets(3).Range("A1")
For i = 3 To iLoop
Set rNa = .Sheets(3).Columns(1).Find(What:="a", After:=rNa, _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows,
_
SearchDirection:=xlNext, MatchCase:=True)

rNa.Offset(0, 2).Formula = "=VLOOKUP( "& strLookupValue&
" ,'"& strPath& "["& strFilename& "]"& _
strLookupSheet& "'!"& strLookupRange& ", 2,FALSE)"
.Sheets(3).Cells(tLrow + 4, 3).Formula = "= '"& strPath& "["
& strFilename& "]"& _
strLookupSheet& "'!"& strLastRow& "-"& tgLastRow
Next i

Any idea how to rectify the above codes and thanks again


Regards
Len

 

[Len]

Hi Dave,

Thanks for your codes and really appreciate your help

It works perfectly and really more efficient than earlier loop
especially when it need to populate vlookup formula in each cell and
then apply to multiple columns

The last formula in that loop helps to perform control check after
rolling out


Cheers
Len

 

[Dave Peterson]

Using the .find will be a big time savior if the number of a's is small compared
to the amount of data.

Glad you got it working better.