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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening a 2nd workbook to get data from, need an easier way
Craig,
Set Wkb = Workbooks("Saturdaybuildto.xls") 'MAIN FILE NAME Set OnHandWkbs = Workbooks.Open(Application.GetOpenFilename) HTH, Bernie MS Excel MVP "Craig Marburger" wrote in message ink.net... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening a 2nd workbook to get data from, need an easier way
When you used application.getopenfilename, the complete drive/path/filename was
returned. But then you chopped the drive and path from the string: So if your file we C:\walmart_buildto\somefile.xls You changed filename to: somefile.xls And the .open will fail since you lost the drive/path. I'd do something like: Option explicit 'to force you to declare your variables. Sub Bulding() dim wkb as workbook dim onhandwkbs as workbook dim myFilename as variant 'could return boolean False dim JustFileName as string Set wkb = Workbooks("Saturdaybuildto.xls") 'MAIN FILE NAME myFileName = Application.GetOpenFilename 'OPENS A FILE WITH INVENTORY DATA if myfilename = false then beep exit sub end if 'instrev was added in xl2k charposition = InStrRev(myFileName, "\") justFileName = Mid(myFileName, charposition + 1) MsgBox "you selected: " & justFileName set onhandwkbs = workbooks.open(filename:=myfilename) ..... ====== And I don't like using variables that match argument names. It may not confuse excel, but it confuses me. I don't like: set onhandwkbs = workbooks.open(filename:=filename) Craig Marburger wrote: 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 -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening a 2nd workbook to get data from, need an easier way
Thanks for your help. It is working.
"Craig Marburger" wrote in message ink.net... 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 |
Reply |
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 |