asked on

VLookUP by vbscript

If I have 2 CSV file as following:

File 1:

aaa,111
bbb,222
ccc,333
ddd,444

Fille2 as following

C1,C2,aaa,C3,C4
C1,C2,ddd,C3,C4
C1,C2,aaa,C3,C4
C1,C2,ccc,C3,C4
C1,C2,bbb,C3,C4
C1,C2,aaa,C3,C4
C1,C2,bbb,C3,C4
C1,C2,aaa,C3,C4
C1,C2,aaa,C3,C4
C1,C2,ccc,C3,C4

I want vbscript to open both csv file and create new csv. the new csv file will be like file2 but script will add new colmun wich will convet aaa to 111 and bbb to 222 which take from file 1

C1,C2,aaa,111,C3,C4
C1,C2,ddd,444,C3,C4
C1,C2,aaa,111,C3,C4
C1,C2,ccc,333,C3,C4
C1,C2,bbb,222,C3,C4
C1,C2,aaa,111,C3,C4
C1,C2,bbb,222,C3,C4
C1,C2,aaa,111,C3,C4
C1,C2,aaa,111,C3,C4
C1,C2,ccc,333,C3,C4

Thank you

TommySzalapski

Try this script

Dim objExcel, objWB1, objWB2, objWS1, objWS2

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True

Set objWB1 = objExcel.Workbooks.Open("C:\temp\file1.csv")
Set objWS1 = objWB1.Sheets(1)

Set objWB2 = objExcel.Workbooks.Open("C:\temp\file2.csv")
Set objWS2 = objWB2.Sheets(1)

objWS2.Columns(4).Insert

objWS2.Range("D1:D" & objWS2.UsedRange.Rows.Count).Formula = "=VLOOKUP(INDEX(C:C,ROW()),file1.csv!$A:$B,2)"
objWS2.Range("D1:D" & objWS2.UsedRange.Rows.Count).Formula = objWS2.Range("D1:D" & objWS2.UsedRange.Rows.Count).Value
'objWS2.Columns("D:D").Copy
'objWS2.Columns("D:D").Select
'objWB2.Selection.PasteSpecial xlPasteValues

objWB2.Save
objWB2.Close false
objWB1.Close false

objExcel.Quit

Open in new window

ASKER CERTIFIED SOLUTION

Bill Prew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

with_out_user

ASKER

Thank you alot,

I will check it and reply with result

with_out_user

ASKER

Thank you

Work Fine and as i need

Bill Prew

Great, glad that was useful. Thanks.

~bp