Hey hey people.
I am working on an excel macro which will take data out of 21 different workbooks, various worksheets from each workbook (about 50 distinct data points and a couple of graphs) and paste them onto a word document using a word template as a reference. The version I have where I copy data from only one document takes about 11 seconds to run, and I am wary this time may increase at a higher rate than linearly (perhaps having multiple workbooks open) . I am wondering between two choices I have come up with for how to extract data from each workbook:
(1) Use an "active workbook" method
(2) "Fully qualify my references", as in, specify
workbooks("workbook_name").worksheet("worksheet_name".range("variable_name_or_cell_location")
each time
I have been warned against using "active workbook", but in this specific case I would only need to change which workbook is active 21 times, and then VBA wouldnt have to be told each time which workbook Im referring to. I understand that active workbook has its disadvantages, but in terms of pure speed is there a large difference in these methods? I would rather not have to build both methods and then time them as this would take a lot of time for someone new like me
:^)
I am working on an excel macro which will take data out of 21 different workbooks, various worksheets from each workbook (about 50 distinct data points and a couple of graphs) and paste them onto a word document using a word template as a reference. The version I have where I copy data from only one document takes about 11 seconds to run, and I am wary this time may increase at a higher rate than linearly (perhaps having multiple workbooks open) . I am wondering between two choices I have come up with for how to extract data from each workbook:
(1) Use an "active workbook" method
(2) "Fully qualify my references", as in, specify
workbooks("workbook_name").worksheet("worksheet_name".range("variable_name_or_cell_location")
each time
I have been warned against using "active workbook", but in this specific case I would only need to change which workbook is active 21 times, and then VBA wouldnt have to be told each time which workbook Im referring to. I understand that active workbook has its disadvantages, but in terms of pure speed is there a large difference in these methods? I would rather not have to build both methods and then time them as this would take a lot of time for someone new like me
:^)