Excel Hints

Results 1 to 14 of 14

Thread: Solved: Cannot access individual columns because table has mixed cell widths

  1. #1

    Solved: Cannot access individual columns because table has mixed cell widths

    Hiyas

    I have recently written some code that reads figures from documents in a Lotus Notes database, does some simple calculations with them and exports the results to a table in a Word document. Every now and then I get a Notes error message saying:

    "Microsoft Word: Cannot access individual columns in this collection because the table has mixed cell widths".

    The infuriating thing is that this appears from nowhere. I can use the export plenty of times with no problems and then next time I come back to it, it gives me this message. If I leave it for a while, there's a very good chance it'll go away again. If I comment out the lines that set the column widths, it solves it every time but of course the table is now out of proportion.

    Here's the basic setup of the Word doc in the code:

    [VBA]'create Word document
    Set wdApp = CreateObject("Word.Application")
    Set worddocument = wdApp.Documents.Add()
    wdApp.visible = True
    worddocument.pagesetup.Orientation = wdOrientPortrait

    With wdApp

    .selection.ParagraphFormat.Alignment=1
    .selection.font.underline=False
    .selection.font.bold=True

    .selection.typetext("Total Costs for Current Projects")
    .selection.TypeParagraph
    'and so forth...

    'create 3-column table to with catcount+2 rows (including title and total rows)
    Set TableObj = .selection.Tables.Add(wdApp.Selection.Range, catcount+2, 3)

    'set all column widths as appropriate
    TableObj.Columns(1).Width = 260 'commenting out these 3 lines always fixes it
    TableObj.Columns(2).Width = 90
    TableObj.Columns(3).Width = 90

    TableObj.cell(1,1).select[/VBA]
    I have no idea why it works sometimes and not others. I'm not changing anything and I'm using the same test data every time so why is it so temperamental? It was working fine this morning but as I write this, it's gone haywire again. Help! I know Notes is sometimes funny with VBA...can I just put it down to that? I would like to be able to guarantee it won't happen again though.

    Any suggestions appreciated. Thanks!

  2. #2
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,290
    Location
    Hi muttleee,

    As you've just created the table, that shouldn't happen. I don't know any way to create a table with mixed cell widths in a column - it's easy enough to change an existing table, but not to create a new one that way.

    When you get this error what does the table look like?
    Enjoy,
    Tony

    ---------------------------------------------------------------
    Give a man a fish and he'll eat for a day.
    Teach him how to fish and he'll sit in a boat and drink beer all day.

    I'm (slowly) building my own site: www.WordArticles.com

  3. #3
    Hi there

    Curious... This is the sort of thing that would happen if you add a new table when your selection (cursor) is directly below an existing table since this has the effect of appending the existing one. Your object reference "TableObj" would then incluce the whole table, with the original part already at 260, 90, 90 and the new part at the equal default sizes, hence the message.

    That said, I don't see how that can happen with your code as posted and I just tested it running from Excel without errors

    The only thing I can think of is that there's something happening in the
    'and so forth...
    part that you didn't post that's causing an issue ???
    K :-)

  4. #4
    I have posted another thread under " 'flexible' table ", although unsolved.
    Attached is my code and I have tested it is possible to change the column width when creating a table.
    Thanks.
    [vba]
    Public objApp As Word.Application
    Public Tbl1 As Table

    Private Sub Command1_Click()

    On Error Resume Next
    Set objApp = GetObject(, "Word.Application")
    If objApp Is Nothing Then
    Set objApp = CreateObject("Word.Application") End If
    With objApp
    .Documents.Add , , , True
    .Visible = False
    End With

    For intCounter1 = 1 To 3
    If intCounter1 = 1 Then
    Set Tbl1 = objApp.ActiveDocument.Tables.Add _
    (Range:=Selection.Range, NumRows:=1, NumColumns:=2)
    Tbl1.Columns(1).Width = 100
    Tbl1.Columns(2).Width = 100
    Else
    Selection.MoveDown Unit:=wdLine, Count:=1
    Selection.InsertRows 1
    End If

    Tbl1.Cell(intCounter1, 1).Range.Text = "Table1 - col1" & intCounter1
    Tbl1.Cell(intCounter1, 2).Range.Text = "Table1 - col2"


    Next


    objApp.ActiveDocument.SaveAs FileName:="c:\test1.doc"

    objApp.Quit False
    Set objApp = Nothing

    MsgBox "done"
    Set Tbl1 = Nothing

    objApp.Selection.MoveEnd Unit:=wdTable, Count:=1 _
    '<- move to end of table
    objApp.Selection.MoveDown Unit:=wdLine, Count:=1, Extend:=wdMove _
    '<- move foward 1 line
    End Sub



    [/vba]

  5. #5
    Hi all,

    Thanks for all the replies so far. To answer some of the points raised:

    Tony: the code runs fine up to the point where it creates a Word document with an empty 3-column table on it where the 3 columns are of equal width. When I run it through the debugger, the code stops executing at the first line that tries to amend a column width, ie
    [VBA]TableObj.Columns(1).Width = 260[/VBA]

    Killian: the 'and so forth...' bit that I mentioned in my original post is just code that prints some text at the top of the page before the table is printed, so I can't imagine it's doing any harm.

    The other thing I notice is that when I run the code and get this error, it seems to take longer for the table to appear that it does when it's working properly. Not sure if that is significant or not.

    Mr. Doubtfire: I don't have anything in my code at the minute like these lines from your code:
    [VBA]Set objApp = Nothing
    Set Tbl1 = Nothing [/VBA]

    Could that be causing a problem? Having said that, the thing that drives me up the wall is that this my code WAS working fine and now isn't. It's the unpredictability that's so annoying! I have 3 similar agents that produce similar 3-column tables for 3 different scenarios. Either they all work or none of them does. If anyone has any more ideas, I'd love to hear them. Thanks!

  6. #6
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,290
    Location
    Hi muttleee,

    When the code stops, what does the table look like?

    Even better, when the code stops could you save the document immediately and post it here?
    Enjoy,
    Tony

    ---------------------------------------------------------------
    Give a man a fish and he'll eat for a day.
    Teach him how to fish and he'll sit in a boat and drink beer all day.

    I'm (slowly) building my own site: www.WordArticles.com

  7. #7
    Hi Tony. Here goes - I have never uploaded an attachment before but there's a first time for everything.

    As you can see it's just a regular table with 3 columns of equal size. The first row should be a title row with headings printed in bold and the rest should be filled with data, including the total of each column in the last row. When the code stops, the cursor is in the top left cell, which is presumably what has to happen before the column can be resized.

  8. #8
    Here's the latest: it's working again but I have no idea why!

    All I have done today is comment out the lines that set column widths and uncomment them again....and that was 2 and a half hours ago. It didn't make any difference then or during the numerous tests I have done in the time in between up to now (12.00). Now all of a sudden it starts working again. I tried all 3 agents on 2 different copies of the same database and they're all working fine. Gahhhh!!

  9. #9
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,290
    Location
    The cursor doen't have to be anywhere particular - it just happens to be put there by the adding of the table.

    There is nothing I can see in that document that should stop the code working. From what you say it sounds like something either in Notes or environmental is the cause, but I have no idea what. Without Notes, I can't check much more out, I'm sorry.
    Enjoy,
    Tony

    ---------------------------------------------------------------
    Give a man a fish and he'll eat for a day.
    Teach him how to fish and he'll sit in a boat and drink beer all day.

    I'm (slowly) building my own site: www.WordArticles.com

  10. #10
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,290
    Location
    Just thinking aloud really ...

    When you say you have three different agents running, do they run at the same time - and do they try to speak with the same instance of Word at the same time? In other words could one be intyerfering with another?

    Or might there be something residual from an earlier failed attempt interfering somehow?

    Is Word running at the same time?

    Does closing Notes and/or Word and/or Windows change anything?
    Enjoy,
    Tony

    ---------------------------------------------------------------
    Give a man a fish and he'll eat for a day.
    Teach him how to fish and he'll sit in a boat and drink beer all day.

    I'm (slowly) building my own site: www.WordArticles.com

  11. #11
    The agents don't run simultaneously. Each produces a separate report when the user clicks the appropriate menu item in the application's toolbar. They do however use the same variable names for the instance of Word and the table object in the document. As I mentioned in an earlier post, neither of these is set to Nothing at the end of the code, so I began to wonder if instances of Word and the table were being left active and then accessed again next time one of the agents runs. However, this does not explain why it works a lot of the time...especially when you consider that all 3 agents refer to tables with columns of identical widths.

    It then occurred to me that the basis of this code was taken from a similar agent on a different database altogether - which again used the same variable names but different table layouts and column widths. Maybe this other agent was being run at some point and setting up different sized tables which somehow interfered with the ones in question. However, this original code runs on another server entirely so I would guess it shouldn't interfere. Also, I deliberately triggered this initial code and tried the newer one straight afterwards and again it worked fine.

    The agents start Word when they need it - it's not running in the background beforehand.

    The problem was happening on Friday afternoon and it was still happening on Monday morning despite my laptop being switched off all weekend, so restarting Notes, Word or Windows didn't directly help. Having said that, the problem might well have come and gone a couple of times over the weekend if anyone had been around to test it.

    I'm stumped.

  12. #12
    Hi again

    I hesitate to get too excited in case I am speaking too soon but I haven't had any problems with this since implementing a simple piece of code on Monday morning. All I did was put in the following at the end of every piece of code that created a table:

    [VBA]Set session = Nothing
    Set db = Nothing
    Set TableObj = Nothing
    Set wdApp = Nothing
    Set worddocument = Nothing [/VBA]

    I thought if Tony was right about some environmental factor causing problems, then setting everything to nothing might help and it's probably good programming practice anyway. So far, so good. I won't mark this solved just yet but I am cautiously optimistic!

    Thanks for all the input!

  13. #13
    I'm going to mark this as solved now because I haven't had any problems since adding that little snippet of code (in the above post) over a week ago. I hope I haven't spoken too soon! Thanks again for all the suggestions.

  14. #14
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,290
    Location
    Hi muttleee,

    I missed your previous post but you are absolutely correct about setting object variables to nothing being good programming practice and that is especially true in automation. I can well believe that an instance of Word could have been left lying around due to persisitence of an object pointer to it and that this may cause problems.

    Let's hope you have solved it but if not, don't hesitate to come back.
    Enjoy,
    Tony

    ---------------------------------------------------------------
    Give a man a fish and he'll eat for a day.
    Teach him how to fish and he'll sit in a boat and drink beer all day.

    I'm (slowly) building my own site: www.WordArticles.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •