Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Feb 2015
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Want macro for hyperlinks and saved content in adjacent columns in table

    Hey everyone,

    I was wondering if someone could guide me to the proper resources or help me to come up with a macro for the following:

    Untitled.png

    Basically I have columns of hyperlinks to word documents and in the adjacent column, I'd like to have the copied and pasted content of those documents in the adjacent column, shrunk to fit.

    This would immensely help me because right now I am clicking the links, hitting Ctrl+A, Ctrl+C in the word document, closing the document, then Ctrl+V-ing each and every time. My spreadsheet defaults to wrapped text, so I then have to manually shrink each cell in the column to fit after finishing all the copying and pasting.

    I tried to record a macro (in excel) but it seems that the code doesn't recognize the select all/copy functions done in the word document. I've also created a shortcut macro (in Word) just to simplify the select all/copy functions under one shortcut but it doesn't save me enough time.

    So to summarize, how can I speed up the process of clicking a hyperlink in excel to a word document, selecting all, copying, pasting back into excel, and then shrinking to fit.

    I keep asking this question and people keep viewing the thread and ignoring it. If it asks too much or betrays a complete lack of knowledge, can someone at least tell me where I should be looking up this information? Your help is much appreciated!

    Thank you!!
    Last edited by shenqila; 2015-02-11 at 16:23.

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,172
    Thanks
    47
    Thanked 981 Times in 911 Posts
    We should charge for responses to this one.

    cheers, Paul

  3. #3
    New Lounger
    Join Date
    Feb 2015
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I just wanted to actually get responses

    What's below is as far as I got, how can I get the macro to detect my select all/copy functions in the word document before pasting it into the adjacent cell in excel?

    Sub Macro4()
    '
    ' Macro4 Macro
    '
    ' Keyboard Shortcut: Ctrl+q
    '
    Range("C3").Select
    Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
    Range("D3").Select
    ActiveCell.FormulaR1C1 = _
    "ARTICLE TEXT......."
    "ARTICLE TEXT......."
    "ARTICLE TEXT......."
    "ARTICLE TEXT......."
    "ARTICLE TEXT......."
    "ARTICLE TEXT......."
    "ARTICLE TEXT......."
    Range("D3").Select
    With Selection
    .HorizontalAlignment = xlGeneral
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = True
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    End Sub

  4. #4
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    You are doing something that most people would consider very odd - putting an entire Word document into a single cell in excel doesn't sound particularly useful. If the content is unreadable because it is squashed down to a tiny size then there isn't much point in doing it.

    What content is in the Word documents? Are we talking a single paragraph or multiple paragraphs/tables/graphics etc.? If you are using an ordinary paste command and it only goes into a single cell then the Word document must only contain a single paragraph.

    Is the cell limited in width and height?
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  5. #5
    New Lounger
    Join Date
    Feb 2015
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you Andrew,

    The purpose is to archive the text of the word documents within the excel sheet. So whenever I copy and paste the contents into excel and shrink to fit, what ends up in the cell is simply the text of each document. Yes I am copying graphics/tables/etc. but only the text ends up in the excel sheet, and these word documents contain multiple paragraphs.

    Perhaps I am not using an ordinary paste command because I am able to get as much as 4-5 pages into each cell. They are limited in width and height, so the shrinking to fit is simply for cosmetic purposes. I am archiving the word documents by listing them (hyperlinked to their location), and archiving the content of them in the adjacent cell.

    Is there anyway to automate this process or make it quicker? (than manually doing all the copying and pasting?

  6. #6
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    Shenquila

    There are multiple ways to do this automatically but we need to understand what you want in the cell. Do you want the entire embedded Word document or just the text contained in it?

    As an example, the following code shows sample code that is importing the files as OLE objects so they retain all their content and act like graphics that can be double clicked to edit in Excel. Select the column of hyperlinks before running this code since you haven't specified how to identify the column of hyperlinks.
    Code:
    Sub Grabby()
      Dim aCell As Range
      Dim sPath As String
      For Each aCell In Selection.Cells
        If aCell.Hyperlinks.Count > 0 Then
          sPath = aCell.Hyperlinks(1).Address
          aCell.Offset(0, 1).Select
          ActiveSheet.OLEObjects.Add Filename:=sPath, Link:=False, DisplayAsIcon:=False
        End If
      Next aCell
    End Sub
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  7. #7
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Quote Originally Posted by shenqila View Post
    I keep asking this question and people keep viewing the thread and ignoring it. If it asks too much or betrays a complete lack of knowledge, can someone at least tell me where I should be looking up this information?
    Comments like this are unlikely to win you any friends. The 'views' to which you refer may represent nothing more than page accesses by web-bots that index web pages such as this. Others may be by people who, though expert in some respects, don't know or don't have the time to invest in providing an answer. Yet others may be by people who want to know what you're being so haughty about, suggesting only 'experts' need reply...

    You also refer to Word and Excel macros you've already created, but you seem reluctant to let us see what they're doing - you didn't posted any code in that post (only later). In any event, extracting the text from a Word document isn't all that difficult. What is more difficult is dealing with content that may be in page headers/footers, textboxes, footnotes, endnotes, tables, content controls, ActiveX controls, fields and the like. Do your documents contain such content? If so, what do you want done with it? And what about how the text in the document is formatted - with headings, indents, bullets and so on?

    Finally, if by "I keep asking this question" you mean you've posted the same question elsewhere, please read: http://www.excelguru.ca/content.php?184, then provide all sites concerned with the links to your cross-posts.
    Last edited by macropod; 2015-02-12 at 03:55.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  8. #8
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    1,722
    Thanks
    146
    Thanked 156 Times in 149 Posts
    My question would be - why? Having the reason you're doing this would help in how to best achieve it. And this might be by not using Excel anyway, as you refer to archiving.

Tags for this Thread

Posting Permissions

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