Opening a 2nd workbook to get data from, need an easier way

[Craig Marburger]

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

 

[Bernie Deitrick]

Craig,

Set Wkb = Workbooks("Saturdaybuildto.xls") 'MAIN FILE NAME
Set OnHandWkbs = Workbooks.Open(Application.GetOpenFilename)

HTH,
Bernie
MS Excel MVP

 

[Dave Peterson]

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 were:
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]

Thanks for your help. It is working.