Vlookup from closed excel file

L

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
 
J

Jacob Skaria

Try

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

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
 
D

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
Click to expand...
 
L

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
 
D

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!
 
L

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
 
D

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
Click to expand...
 
L

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
 
D

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.
 
D

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...
 
L

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
 
D

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
Click to expand...
 
L

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
 
D

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA Save 3
Create CSV 5
Update link on vlookup from closed excel file 2
BeforeSave Event 5
BeforeSave 7
Code execution is correct but generates Error :The file you aretrying to open is in a different form 3
Want code for emailing Excel files that aren't the activewrkbk-Out 1
Code for emailing Excel workbooks will only attach activeworkbook 6

Top