[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Remove rows from a table and restore them back again with Word VBA

Posted on 2011-10-29
10
Medium Priority
?
368 Views
Last Modified: 2012-05-12
    I have a VBA program in Word that displays a table with six columns (two of which are hidden) and about a hundred rows. Its function, among other things, is to present vocabulary words to students in both French and English, one set in each row. Because of some subroutines, the width of the columns may change from time to time.
     I want to let students work with a sub-set of, say,  ten or twenty words the table at a time. Currently, I’ve written a sub that lets students select the rows they want to work on. The sub then cuts unwanted rows above and below the selection, pastes them in another throw-away document where I convert the table to text, replace the paragraph marks with a unique symbol so that the whole is one long string. Then I save the string as a Document Variable in the original document. That removes the unwanted rows from the table.
     When the student wants to restore the cut words, I get the deleted rows from the Document Variable, restore the paragraph marks, convert the strings to a table, and then go through the table giving it the character formatting it lost when I first converted the table rows into a string. I then have a new table just beneath the original table. I would like it if the table with the deleted rows could then automatically join the original table, but the column widths between the two tables are different. Any routine that deals with columns fails with a message saying that the table columns have varying widths.
     
     My questions: Is there some routine I could write that would ensure that the column widths are the same between the two tables? If so, would that automatically merge the two tables?
     
     Or is there a better way to solve my basic problem? As a kludge, I am currently selecting the rows to hide, changing the point size to a minimal 3 points, designating the rows as hidden, and storing them all at the bottom of the table so that they remain in the table but are unreadable. It looks sloppy. See the picture of the table below, where the “hidden” rows are at the end and extend down further than the picture shows. Steve Jobs would certainly not approve of that kludge unless there were absolutely no other way to solve the problem.
     What is the best way to solve this problem? Note: To convert the original table into strings, put the other strings below, then convert the whole back to a table then takes too long as we have to go through hundreds of character formatting changes that right now I do on just the deleted items.
     Thanks for any and all help!
     JRA
 Picture of "hidden" rows at the bottom of a table
0
Comment
Question by:JohnRobinAllen
  • 5
  • 5
10 Comments
 
LVL 46

Expert Comment

by:aikimark
ID: 37052736
Does the student select a number range?  Your screen shot shows consecutive numbers.

What kind of editing does/might the student do?
0
 

Author Comment

by:JohnRobinAllen
ID: 37052931
The student in this case has selected items 10 through 19 out of a total list of about 100 words. My current "solution" to limit the list to the words the student originally selected means that I must move items 1 through 9 to the bottom of the table and I reformat items 20 to the end of the rows (including the rows I moved there from the top of the list). The reformatting makes the rows impossible to read but they are certainly not "hidden."

The student then works with just the visible rows until she wants to restore the hidden rows. The numbers in column one are  based on the original order of the items that come from several different articles that explain French vocabulary. There is a different list for each article.

The student can change the order of the words in several ways, sorting on any of the visible columns or sorting everything in an arbitrary order. If she sorts on column 1, the words return to their original order.

The student may not edit the items, but she can make either the French or the English words disappear and test herself on what she remembers of the different translations. She can also take a written test either from French to English or from English to French, whereby the computer chooses questions randomly and asks the student to write her translation, that the computer judges.

I was thinking of having the computer create a companion document to hold the hidden rows until it is time to return them to the original file. That would avoid the messy current solution and not require me to make special arrangements when the student sorts the words on the different columns (to avoid having the hidden rows be a part of the sort).

That solution of storing the data in a second document still gives the problem of how to merge the two tables back together when the student wants to work with a different selection. I cannot simply merge two tables: the column widths vary between the two tables. I'm thinking of writing a routine that will make the width of each column in table 2 be the same as the width of the equivalent column in table 1, but I do not know whether Word will intervene and decide for itself just how wide each column should be. Even if I succeed in making all the columns equal in width, will the tables merge correctly?

Another solution is to use my present solution but to avoid having the table use fancy formatting from Word. That might conceal better the hidden rows at the bottom. I would like to keep the colorful formatting, but I do not know how to make the "hidden" rows be just colorless and therefore invisible.

I am open to any suggestions or comments.

Thanks for your interest.

JRA


 
0
 
LVL 46

Expert Comment

by:aikimark
ID: 37053015
@JRA

I think we need to start with the application criteria (what do you need)

Once we know the criteria, make user interface decisions, based on what software you have (Word, Excel, etc.)
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Author Comment

by:JohnRobinAllen
ID: 37057566
I use Word and program it with VBA.
     I have the problem virtually solved if I move the rows to delete to the bottom of the table, do not format the table and hide all the borders. The hidden cells disappear. The result is what you see in the image below.
     What I would like to do is to format the visible portion of the table, but when I apply a Word 2010 Table Style to the table, it affects all the rows and they appear as you see in the image I put with comment 27422085 above.
     I'll ask that question in another post, but if anyone here can solve it, I can assign the points with this question.

     JRA Table with no formatting and about ninety rows concealed at the end
0
 
LVL 46

Expert Comment

by:aikimark
ID: 37057866
I have an idea about imbedding an Excel worksheet in your Word document and then hiding/showing the worksheet rows.  I will need to play with this before I recommend anything.
0
 

Author Comment

by:JohnRobinAllen
ID: 37060542
Thank you for your help. I am a bit confused as to why an Excel spreadsheet will work better than a Word table. I use a spreadsheet for calculations, but there are no calculations needed here. As for the ability to hide rows in a spreadsheet, I am hiding rows in my table in the above kludge.
     For my own part, I suspect that I will not be able to format only a part of a table, and so I am giving up on trying to do that. I am satisfied with my current solution, as shown in comment 37057566, but I am open to any other suggestions that will improve on that.

JRA
0
 
LVL 46

Expert Comment

by:aikimark
ID: 37060613
Pardon my French (when you see the Excel data).

The workbook contains 33 rows.  I've hidden all, but rows 10-17.
sourcetable.xls
FrenchQuiz.doc
0
 

Author Comment

by:JohnRobinAllen
ID: 37061585
This is most interesting. Two questions:
     1. The student will initially see the entire table. What VBA code did you use to let the student select the rows she wants to see, and then we show that extract alone in the Word document?
     2. If a student then wants to "delete" an item in the exposed extract, can she "delete" it (and other words as she works through the list) but later restore the "deleted" words without restoring the entire table?

     Thank you very much for what you have done. Your method would allow me to format the visible table. Since I have already written the code for my solution, and since my "solution" does not involve using a second file, I'll probably stick with my "solution" that handles the above two problems.
     One further comment: your solution requires using an Excel document. Could the second file be equally as well  just a Word document with the table?

     JRA
     
0
 
LVL 46

Accepted Solution

by:
aikimark earned 2000 total points
ID: 37062198
@JRA

1. This was a proof-of-concept.  I hid the rows manually.
2. I'm not sure I understand your use of "delete"

>>Could the second file be ... a Word document with the table?
No and Yes.

No. Not if you want to truly hide the row.

Yes. It is possible to use a Word table as a data source.  This is one of the possible mail merge configurations.

=============
I was also going to post a proof-of-concept document with a user form.  However, it looks like you are wedded to the Word table manipulation, so I'll leave it for some other question.

I don't know what you've written, so I can't opine on the efficacy of your implementation.  My gut instinct is to extract (not delete) row data from a master table for the quiz and then merge the data back into the master table, bases on the number column.  Think of a check-out/check-in scheme.
0
 

Author Closing Comment

by:JohnRobinAllen
ID: 37062261
I think this is an interesting start to a solution that has some advantages over my own solution. However, in case anyone else is interested in an alternative to the Aikimark solution, I will post my own here in the near future.
    For the moment, however, I have a deadline to meet by the end of today, so I will have to post my solution a bit later.
    Many thanks for the discussion.
    JRA
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A few years ago I was very much a beginner at VBA, and that very much remains the case today.  I'll do my best to explain things as I go in the hope that other beginners can follow.  If you just want to check out a tool that creates a Select Case fu…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question