J
John T Ingato
I have written an add-in ( mostly for learning) that allows the user, in any
worksheet, to right click on a zipcode or a city name and have the correct
match returned depending on which was entered(either city and State or a
matching Zipcode).
PS. I hope to port it to Word eventually.
I originally set up the add-in to contain all the searchable worksheets
separated by 10000's for example
Worksheet names in the add-in:
"00000", "10000", ... "90000"
I would qualify the Zipcode by assigning the "SearchSheetName" to the
appropriate sheet and search through it. I worked excellent when the user
entered a Zipcode. But when I searched for a city name, I had to search
through all sheets to find one, or possibly many matches. This scenario
operated properly, however the search completed in 1.67 seconds. Not very
efficient.
In an attempt to speed up the search, I sorted through and created and added
worksheets "A" - "Z" for searching for a city name. This was much faster at
..00985 seconds.
The problem now was that everytime the user opened a workbook, the add-in
loaded up, which included 38 worksheets, each containing 1000's of rows of
data. I assume this would be an annoyance to any user.I took over 5 seconds
just to open a blank workbook
So my last attempt, prior to this post, was to save each sheet as a separate
workbook. The idea being the add-in would load instantaneously, and only the
workbook needed would be opened for searching. As you experts know, the
opening and closing of each workbook to more time then the original scenario
with each search taking approx. 2.2397 seconds... much longer for a city
search.
The question ??? - What is the best way to alleviate both overhead issue. I
know a database setup would probably solve this, but that is a whole new
learning curve - (any assistance would be appreciated)
Short of creating a databace ref, would wring and opening text files be
faster? Can I just search through worksheets or text files without opening
them?
Any help would be greatly appreciated
John
worksheet, to right click on a zipcode or a city name and have the correct
match returned depending on which was entered(either city and State or a
matching Zipcode).
PS. I hope to port it to Word eventually.
I originally set up the add-in to contain all the searchable worksheets
separated by 10000's for example
Worksheet names in the add-in:
"00000", "10000", ... "90000"
I would qualify the Zipcode by assigning the "SearchSheetName" to the
appropriate sheet and search through it. I worked excellent when the user
entered a Zipcode. But when I searched for a city name, I had to search
through all sheets to find one, or possibly many matches. This scenario
operated properly, however the search completed in 1.67 seconds. Not very
efficient.
In an attempt to speed up the search, I sorted through and created and added
worksheets "A" - "Z" for searching for a city name. This was much faster at
..00985 seconds.
The problem now was that everytime the user opened a workbook, the add-in
loaded up, which included 38 worksheets, each containing 1000's of rows of
data. I assume this would be an annoyance to any user.I took over 5 seconds
just to open a blank workbook
So my last attempt, prior to this post, was to save each sheet as a separate
workbook. The idea being the add-in would load instantaneously, and only the
workbook needed would be opened for searching. As you experts know, the
opening and closing of each workbook to more time then the original scenario
with each search taking approx. 2.2397 seconds... much longer for a city
search.
The question ??? - What is the best way to alleviate both overhead issue. I
know a database setup would probably solve this, but that is a whole new
learning curve - (any assistance would be appreciated)
Short of creating a databace ref, would wring and opening text files be
faster? Can I just search through worksheets or text files without opening
them?
Any help would be greatly appreciated
John