performance question

[Gary Keramidas]

i have a summary workbook that gets data from 12 users workbooks, the
summary workbook has 12 sheets (one for each month) all in the same folder
on a network drive. right now i just have formulas as links to all of the
workbooks. there are 12 formulas like this on each of the 12 sheets in the
summary workbook
example

='N:\My Documents\Excel\RECCU\FSA\[April.xls]Branch'!C43+'N:\My
Documents\Excel\RECCU\FSA\[Celia.xls]Branch'!C43+'N:\My
Documents\Excel\RECCU\FSA\[Darlene.xls]Branch'!C43+'N:\My
Documents\Excel\RECCU\FSA\[Dawn.xls]Branch'!C43+'N:\My
Documents\Excel\RECCU\FSA\[Kathy.xls]Branch'!C43+'N:\My
Documents\Excel\RECCU\FSA\[Kim.xls]Branch'!C43+'N:\My
Documents\Excel\RECCU\FSA\[Kristin_T.xls]Branch'!C43+'N:\My
Documents\Excel\RECCU\FSA\[Maria.xls]Branch'!C43+'N:\My
Documents\Excel\RECCU\FSA\[Nicci.xls]Branch'!C43+'N:\My
Documents\Excel\RECCU\FSA\[Nicole.xls]Branch'!C43+'N:\My
Documents\Excel\RECCU\FSA\[Roberta.xls]Branch'!C43+'N:\My
Documents\Excel\RECCU\FSA\[Sue_D.xls]Branch'!C43

someone said i may be faster to use code. well, i have all of the loops and
everything works fine. i created the same formula using vb that i have above

vb formula:

ActiveCell.Formula = "=[April.xls]Branch!" & MyRange & " +
[Celia.xls]Branch!" & MyRange & "" & _
" +[Darlene.xls]Branch!" & MyRange & " +[Dawn.xls]Branch!" & MyRange & ""
& _
" + [Kathy.xls]Branch!" & MyRange & " + [Kim.xls]Branch!" & MyRange & " +
[Kristin_T.xls]Branch!" & MyRange & "" & _
" + [Maria.xls]Branch!" & MyRange & " +[Nicci.xls]Branch!" & MyRange & ""
& _
" + [Nicole.xls]Branch!" & MyRange & " +[Roberta.xls]Branch!" & MyRange
& " + [Sue_D.xls]Branch!" & MyRange & ""

it has to do this routine 12 times to create all the formulas for each of
the 12 sheets in the summary workbook, depending on the month they choose.

is there a faster way to get the data out of the 12 workbooks than creating
formulas like i did?

 

[abcd]

you must remember with a formula, you also have a link (since
something change in a book, your formula gives the new value)

If you need the value, you may evaluate the string, without writting
it first in a cell (vba function evaluate)

But, if this link is precious, the formula is probably the better way.


Whatever, you should look on the web the way to list a folder, so you'll
be abble to construct this formula with a loop (and not the explicit
names). I don't know the rest of the code, just a suggestion that might help

 

[Gary Keramidas]

thanks for the reply. there are other files in the folder, so i have no idea
how i would differentiate between the ones i need and the ones i don't. when
i tried the evaluate option. i just got #value in the cells, unless i had
the workbook physically open. then it worked.

 

[abcd]

well, ok. If you can not group your files in a reserved folder, you may
add a specific mark in their names.

Example name becomes _SYNTH_name.cls or name_MARKER.xls
or anything of your choice.

Then there's a difference with other books.

for example when reading the names, just check that
left( filename , 6) = "_SYNTH_"

 

[Gary Keramidas]

that may work. all of the files i don't need to link to, all have at least
one space in the file name. the ones i link to, are all continuous to the
file extension. a couple may have an underscore in the name, but no spaces.

 

[abcd]

If InStr(1, Filename, " ", vbTextCompare) > 0 Then
'...
End If