Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening a 2nd workbook to get data from, need an easier way
I am tring to open a 2nd workbook that contains inventory data that I then
sort and look up the inventory. How can I refrance the 2 workbooks data. Here is some of the programming I have been working on. I am need a beginning of how to keem the 2 workbooks straight you would be able to see what I am trying to accomplish with the rest of the program Sub Bulding() ' ' ChDir "M:\WALMART_BUILDTO\" Set wkb = Workbooks("Saturdaybuildto.xls") 'MAIN FILE NAME FileName = Application.GetOpenFilename 'OPENS A FILE WITH INVENTORY DATA profile = FileName Do While (1) charposition = InStr(FileName, "\") If charposition 0 Then FileName = Mid(FileName, charposition + 1) Else Exit Do End If Loop MsgBox "you selected" & FileName 'Set onhandwkbs = Workbooks(FileName) THIS COMMAND WOULD NOT WORK 'OPEN INVENTORY DATA FILE Workbooks.Open FileName:= _ profile Range("A16:G678").Select Selection.sort Key1:=Range("B17"), Order1:=xlAscending, Key2:=Range("C17" _ ), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom 'SORTS THE INVENTORY DATA 'onhandwkbs = ActiveWorkbook.Name wkb.Activate ' active MAIN file Order = Range("D3") ' store = Cells(3, 1).Value Set exps = ActiveWorkbook.Sheets("walmart") 'Loop up upc to find inventory Set lookupupc = Range("A701:A791") 'A701:791 fupc = lookupupc.Cells(1).Row lupc = fupc + lookupupc.Rows.Count - 1 For upcrows = fupc To lupc If ((Cells(3, 3).Value) = (Cells(upcrows, 1).Value)) Then upc = Cells(upcrows, 2).Value 'Round = Cells(upcrows, 3).Value End If Next upcrows onhandwkbs.Activate 'activate one hand file Set lookupon = Range("b17:b800") fon = lookupon.Cells(1).Row lon = fon + lookupon.Row.Count - 1 For onrows = fon To lon If (upc = Cells(lookupon, 3).Value & store = Cells(lookupon, 2).Value) Then onhand = Cells(lookupon, 6).Value wkb.active Range("d3") = (Cells(3, 2).Value) - onhand profile.active End If Next onrows |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Easier way of adding new sheets to workbook | Excel Programming | |||
Easier way of adding new sheets to workbook | Excel Programming | |||
Easier way of adding new sheets to workbook | Excel Programming | |||
Getting data from a workbook without opening it | Excel Programming | |||
Pull Data from a closed workbook, without opening it. | Excel Programming |