how to use VB code to define vlookup in two workbook

[yihong]

Dear friends,

I try to combine data between two worksheet using VB. One workbook
calls SAN kept in c:\test\import, another workbook calls WYE kept in
c:\test\backup

SAN Workbook has one sheet (sheet1)
COLUMN A B
C034001
C042001
C054021

WYE Workbook has one sheet(code)
column A B
C034001 WYE1
C042001 WYE2
C054021 WYE3


Now want to use vb code to copy WYE column B value to SAN column B
based on the column A. But always failed. Could anybody help me. Thanks
so much

My current some code as below
set backxl=new excel.application
set destxl=new excel.applicaiton

strDestF=C:\test\import\san.xls
strBackF=c:\test\backup\wye.xls
destXL.Workbooks.Open strDestF
backXL.Workbooks.Open strBackF
i=0


I try to use vlookup to get value from wye.xls to san.xls, but failed
destXL.Cell(i + 2, 2) = "vlookup('" & destXL.Cells(i + 2, 1) & "'," &
"[strBackF]Code!A$A$2" & ":" & "$B$4" & ",2)"

Regards

 

[duane]

how about using find command to get the row and just refer to the cell
column and row?

the below is an example

tender = Sheets("km").Cells(j, colkm).Text
Set rng = Sheets("nominated").Range("nom").Find(tender)
If rng Is Nothing Then GoTo badkmtender
nomrow = rng.Row

 

[yihong]

Hi, Duane

Thanks for your reply. Because wye.xls has many codes. A is our company
code, B is our customer code. So use Vlookup I think is the fastest way
to get the customer code from WYE.xls and paste to SAN.XLS.

Regards

 

[yihong]

Dear friends,

I used VB (excel.application) to open two excel file (One called
AAA.XLS is standard code, another called BBB.XLS will get code from
AAA.XLS), after finishing, I use .Quit to exit both of excel file. Then
again to open another excel file CCC.XLS to get AAA.XLS code, but
failed. I suspected excel or workbook or temp file didn't acutually
close. Below is part of my codeing, when second time run to
destXL.Cells(iRowDest, 31) = Application.VLookup(findString, rang1, 2,
False), will failed. Here destxl is the example BBB.XLS or CCC.XLS,
backxl likes AAA.XLS.
I tried many times, but all failed. Anyone can help. Thanks so much!

Set backXL = New excel.Application 'SANKYU AND WYETH CODE
Set destXL = New excel.Application 'NEED ADD WYETH CODE

destXL.Workbooks.Open strDestF
destXL.Cells(1, 31) = "WYETH"

backXL.Workbooks.Open strBackF
'Do While backXL.Cells(backCount, 1) <> ""
' backCount = backCount + 1 'get how many wyeth code in template
'Loop

'
Do While destXL.Cells(iRowDest, 30) <> ""
Dim findString As String
Dim rang1 As Range

Set rang1 = backXL.Range("A2:B10000")
findString = destXL.Cells(iRowDest, 30)
destXL.Cells(iRowDest, 31) = Application.VLookup(findString, rang1,
2, False)

iRowDest = iRowDest + 1
sourceCount = iRowDest

Loop
destXL.Cells(sourceCount, 25) = "=SUM(Y2:Y" & sourceCount - 1 & ")"
lbxFile.Refresh
Kill (strSourceF)
MsgBox "Finish to add Wyeth Code!", vbOKOnly, "Finish"
'clean up and exit
Set oWS = Nothing
If Not oWB Is Nothing Then oWB.Close
Set oWB = Nothing
destXL.Quit
backXL.Quit
'Kill (strBackF)
btnExport.Enabled = False
Set destXL = Nothing
Set backXL = Nothing
Application.Quit
btnExport.Enabled = False
btnConfirm.Enabled = False
excel.Application.Quit
'Application.ActiveWindow.Close
'Unload Me
Exit Sub