Consulting

Results 1 to 17 of 17

Thread: Solved: sos-bookmark table

  1. #1

    Solved: sos-bookmark table

    hi all!
    i need to extract data from an excl sheet and put it into a table in word.i have been able to do that using the table index but the problem is that the doc itself is organised using a table so that it is not possible for me to get the table index reliably.
    is there any way to name a table and use this name as in tables("tablename")
    instead of tables(index number)?
    alternatively,can i bookmark a table and then use this bookmark to insert data into the table although i m less keen on this solution.
    hope i have made my question clear!
    thanks in advance

  2. #2
    VBAX Mentor
    Joined
    Sep 2004
    Location
    Nashua, NH, USA
    Posts
    489
    Location
    Quote Originally Posted by sangeeta
    hi all!
    i need to extract data from an excl sheet and put it into a table in word.i have been able to do that using the table index but the problem is that the doc itself is organised using a table so that it is not possible for me to get the table index reliably.
    is there any way to name a table and use this name as in tables("tablename")
    instead of tables(index number)?
    alternatively,can i bookmark a table and then use this bookmark to insert data into the table although i m less keen on this solution.
    hope i have made my question clear!
    thanks in advance
    if there's only 1 table in the document, the table index does not change.

    if there's more than one table, create a Table object for each table of interest and work via the Table object for the table.

    check the Object Browser for info on the Table object.

  3. #3
    thanks a lot but i m still not clear on how to do that.could you please help me with an example?

  4. #4
    Knowledge Base Approver
    Space Cadet
    VBAX Tutor sandam's Avatar
    Joined
    Jan 2005
    Location
    London
    Posts
    292
    Location
    [vba]'n = an integer between 1 and the total number of tables in the document

    With Activedocument.Content.Tables(n)

    .Cell(1,1).Range.Text = "This is the first cell in the nth table"

    End With
    [/vba]
    Last edited by sandam; 05-24-2005 at 01:29 AM. Reason: vba tags - doh!
    Nothing annoys a non-conformist more than other non-conformists who refuse to conform to the rules of non-conformity.


    Confused is my normal state of mind


  5. #5
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,291
    Location
    Tables in Documents do not have names; they can only be referenced by index number and they are not, themselves, objects which you can reliably identify. If you cannot guarantee enough about the document to be sure, using a bookmark is by far your best option.

    If you have a bookmark somewhere in the table you can use
    [VBA]ActiveDocument.Bookmarks("BookmarkName").Range.Tables(1)[/VBA]
    to reference it
    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

  6. #6
    I don't think i made myself clear.The problem is that there are so many tables in my doc(in fact the entire doc is within one big table,there are tables within the table) that using the index number is cumbersome and unreliable.That's the reason i thought there should be some other way of accessing the tables,say by using a name or bookmarking them in some way.
    sorry for all the trouble and thanks in advance.

  7. #7
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,291
    Location
    I'm quite prepared to believe that using the index number is unreliable, but cumbersome!

    Anyway, as I said, you can't identify the table in any direct way and therefore you will have to associate something else with it, which you can identify and a bookmark is the simplest. What is your problem with a bookmark?
    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

  8. #8
    Administrator
    VP-Knowledge Base VBAX Guru MOS MASTER's Avatar
    Joined
    Apr 2005
    Location
    Breda, The Netherlands
    Posts
    3,281
    Location
    Hi and Welcome to VBAX!

    Perhaps you can post a little sample of your table document so we can see if these are normal tables or nested ones. And perhaps give some more clues on what you're trying to do so we can be of more assistance?

    Nested or not I'd go for shure with the bookmark (or any other reference) method of Tony! (That's most reliable one I know of)

    If you say your table is so complicated then why do not take another approach:
    * Have your Excel document set up as how you'd like your data to look
    * Possibly name the range for easy retrievel
    * Copy this range and paste it in to Word as table...(This could save you time depending on the full range of your question)

    You could write some code to clean or alter the table afterwards...but don't know enough detail now to answer that!

    Enjoy!
    _________
    Groetjes,

    Joost Verdaasdonk
    M.O.S. Master

    Mark your thread solved, when it has been, by hitting the Thread Tools dropdown at the top of the thread.
    (I don't answer questions asked through E-mail or PM's)

  9. #9
    VBAX Mentor
    Joined
    Sep 2004
    Location
    Nashua, NH, USA
    Posts
    489
    Location
    Quote Originally Posted by sangeeta
    thanks a lot but i m still not clear on how to do that.could you please help me with an example?
    I was hinting at doing somethong loke the following:

    Dim myTable as Word.Table

    ' If you want to work with the 12th table in the document.
    Set myTable = activeDocument.Content.Tables(12)

    Then use myTable to refer to the table where you may have previous used, say:

    Use:

    myTable.Whatever

    instead of

    ActiveDocument.Content.Tables(12).Whatever

    AFAIK, the myTable object will always point to the correct table, even if the table's index changes.

  10. #10
    thanks a ton! i guess i should be able to take it from here.will get back to u all in case of any problem.
    by the way,i m going with the bookmark solution.
    thanks for your time!

  11. #11
    hi all! i bookmarked a cell in my table and i m trying to run the following code from excel sheet:
    Set WordApp = CreateObject("Word.Application")
    Set WordDoc = GetObject("C:\Documents and Settings\sb008078\Desktop\AUTOMATION\Automating word\automation.doc")
    With WordApp
    .Visible = True
    .WindowState = wdWindowStateMaximize
    End With
    With WordDoc
    Selection.GoTo What:=wdGoToBookmark, Name:="bk1"
    Selection.Find.ClearFormatting
    With Selection.Find
    .text = ""
    .Replacement.text = ""
    .Forward = True
    .Wrap = wdFindContinue
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False
    End With
    While Rindex < 11
    Rindex = Rindex + 1
    Cindex = 0
    Selection.MoveDown Unit:=wdLine
    While Cindex < 11
    Selection.TypeText text:=Worksheets("Sheet1").Cells(Rindex, Cindex).Value
    Selection.MoveRight Unit:=wdCell
    Wend
    Wend
    End With

    but i m getting a run time error 438:object does not support this method or property with the following line highlighted:
    Selection.GoTo What:=wdGoToBookmark, Name:="bk1"
    what i can't understand is why is it happening?
    i have used this property on selection object in word doc and it works fine.

    help please! i need to get this done.....

  12. #12
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,291
    Location
    The reason for your error is that you are using the Excel Selection, instead of the Word one. Inside your With you should use .Selection, HOWEVER the Word Selection does not belong to the Document so you should be using With WordApp.

    Although it is unlikely to go wrong, the way you open your Document with GetObject is not guaranteed to use the instance of Word you have created with the CreateObject. As you won't be using the WordDoc object after you make the change above I would suggest doing it this way

    [VBA] Set WordApp = CreateObject("Word.Application")
    With WordApp
    .Documents.Open "C:\Documents and Settings\sb008078\Desktop\AUTOMATION\Automating word\automation.doc"
    .Visible = True
    .WindowState = wdWindowStateMaximize
    .Selection.GoTo What:=wdGoToBookmark, Name:="bk1"
    .Selection.Find.ClearFormatting
    With .Selection.Find
    .text = ""
    .Replacement.text = ""
    .Forward = True
    .Wrap = wdFindContinue
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False
    End With
    While Rindex < 11
    Rindex = Rindex + 1
    Cindex = 0
    .Selection.MoveDown Unit:=wdLine
    While Cindex < 11
    .Selection.TypeText text:=Worksheets("Sheet1").Cells(Rindex, Cindex).Value
    .Selection.MoveRight Unit:=wdCell
    Wend
    Wend
    End With[/VBA]

    As a side note - if you enclose your code in VBA .. /VBA tags it is much easier to read.

    I don't have your document so can't be sure what you have, but I note as I write this that you don't execute the Find, so what is it doing? I would also suggest that you try and avoid using the Selection object at all. If you have a reference to your table you can use either the Cell method or the Rows and Columns properties.
    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

  13. #13
    Administrator
    VP-Knowledge Base
    VBAX Guru MOS MASTER's Avatar
    Joined
    Apr 2005
    Location
    Breda, The Netherlands
    Posts
    3,281
    Location
    Hi,

    Like Tony mentioned there are better ways of coding this using a range and the proper table navigation.

    I notice that you're using Late Binding (CreateObject, Dim as Object etc..)
    Is there a specific reason for this? (Version problems..etc..)

    If you would use Early Binding from Excel (Set reference to Word Object library) then you could use Intellisense to help you coding from Excel and Execution time will be faster.

    Would you like a version of your code in early binding?
    _________
    Groetjes,

    Joost Verdaasdonk
    M.O.S. Master

    Mark your thread solved, when it has been, by hitting the Thread Tools dropdown at the top of the thread.
    (I don't answer questions asked through E-mail or PM's)

  14. #14

    Smile

    Thanks a lot! yes, the early bindng version would be of great help.
    thanks tony!

  15. #15
    Administrator
    VP-Knowledge Base
    VBAX Guru MOS MASTER's Avatar
    Joined
    Apr 2005
    Location
    Breda, The Netherlands
    Posts
    3,281
    Location
    Hi,

    Sure here it comes.

    Because I don't know exactly how you want your table filled I'll make this an example of how to fill a Word table from excel using an existent table.

    Before you run it check the reference in the VBE. (Read code remark)
    [VBA]
    Option Explicit

    Sub FillWordTable()
    'Before Run Go to Tools/Reference and check the right reference to:
    'Microsoft Word 11.0 Object Library (if you have xp it will be 10.0 / 2000 9.0 etc...)

    Dim oApp As New Word.Application
    Dim oDoc As Word.Document
    Dim oTable As Word.Table
    Dim sPath As String, iCol As Integer, iRow As Integer

    sPath = ThisWorkbook.Path & _
    Application.PathSeparator & "Test.doc"
    Application.ScreenUpdating = False

    With oApp
    Set oDoc = .Documents.Open(Filename:=sPath)
    Set oTable = oDoc.Bookmarks("bk1").Range.Tables(1)

    For iCol = 1 To oTable.Columns.Count

    For iRow = 1 To oTable.Rows.Count

    oTable.Cell(iRow, iCol).Range.Text = _
    Application.Worksheets("Sheet1").Cells(iRow, iCol).Value

    Next

    Next

    .Visible = True
    .Activate
    .WindowState = wdWindowStateMaximize
    End With
    Set oTable = Nothing
    Set oDoc = Nothing
    Set oApp = Nothing
    End Sub
    [/VBA]

    See Attachment

    The code is setup in a way you can run it as is. (no need to change the path just run via the button)

    Enjoy!
    _________
    Groetjes,

    Joost Verdaasdonk
    M.O.S. Master

    Mark your thread solved, when it has been, by hitting the Thread Tools dropdown at the top of the thread.
    (I don't answer questions asked through E-mail or PM's)

  16. #16
    thanks a lot !surely this will help a lot.
    thanks agian.we can consider this thread solved.

  17. #17
    Administrator
    VP-Knowledge Base
    VBAX Guru MOS MASTER's Avatar
    Joined
    Apr 2005
    Location
    Breda, The Netherlands
    Posts
    3,281
    Location
    You're welcome..glad we could help!
    _________
    Groetjes,

    Joost Verdaasdonk
    M.O.S. Master

    Mark your thread solved, when it has been, by hitting the Thread Tools dropdown at the top of the thread.
    (I don't answer questions asked through E-mail or PM's)

Posting Permissions

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