PDA

View Full Version : VBA code for Vlookup in 2 wkbooks and import to master



tyrese214
08-25-2007, 05:07 AM
Hi all, I am HAPPY TO PAY the first person (via paypal) that can have a solution to this problem in the next few hours .... Thankyou

Im creating a cash reconciliation application for a BUS operator client that has 6 depots and 1500 drivers.

each driver is uniquely identified by a special number.

Basically, I need to import data that is in 2 different workbooks that match a driver number in the main workbook using a VBA Vlookup code.

The cashrec is saved in the following destination:

C:\BT\cashrec.xls

This workbook has a driver name in column A and driver number in column B.


2 separate folders that contain workbooks of the days of data are as follows:

C:\BT\Cubic - April\
C:\BT\DVL - April\

ok now, the format of the 2 workbooks that need to be imported:

one will be named;

cubic1.xls

This will reside in the C:\BT\Cubic - April\ destination,


the 1 in the workbook named cubic1.xls, represents the day. I will do the import on this workbook on day 1.

The driver number in the cubic1.xls workbook that is to be matched with the cashrec.xls workbook appears in column N.

If there is a match of driver number to the driver number in column B of the Cashrec.xls, the relevant amount in column J in the cubic1.xls workbook is to be imported into column D of the Cashrec.


the name of the other workbook is:

DVL1.xls

This will reside in the C:\BT\DVL - April\ destination,

The driver number in the dvl1.xls workbook that is to be matched with the cashrec.xls workbook appears in column I.

If there is a match of driver number to the driver number in column B of the Cashrec.xls, the relevant amount in column D in the DVL1.xls workbook is to be imported into column C of the Cashrec.

in addition;

If there is a match of driver number to the driver number in column B of the Cashrec.xls, the relevant amount in column E in the DVL1.xls workbook is to be imported into column F of the Cashrec.xls


I am assured that the format for the vlookup will not change again.


I need two separate procedures that will first import from the

C:\BT\Cubic - April\

then the

C:\BT\DVL - April\

the identification of the pathname and filename to go to, are in my cashrec.xls in cell n1 & n2 for the cubic import and o1 & o2 for the DVL import.

I gave it my best shot to write two separate procedures for the imports but I am majorly confused and stuck as to the proper syntax for my objective and desires as above. I am having trouble coding exactly my requirements as I have throughly explained above..

here is my crack at the code:

I have attached a layout of my cashrec.xls and the 2 files with the contents to be imported to this posting... please view and help me out....

my email is: accounting.solutions@chandra.id.au

Thankyou heaps to everyone that can help me out.. I will definetely keep whoever in mind for any paid projects from my contacts.. thankyou in advance and I look forward to your response as this is due tomorrow..






Sub import1()
'this code imports the days within the Cubic file and assigns it to driver number

Dim myDir, fn As String, rng As Range, msg As String

Pathname = ActiveSheet.Range("n1").Value
Filename = ActiveSheet.Range("n2").Value
'strWks = ActiveSheet.Range("c3").Value



With ThisWorkbook.ActiveSheet
Set rng = .Range("b8", .Range("b" & Rows.Count).End(xlUp)).Offset(, 2)
End With
myDir = ActiveSheet.Range("n1").Value

fn = ActiveSheet.Range("n2").Value
If fn = "" Then
msg = "File Is Not found"
Else
With rng.Offset(, i)
.Formula = "=vlookup(b8,'" & myDir & "\[" & fn & "]sheet1'!c:f," & i + 2 & ",false)"
.Value = .Value
End With
End If

If Len(msg) Then MsgBox msg
End Sub




Sub import2()
'this code imports the days within the DVL file and assigns it to driver number

Dim myDir, fn As String, rng As Range, msg As String

Pathname = ActiveSheet.Range("o1").Value
Filename = ActiveSheet.Range("o2").Value
'strWks = ActiveSheet.Range("c3").Value



With ThisWorkbook.ActiveSheet
Set rng = .Range("b8", .Range("b" & Rows.Count).End(xlUp)).Offset(, 1)
End With
myDir = ActiveSheet.Range("n1").Value

fn = ActiveSheet.Range("n2").Value
If fn = "" Then
msg = "File Is Not found"
Else
With rng.Offset(, i)
.Formula = "=vlookup(b8,'" & myDir & "\[" & fn & "]sheet1'!c:f," & i + 2 & ",false)"
.Value = .Value
End With
End If

If Len(msg) Then MsgBox msg
End Sub

rbrhodes
08-25-2007, 10:05 PM
Here you go: