procedural help lease

[Gary Keramidas]

i am just looking for a short description, no code, on how you would proceed
with this task. i have an idea, but just wanted some other input before i really
got into it too far.

looking to summarize spice blends. every blend is in a separate workbook whose
name starts with a unique number then has some text description then .xls.

the range of cells for the spices needed for that blend is always the same in
each workbook.

so in the summary workbook, if the user wanted to schedule a particular blend,
they would just enter the numerical part of the filename then i would just find
and open it.

then i would need to populate the columns with the spices and the number of
pounds needed.

with multiple blends scheduled, there would be some unique spices and some that
are reused, like salt, for example. so i would need to add to the number in that
column, but if the spice hasn't been used yet, i would need to add a column for
that spice.

would you import all of the blends for the week in a "work" sheet, sort, then
delete the dupes and then populate the actual schedule sheet?

just looking for some ideas here, i know it's not much to go on.

thanks for reading.

 

[Gary Keramidas]

i guess i should ask one other thing.

what would be the best way to open a file based on a partial filename? the known
partial part is always the beginning of the filename.

 

[Guest]

I would do it something like this pseudo code I would think

dim spcRng as Range, spNum as Long
dim rcpeRng as Range, cell as Range
dim bk as workbook
worksheets("DataEntry")
set spcRng = .rows(1).cells
spNum =.Range("B9").Value
end with
set bk = workbooks.Open(spNum & ".xls")
with bk.Worksheets(1)
set rcpeRng = .Range("B1",.Cells(1,256).End(xltoLeft))
end with
for each cell in rcpeRng
res = application.match(cell,spcRng,0)
if iserror(res) then
res = spcrng.cells(1,"IV").End(xltoLeft)(1,2).column
spcRng.Parent.Cells(1,res).Value = cell.value
spcRng.Parent.Cells(2,res).Value = cell.offset(1,0).value
else
spcRng.Parent.Cells(2,res).Value = spcRng.Parent( _
2,res).Value + cell.offset(1,0).value
end if
Next

 

[Guest]

You might want to try the Consolidate feature from the Data menu

It allows you to bring together sheets and sum like fields
so your salt fields will be added and any unique fields will be added to the
list of spices.

it can be done manually or programatically

hope this helps

David

 

[Gary Keramidas]

thanks for your time tom. i'll check it out.

 

[Gary Keramidas]

thanks for the idea

--


Gary

You might want to try the Consolidate feature from the Data menu

It allows you to bring together sheets and sum like fields
so your salt fields will be added and any unique fields will be added to the
list of spices.

it can be done manually or programatically

hope this helps

David

 

[Gary Keramidas]

ended up just using this to get the file name to open:
FileToOpen = Dir(fPath & fName & "*.xls")