Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
using VLOOKUP from MS Access
I'm openning an excel workbook with many spreadsheets from MS Access by using
the following code: Dim xlapp As New Excel.Application Dim xlbook As New Excel.Workbook Set xlbook = xlapp.Workbooks.Open(strFileName, True) How could I use VLOOKUP to find a needed number from Sheet1 in this workbook? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
using VLOOKUP from MS Access
In excel you can use
Application.vlookup(What, Where, 1, False) So I would think that you could try xlbook.vlookup.(What, Where, 1, False) Let me know if that works... "Alex" wrote: I'm openning an excel workbook with many spreadsheets from MS Access by using the following code: Dim xlapp As New Excel.Application Dim xlbook As New Excel.Workbook Set xlbook = xlapp.Workbooks.Open(strFileName, True) How could I use VLOOKUP to find a needed number from Sheet1 in this workbook? Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
using VLOOKUP from MS Access
This worked in MSWord with a reference to MSExcel.
Option Explicit Sub testme() Dim xlApp As Excel.Application Dim xlBook As New Excel.Workbook Dim strFileName As String Dim res As Variant Dim myRng As Excel.Range Dim myVal As Variant strFileName = "C:\my documents\excel\book1.xls" myVal = "asdf3" Set xlApp = New Excel.Application xlApp.Visible = True 'nice for testing Set xlBook = xlApp.Workbooks.Open(strFileName, True) Set myRng = xlBook.Worksheets("Sheet1").Range("a1:E99") res = xlApp.VLookup(myVal, myRng, 2, False) If IsError(res) Then MsgBox "No match" Else MsgBox res End If xlBook.Close savechanges:=False xlApp.Quit Set myRng = Nothing Set xlBook = Nothing Set xlApp = Nothing End Sub Alex wrote: I'm openning an excel workbook with many spreadsheets from MS Access by using the following code: Dim xlapp As New Excel.Application Dim xlbook As New Excel.Workbook Set xlbook = xlapp.Workbooks.Open(strFileName, True) How could I use VLOOKUP to find a needed number from Sheet1 in this workbook? Thanks -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
using VLOOKUP from MS Access
Thanks a lot, Jim.
I'm trying this after the workbook's openning but may be something wrong with how I'm using the path to the file or a range. So, it's not working. With xlbook curCost = .WorksheetFunction.VLookup(strRecipeNum, "'S:\Operations\Products\Recipes\[MRP-Recipes.xls]'!A1:C400", 12, False) End With "Jim Thomlinson" wrote: In excel you can use Application.vlookup(What, Where, 1, False) So I would think that you could try xlbook.vlookup.(What, Where, 1, False) Let me know if that works... "Alex" wrote: I'm openning an excel workbook with many spreadsheets from MS Access by using the following code: Dim xlapp As New Excel.Application Dim xlbook As New Excel.Workbook Set xlbook = xlapp.Workbooks.Open(strFileName, True) How could I use VLOOKUP to find a needed number from Sheet1 in this workbook? Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
using VLOOKUP from MS Access
Thanks a lot, Dave.
The code goes without any error messages. But, just my vlookup function cannot return any value. It returns error 2042. It looks like i'm doing the same, but ..... Set xlbook = xlApp.Workbooks.Open(strFileName, True) Set myRng = xlbook.Worksheets("Legend").Range("C3:L500") 'With xlbook ' .Worksheets("Legend").Range("C3:L500").Name = "d" 'End With ' I've checked, just in case, whether the range can be set up there. varCost = xlApp.VLookup(strVal, myRng, 12, False) I think, there is some simple mistake, but I cannot see it. "Dave Peterson" wrote: This worked in MSWord with a reference to MSExcel. Option Explicit Sub testme() Dim xlApp As Excel.Application Dim xlBook As New Excel.Workbook Dim strFileName As String Dim res As Variant Dim myRng As Excel.Range Dim myVal As Variant strFileName = "C:\my documents\excel\book1.xls" myVal = "asdf3" Set xlApp = New Excel.Application xlApp.Visible = True 'nice for testing Set xlBook = xlApp.Workbooks.Open(strFileName, True) Set myRng = xlBook.Worksheets("Sheet1").Range("a1:E99") res = xlApp.VLookup(myVal, myRng, 2, False) If IsError(res) Then MsgBox "No match" Else MsgBox res End If xlBook.Close savechanges:=False xlApp.Quit Set myRng = Nothing Set xlBook = Nothing Set xlApp = Nothing End Sub Alex wrote: I'm openning an excel workbook with many spreadsheets from MS Access by using the following code: Dim xlapp As New Excel.Application Dim xlbook As New Excel.Workbook Set xlbook = xlapp.Workbooks.Open(strFileName, True) How could I use VLOOKUP to find a needed number from Sheet1 in this workbook? Thanks -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
using VLOOKUP from MS Access
I found a mistake.
If I'm using the range such as C3:L500, I should use 10 for column (not 12). Thanks everybody. "Alex" wrote: Thanks a lot, Dave. The code goes without any error messages. But, just my vlookup function cannot return any value. It returns error 2042. It looks like i'm doing the same, but ..... Set xlbook = xlApp.Workbooks.Open(strFileName, True) Set myRng = xlbook.Worksheets("Legend").Range("C3:L500") 'With xlbook ' .Worksheets("Legend").Range("C3:L500").Name = "d" 'End With ' I've checked, just in case, whether the range can be set up there. varCost = xlApp.VLookup(strVal, myRng, 12, False) I think, there is some simple mistake, but I cannot see it. "Dave Peterson" wrote: This worked in MSWord with a reference to MSExcel. Option Explicit Sub testme() Dim xlApp As Excel.Application Dim xlBook As New Excel.Workbook Dim strFileName As String Dim res As Variant Dim myRng As Excel.Range Dim myVal As Variant strFileName = "C:\my documents\excel\book1.xls" myVal = "asdf3" Set xlApp = New Excel.Application xlApp.Visible = True 'nice for testing Set xlBook = xlApp.Workbooks.Open(strFileName, True) Set myRng = xlBook.Worksheets("Sheet1").Range("a1:E99") res = xlApp.VLookup(myVal, myRng, 2, False) If IsError(res) Then MsgBox "No match" Else MsgBox res End If xlBook.Close savechanges:=False xlApp.Quit Set myRng = Nothing Set xlBook = Nothing Set xlApp = Nothing End Sub Alex wrote: I'm openning an excel workbook with many spreadsheets from MS Access by using the following code: Dim xlapp As New Excel.Application Dim xlbook As New Excel.Workbook Set xlbook = xlapp.Workbooks.Open(strFileName, True) How could I use VLOOKUP to find a needed number from Sheet1 in this workbook? Thanks -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup from Access | Excel Discussion (Misc queries) | |||
Using VLOOKUP to look up info from Access | Excel Worksheet Functions | |||
vlookup from Access db | Links and Linking in Excel | |||
VLOOKUP from Access Tables | Excel Worksheet Functions | |||
Vlookup to Access Dbase | Excel Programming |