Allowing user to set what and how many selections?

[Ed]

We work with a lot of engineering documents. Each doc has basically the
same general format, but specifics differ enough betweens sections that a
one-size-fits-all template isn't possible. I can pull all the info out of
the database to create the docs (this must be done through Word VBA; the
database itself isn't capable of doing this). Each report doc is created as
a plain text doc with no formatting.

What I'm trying to do now is figure out the creation of an index table (as
an Excel doc) that each section can customize. The info populating the
index table would be pulled from each report doc as it is created. But
each section's table will be different. For example, one section may want
the data in Blocks 10, 12, and 15A in their index, while Block 15A may not
even exist on another section's report. I'm trying to figure out how to let
one administrator from each section customize their index format - what they
want and how many data blocks they want to retrieve, rather than being the
one person to create an individualized index table for each section, and
customize it every time they change their mind!

I'm considering popping up a modeless UserForm that will allow the user to
select (in typical opened report) the headers of the blocks, the number of
spaces the retrieved data will span, and then enter a title for that data
field. For instance, if the report has
15A. TEMP: 13 deg C
the user can select
15A. TEMP: and click a button on the form, which will set that text as a
search string, then enter 8 spaces (for the retrieved data length) in a text
box, and then enter a title in another text box. All of this search info
could be written into a table saved in a doc associated with the UserForm,
and read back into strings in a Form_Initialize event. This would allow the
user to set up a table as big or small as desired. Then, when each report
doc is created, the code searches for the selected string, then extends the
range the specified number of spaces and selects that text to write into the
Excel table.

Does all this sound like a good way to go? Or am I doing things the hard
way? Is there a better way to consider?

Ed

 

[David]

Does all this sound like a good way to go? Or am I doing things the hard
way? Is there a better way to consider?

Ed

I am an amateur programmer. I program to make my life at the office
simpler. So, having said that, I have spaghetti code. It's ugly,
repetitive, jumps around, and when I have to edit something, it
generally takes me a little while to figure it out. But you know
what, it works. It reduces my task from 20-25 minutes per report to
2-3 minutes. I use it almost every day and I like it.

Sometimes you just have to try things to see how they'll work out.
When I initially set out to write my code, it took me several tries to
find something that suited my needs. It eventually took about three
different versions to get it working. To me, this is the hardest part
of programming, proving it.

Good luck!
David

 

[Ed]

Dave, I know *exactly* how you feel! But that's where I also learned that
sometimes asking a question here can save me either trying to reinvent the
wheel or trying to turn a screw with a hammer. The only reason I've gotten
myself into any situation where someone can be called my "user" or
"customer" is they like something I've cobbled together for myself and want
to see if I can adapt it for them, too. Most of the time, it means turning
my hard-coded paths into user-defined parameters, like I need to see if I
can do now. I've already got the shell of this working for me; it's just
making it so someone else can set their own parameters and the retrieve them
for next time.

Ed

 

[David]

... trying to turn a screw with a hammer.

I've been guilty of this, literally!

I guess the point I'm making is that if time isn't that important,
don't spend too much time making it effecient. I'm sure my code could
be made to run faster, but it really isn't necessary.

But I do understand your point also of asking for directions before
taking the wrong road. But I think you'd have to provide much more
info (such as samples indexes) before anyone would offer any
assistance.

 

[Ed]

But I do understand your point also of asking for directions before
taking the wrong road. But I think you'd have to provide much more
info (such as samples indexes) before anyone would offer any
assistance.

In my first post, I gave the following example:
***************
I'm considering popping up a modeless UserForm that will allow the user to
select (in typical opened report) the headers of the blocks, the number of
spaces the retrieved data will span, and then enter a title for that data
field. For instance, if the report has
15A. TEMP: 13 deg C
the user can select
15A. TEMP: and click a button on the form, which will set that text as a
search string, then enter 8 spaces (for the retrieved data length) in a text
box, and then enter a title in another text box. All of this search info
could be written into a table saved in a doc associated with the UserForm,
and read back into strings in a Form_Initialize event. This would allow the
user to set up a table as big or small as desired. Then, when each report
doc is created, the code searches for the selected string, then extends the
range the specified number of spaces and selects that text to write into the
Excel table.
*****************

