Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.VLookup and External files
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)),fal se) 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.VLookup and External files
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. DG wrote: 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)),fal se) 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.VLookup and External files
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 DG wrote: 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)),fal se) 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Launch external application | Excel Programming | |||
Open an external application | Excel Discussion (Misc queries) | |||
SOS: Connecting to External Application (AS400C) | Excel Programming | |||
Linking from external application Crystal | Excel Discussion (Misc queries) | |||
Close External Application using VBA | Excel Programming |