Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 22

Thread: Add to Macro - Move additional Text from Excel to Word doc

  1. #1
    Board Regular
    Join Date
    Feb 2003
    Posts
    315
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Add to Macro - Move additional Text from Excel to Word doc

    Hello - Thank you for looking. I have a Working Macro that has saved

    me countless time with increased data accuracy across the last years that was created with Help of others more knowledgable than I. I am using - Windows XP SP3 & Office XP and 2003.

    I would like to add a feature to this Macro that will add info to the

    end product making it more useable and again saving time.

    My Macro cuts one designated row from One Workbook and Places it in

    Another at the next available row position. It then copies a specified cell and places the text in a Named Open Word Document at the position of the cursor.

    I would like to Copy text from three additional Cells of that same

    last row and place them in another spot in the Open Word Document. The additional text would all be placed on one line with space dash space between them/ The last row is currently left highlighted.

    Im envisioning that leaving the current macro as is and adding code to the end to return to the highlighted row to copy the cells I need and then pasting them into the word document.

    I'm puzzled as to how to specify the location that I want the

    Additional text copied to. My thoughts say that the current code could be modified to leave the cursor at the desired position ( its not the position of the previous paste ) - but unsure how to do that. OR, designating the position for the paste to be the first row after any row containing 6 or more dashes. ( -------- )

    The Text would be in Cells S , AQ and AO of the last ( just

    transfered ) row of the Excel Workbook Named AMZ-GM Sold.xls.

    Here is my Current Code.
    Code:
    Sub OpenToSold()
    ' OpentoSold Macro Moves Sold Items from Amz Open to Sold and Pastes _
      the Description at the Cursor in the open AmazonSale Word Doc.
    
       'Macro recorded 2/1/2008 by Mike
    
       ' Keyboard Shortcut: Ctrl+Shift+W
    
    Dim lRow As Long
    Dim lCol As Long
    Rows(ActiveCell.Row).Cut
    Windows("AMZ-GM Sold.xls").Activate
    lRow = ActiveSheet.Cells.SpecialCells(xlLastCell).Row 'This gets the last row from AMZ-GM Sold.xls
    ActiveSheet.Cells(lRow + 1, 1).Activate 'Add 1 to the last row for first blank row
    ActiveSheet.Paste
    Cells(ActiveCell.Row, 26).Copy ' Copy Z--- 26 = z
    
    
    
    Dim WDApp As Word.Application
    Dim WDDoc As Word.Document
    Set WDApp = GetObject(, "Word.Application") ' Reference active document
    Set WDDoc = WDApp.ActiveDocument
    WDApp.Selection.PasteSpecial
    WDApp.Visible = True 'This should leave Word Open
    ' Clean up
    Set WDDoc = Nothing
    Set WDApp = Nothing
    Application.WindowState = xlMinimized
    End Sub
    I'm getting better at editing my macros but still having tough time with specifying variables / location. There does not seem to be the pleathra of info for word VBA as there is for Excel.

    Any Ideas or Help much appreciated. Thank You

  2. #2
    Moderator Macropod's Avatar
    Join Date
    Aug 2007
    Location
    Canberra, Australia
    Posts
    2,851
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Add to Macro - Move additional Text from Excel to Word doc

    You could use code like:
    Code:
    Sub OpenToSold()
    ' OpentoSold Macro Moves Sold Items from Amz Open to Sold and Pastes _
      the Description at the Cursor in the open AmazonSale Word Doc.
    ' Keyboard Shortcut: Ctrl+Shift+W
    
    Dim lRow As Long
    Dim WDApp As Word.Application
    Dim WDDoc As Word.Document
    Set WDApp = GetObject(, "Word.Application") ' Reference active document
    Set WDDoc = WDApp.ActiveDocument
    ActiveSheet.Rows(ActiveCell.Row).Cut
    Windows("AMZ-GM Sold.xls").Activate
    With ActiveSheet.UsedRange
      lRow = .Cells.SpecialCells(xlLastCell).Row 'This gets the last row from AMZ-GM Sold.xls
      lRow = lRow + 1
      .Cells(lRow, 1).Paste
      WDApp.Bookmarks("first").Range.Text = .Cells(lRow, 10).Text
      WDApp.Bookmarks("second").Range.Text = .Cells(lRow, 20).Text
      WDApp.Bookmarks("third").Range.Text = .Cells(lRow, 26).Text
    End With
    WDApp.Visible = True 'This should leave Word Open
    ' Clean up
    Set WDDoc = Nothing: Set WDApp = Nothing
    Application.WindowState = xlMinimized
    End Sub
    where the destination is a series of bookmarks; one per cell you want to output to Word. Note that, with this code, you don't need to have any particular range selected in Word.
    Cheers
    Paul Edstein
    [MS MVP - Word]

  3. #3
    Board Regular
    Join Date
    Feb 2003
    Posts
    315
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Add to Macro - Move additional Text from Excel to Word doc

    where the destination is a series of bookmarks; one per cell you want to output to Word. Note that, with this code, you don't need to have any particular range selected in Word.
    Thanks Paul -- attempted but Received Compile Error at first .bookmarks statement ( .bookmarks is Blocked in Dark Blue ) --statement says "Method or Data Member not Found"

    Really appreciate the help !

  4. #4
    Moderator Macropod's Avatar
    Join Date
    Aug 2007
    Location
    Canberra, Australia
    Posts
    2,851
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Add to Macro - Move additional Text from Excel to Word doc

    Oops, that should have been:
    WDApp.ActiveDocument.Bookmarks ...
    for each of those lines.
    Cheers
    Paul Edstein
    [MS MVP - Word]

  5. #5
    Board Regular
    Join Date
    Feb 2003
    Posts
    315
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Add to Macro - Move additional Text from Excel to Word doc

    Quote Originally Posted by Macropod View Post
    Oops, that should have been:
    WDApp.ActiveDocument.Bookmarks ...
    for each of those lines.
    Thanks Again -- The Macro now runs to completion. The Initial transfer of the data in position 26 ( Cell Z ) transfers correctly to the position of the cursor in the word document BUT no text from Cells S + AQ + AO is copied. Cell Z Remains copied to clipboard. ( dotted border around it ) -- The position in the work document that I need the text from those cells pasted remains unchanged.

    Is there something I Can explain better to help convey the result I am seeking or show a copy of the word document I am using ?

    I will be away from the computer for about an hour. I appreciate your efforts and will be available as soon as I return.

    Thanks Again

  6. #6
    Moderator Macropod's Avatar
    Join Date
    Aug 2007
    Location
    Canberra, Australia
    Posts
    2,851
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Add to Macro - Move additional Text from Excel to Word doc

    Ok, I'd missed your previous reference to Cells S + AQ + AO. Use:
    Code:
      WDDoc.Bookmarks("first").Range.Text = .Cells(lRow, 19).Text
      WDDoc.Bookmarks("second").Range.Text = .Cells(lRow, 26).Text
      WDDoc.Bookmarks("third").Range.Text = .Cells(lRow, 41).Text
      WDDoc.Bookmarks("fourth").Range.Text = .Cells(lRow, 43).Text
    PS: What remains on the clipboard isn't just cell Z, but the whole row - from your previous cut/paste operation. The above code doesn't reference the clipboard; it references the worksheet directly and doesn't use copy/paste.
    Cheers
    Paul Edstein
    [MS MVP - Word]

  7. #7
    Board Regular
    Join Date
    Feb 2003
    Posts
    315
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Add to Macro - Move additional Text from Excel to Word doc

    Thanks again -- Hope you arent loosing patience -

    Now the macro stops at .Cells(lRow, 1).Paste

    Message = Run Time Error 438 - Object doesnt support this property or method.

    AND another question / observation -

    Wont all the cells texts appear on the same line as the original paste the way its written ?

    Per OP:
    "I would like to Copy text from three additional Cells of that same
    last row and place them in another spot in the Open Word Document. The additional text would all be placed on one line with space dash space between them.

    Im envisioning that leaving the current macro as is and adding code to the end to return to the highlighted row to copy the cells I need and then pasting them into the word document.

    I'm puzzled as to how to specify the location that I want the
    Additional text copied to. My thoughts say that the current code could be modified to leave the cursor at the desired position ( its not the position of the previous paste ) - but unsure how to do that. OR, designating the position for the paste to be the first row after any row containing 6 or more dashes. ( -------- ) "


    The Place I need to paste the text is variable but always below the first paste. Usually 8 - 10 Rows down depending on the content of the document. I realize that its impossible to specify a constant location for it, thus my idea that - The one thing that is Constant is that its always the first row following the first instance of 6 or more dashes. ( ---------- )

    Hoping to see :

    ------------------
    S - AQ - AO

    Let me know if there is anything else I can explain better - Thank You Again.

  8. #8
    Moderator Macropod's Avatar
    Join Date
    Aug 2007
    Location
    Canberra, Australia
    Posts
    2,851
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Add to Macro - Move additional Text from Excel to Word doc

    Now the macro stops at .Cells(lRow, 1).Paste

    Message = Run Time Error 438 - Object doesnt support this property or method.
    I have no idea why you're now getting the Run Time Error 438 error - it wasn't occurring before and changing the code to populate the Word document has nothing to do with that part of the code.
    Wont all the cells texts appear on the same line as the original paste the way its written ?
    I'm not sure what you mean. The code as I've revised it finds the last row in the destination workbook, increments the count by one, then uses the new address for all further operations - pasting & getting data for the Word document.
    The Place I need to paste the text is variable but always below the first paste.
    The code I've provided assumes there are always four known locations in the document where the data need to go, each of which is identified by a bookmark there. Thus, it doesn't matter what you do/don't have selected. If that isn't how your document works (or can work), then you're going to have explain how the macro is supposed to know where all the other data go. Simply saying there's 6 or more dashes might work for the first one, but what about the rest? And, if you can have 6 or more dashes there, why not a bookmark?
    Cheers
    Paul Edstein
    [MS MVP - Word]

  9. #9
    Board Regular
    Join Date
    Feb 2003
    Posts
    315
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Add to Macro - Move additional Text from Excel to Word doc

    [QUOTE=Macropod;3816666]I have no idea why you're now getting the Run Time Error 438 error - it wasn't occurring before and changing the code to populate the Word document has nothing to do with that part of the code.

    Yes - Did not happen before -- Is there anything I can post that may help see it ? Maybe a new copy of the code as I have it after pasting in the new sections ?

    BOOKMARKS !! --- Sorry I had NO Understanding of them -- I will take a little time and see how to use them. Completely new concept for me. Sorry I mis understood the game plan. Let me read and learn a little on how to leave a bookmark.

    Thanks - Holiday coming here after work tomorrow - I will do some google reading - I do not have a book on Word VBA.

  10. #10
    Moderator Macropod's Avatar
    Join Date
    Aug 2007
    Location
    Canberra, Australia
    Posts
    2,851
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Add to Macro - Move additional Text from Excel to Word doc

    Re Run Time Error 438, I suggest you check what else has changed about your workbooks (e.g. which one is active when you run the macro).
    Cheers
    Paul Edstein
    [MS MVP - Word]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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