Right now, I have the code to search for "15A. TEMP: ", then collapse the
Found range and MoveRight 8 spaces to encompass the "13 deg C", pull that
into a string and write it into Excel. I do that for 12 different data
points right now. The problem is that it's all hard-coded in my macro for
my reports. If I want to give this to another user (which there are other
users who want this, because they can't access their reports like I can),
then each user must have the ability to set their own "search for this"
strings and "reset range this many spaces right or left" parameters, and
give their own titles to the data fields. Right now, I just use a loop with
a counter and a Select Case statement:
Select Case cntLoop
Case 1
strFind = "2. TITLE: "
Case 2
strFind = "15A. TEMP: "
etc.

So is there a way to set this up for any user without stringing spaghetti
from east to west? If no one has anything like this, then I guess I'll
start stringing.

Ed

 

[David]

In my first post, I gave the following example:
***************
I'm considering popping up a modeless UserForm that will allow the user to
select (in typical opened report) the headers of the blocks, the number of
spaces the retrieved data will span, and then enter a title for that data
field. For instance, if the report has
15A. TEMP: 13 deg C
the user can select
15A. TEMP: and click a button on the form, which will set that text as a
search string, then enter 8 spaces (for the retrieved data length) in a text
box, and then enter a title in another text box. All of this search info
could be written into a table saved in a doc associated with the UserForm,
and read back into strings in a Form_Initialize event. This would allow the
user to set up a table as big or small as desired. Then, when each report
doc is created, the code searches for the selected string, then extends the
range the specified number of spaces and selects that text to write into the
Excel table.
*****************

Right now, I have the code to search for "15A. TEMP: ", then collapse the
Found range and MoveRight 8 spaces to encompass the "13 deg C", pull that
into a string and write it into Excel. I do that for 12 different data
points right now. The problem is that it's all hard-coded in my macro for
my reports. If I want to give this to another user (which there are other
users who want this, because they can't access their reports like I can),
then each user must have the ability to set their own "search for this"
strings and "reset range this many spaces right or left" parameters, and
give their own titles to the data fields. Right now, I just use a loop with
a counter and a Select Case statement:
Select Case cntLoop
Case 1
strFind = "2. TITLE: "
Case 2
strFind = "15A. TEMP: "
etc.

So is there a way to set this up for any user without stringing spaghetti
from east to west? If no one has anything like this, then I guess I'll
start stringing.

Ed

Can you read in all section headers and display them to the end user
in a drop down list with multiselect on? The users select the headers
they want, the macro retrieves the data and posts it to the end
report.

Can the section headers be read with a fixed length and then trimmed
using Trim function?

 

[Ed]

Can you read in all section headers and display them to the end user
in a drop down list with multiselect on? The users select the headers
they want, the macro retrieves the data and posts it to the end
report.

Can the section headers be read with a fixed length and then trimmed
using Trim function?

Oh, that would be nice! Unfortunately, these reports vary enough in format
that I can't set up anything ahead of time. I can't set up a list of
headers, because some of those headers may not exist on some reports, and
the search string may not be set up the same way. For instance, the example
of "15A. TEMP: " may be block 23 on another report, and may not be
included at all on others. If I set 20 spaces, say, for the TITLE: , it may
only be allotted 15 spaces on one report, and may have 25 on another.

The more I look at this, the more I think I need to build each one
individually by hand and give the final product to the user. If I give them
something to set up on their own and it screws up, I'm going to be there
fixing it as an individual item anyway! Oh, well - it was a nice thought!

Ed

 

[David]

Oh, that would be nice! Unfortunately, these reports vary enough in format
that I can't set up anything ahead of time.

That was my point. Read each database query on the fly to create the
dropdown. Dynamically create the list of headers with each run.

I can't set up a list of
headers, because some of those headers may not exist on some reports, and
the search string may not be set up the same way. For instance, the example
of "15A. TEMP: " may be block 23 on another report, and may not be
included at all on others. If I set 20 spaces, say, for the TITLE: , it may
only be allotted 15 spaces on one report, and may have 25 on another.

Do all the headers have a colon to mark them? If so, then you might
be able to iterate through the query building a list of headers for
that run. Assuming the header can be found, is the remaining text on
that line the data you need to search for? If so, you might can get
the length with the Len() function

Without seeing a few samples of the raw data and a finished report,
it's difficult to give accurate suggestions.

Feel free to e-mail a copy if you like.

 

[Ed]

That was my point. Read each database query on the fly to create the
dropdown. Dynamically create the list of headers with each run.

Do all the headers have a colon to mark them? If so, then you might
be able to iterate through the query building a list of headers for
that run. Assuming the header can be found, is the remaining text on
that line the data you need to search for? If so, you might can get
the length with the Len() function

I see what you mean. While I could do that, the more I look at this, the
more I realize it would be a monster to do that. The format of the report
I'm looking at right now has over 90 headers. While all the headers have
colons, not all colons are in headers. Some data points that might be
desired for an index sheet are buried under subheadings. There's even some
I have to MoveLeft to get, rather than MoveRight.

No, I think I'm looking at building a custom search-and-destroy table for
each format. But I think if the user gave me a printed copy of a report
with the desired data points highlighted, I could fairly easily and quickly
build the table. That way, there'd be no going back because the
one-size-fits-all didn't and now his index of 2,000 reports is goofy and has
to be redone.

Thanks for the input, Dave, and helping me work through this.

Ed