Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
performance question
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? -- Gary |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
performance question
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
performance question
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. -- Gary "abcd" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
performance question
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_" |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
performance question
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. -- Gary "abcd" wrote in message ... 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_" |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
performance question
If InStr(1, Filename, " ", vbTextCompare) 0 Then
'... End If |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Performance Ratings VBA help | Excel Discussion (Misc queries) | |||
Performance | Excel Discussion (Misc queries) | |||
Performance IF(IF or IF(AND | Excel Worksheet Functions | |||
Spreadsheet Performance | Excel Programming | |||
Bad performance | Excel Programming |