Macro - get data from other workbook

[alexjamesbrown]

I am trying to create a macro to do the following:

I have an excel file, called Warehouse.xls
This has two columns - Column A, which is ProductCode, and Column B which is WarehouseLocation
This file is a list of all my products, and where they are stored in the warehouse.

I then have a second workbook called Orders.xls
this is automatically generated from various vendors.
This has many columns, but the column relevant here is "ProductCode"

What i need to do, is to run a macro, and have it add a column to the end of my Orders.xls workbook, and populate this with the relevant WarehouseLocation (from Warehouse.xls) based on the value of the Orders.xls ProductCode.

Any ideas where i start?
Im familiar with both visual basic, and SQL (im a dba) but i've never done any vba work as such.

any help, or pointers in the right direction would be great!

Regards,

Alex

 

[SkipVought]

Hi,

You could use VLOOKUP to the Warehouse range.

You could write a function that takes the ProductCode as an argument and returns WarehouseLocation, using ADO. Example...

Sub GetWarehouse(sProdCode as string) as string
    Dim sConn As String, sSQL As String
    Dim rst As ADODB.Recordset, cnn As ADODB.Connection
    Dim sPath As String, sDB As String
    
    sPath = ""         'your path
    sDB = "Warehouse"  'your workbook name
    
    Set cnn = New ADODB.Connection
    
    sConn = "Provider=MSDASQL.1;"
    sConn = sConn & "Persist Security Info=False;"
    sConn = sConn & "Extended Properties=""DSN=Excel Files;"
    sConn = sConn & "DBQ=" & sPath & "\" & sDB & ".xls;"
    sConn = sConn & "DefaultDir=" & sPath & ";"
    sConn = sConn & "DriverId=790;MaxBufferSize=2048;PageTimeout=5;"""
    
    cnn.Open sConn
    
    Set rst = New ADODB.Recordset
    
    sSQL = "select WarehouseLocation From [Sheet1$] where ProductCode ='" & sProdCode & "'"
    
    On Error Resume Next
    
    With rst
        .Open sSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText
        
        If Err.Number = 0 Then
            .MoveFirst
            Do Until (.EOF)
                GetWarehouse = GetWarehouse & rst(0)
                .MoveNext
            Loop
        Else
            Err.Clear
            GetWarehouse = "NONE"
        End If
        .Close
    End With
    cnn.Close
    
    Set rst = Nothing
    Set cnn = Nothing

End Sub

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[Bong]

I then have a second workbook called Orders.xls
this is automatically generated from various vendors.

Does this mean that there are several instances of Orders.xls? Is Orders.xls just appended by the vendors or created anew each time? If the latter, then you have to have the macro in Warehouse.xls.

Let's say that's the case. Then in Warehouse.xls, you'll need to open Orders.xls:
Workbooks.Open (fnam)
name2 = Workbooks(Workbooks.Count).Name, where fnam is Orders.xls or whatever name it is (if there are multiple instances).
Now you'll have 2 open workbooks: thisWorkbook and workbooks(name2). You want to add a column to the (only?) sheet in workbooks(name2). First you need to know where the existing data ends. Will you always know? Let's say you don't but that row 1 is populated contiguously from "A" to whatever column is the last one used. Then the column (number) you want to populate is:
workbooks(name2).sheets(1).cells(1,1).end(xltoRight).column + 1, assuming that Sheet1 is the sheet you want in Orders.xls.

Now I suppose you want to march down the rows of the column in Orders.xls (workbooks(name2)) where the value in row 1 is ProductCode, and search Warehouse.xls (thisWorkbook) for that value in column x and populate the appropriate column in workbooks(name2) with the value in column y (of thisWorkbook).

_________________
Bob Rashkin

 

[alexjamesbrown]

hi,

thanks for your reply.

i've attached the 2 workbooks.
data.xls is the orders file, and warehouse.xls is the "lookup" table... basically warehouse contains a list of product codes and warehouse locations.

what i want to be able to do, is load data.xls, and load the macro, so that it adds a column "warehouse location" and populates that column with data from the warehouse.xls file, based on the SKU from data.xls