Application.VLookup and External files

[DG]

I want to use Application.VLookup to find values in an excel spreadsheet on
a network.

I'm not sure of the syntax, especailly the " or ' and !.

Here is what I have:

In Sheet1 of Book3.xls I have this in a vba module:

Sub FindPrice()
cells(a,1) = Application.VLookup("PRM 8018539",'\\myserver01\PRICE
UPDATES\[IPRICE 1.xls]'!sheet1.range("B2",Range("C65536").End(xlUp)),false)
End Sub

Exactly as is returns Compile Error: Expected: expression ant the first
single quote in front of myserver.

If I change both single quotes to double quotes I get: Compile Error:
Expected: list separator or ).

Help.

DG

 

[Dave Peterson]

You have a couple of choices.

#1. You can open the file and then use application.vlookup() against that open
file.

Dim iPrWks as worksheet
dim iPrRng as range
dim res as variant 'could be an error

set iprwks = workbooks.open _
(filename:="\\myserver01\price updates\iprice 1.xls",
_ readonly:=true).worksheets("Sheet1")

with iprwks
set iprRng = .range("B2",.cells(.rows.count,"C").end(xlup))
end with

'return the second column of the range????
res = application.vlookup("prm 8018539", iprrng, 2, false)

if iserror(res) then
res = "No match"
end if

activesheet.cells(a,1).value = res

#2. You could build a formula and plop it into that cell. Then convert it to
values:

With activesheet.cells(a,1)
.formula = "=vlookup(""PRM 8018539""," _
& "'\\myserver01\PRICE UPDATES\[IPRICE 1.xls]'!sheet1!b:c,2,false)"
.value = .value
end with

Notice that the double quotes surrounding strings are doubled up. And I changed
the range to B:C instead of limiting the rows.

I want to use Application.VLookup to find values in an excel spreadsheet on
a network.

I'm not sure of the syntax, especailly the " or ' and !.

Here is what I have:

In Sheet1 of Book3.xls I have this in a vba module:

Sub FindPrice()
cells(a,1) = Application.VLookup("PRM 8018539",'\\myserver01\PRICE
UPDATES\[IPRICE 1.xls]'!sheet1.range("B2",Range("C65536").End(xlUp)),false)
End Sub

Exactly as is returns Compile Error: Expected: expression ant the first
single quote in front of myserver.

If I change both single quotes to double quotes I get: Compile Error:
Expected: list separator or ).

Help.

DG

 

[Dave Peterson]

I messed up the second suggestion. (I wasn't careful enough with my exclamation
points and apostrophes!



#2. You could build a formula and plop it into that cell. Then convert it to
values:

With activesheet.cells(a,1)
.formula = "=vlookup(""PRM 8018539""," _
& "'\\myserver01\PRICE UPDATES\[IPRICE 1.xls]sheet1'!b:c,2,false)"
.value = .value
end with

I want to use Application.VLookup to find values in an excel spreadsheet on
a network.

I'm not sure of the syntax, especailly the " or ' and !.

Here is what I have:

In Sheet1 of Book3.xls I have this in a vba module:

Sub FindPrice()
cells(a,1) = Application.VLookup("PRM 8018539",'\\myserver01\PRICE
UPDATES\[IPRICE 1.xls]'!sheet1.range("B2",Range("C65536").End(xlUp)),false)
End Sub

Exactly as is returns Compile Error: Expected: expression ant the first
single quote in front of myserver.

If I change both single quotes to double quotes I get: Compile Error:
Expected: list separator or ).

Help.

DG