Excel to Word

  • I am trying to create a macro that will use data from Excel to create a Word doc and I came across this. Almost everything seems to work exactly as I want it to with 2 exceptions.


    • For some reason it leaves a blank line at the top of the Word doc. I've tried several different things, but I can't seem to get rid of it.
    • I'm trying to insert a hyperlink for an email address, but every method I try simply does nothing.


    If anyone can offer some guidance I'd really appreciate it.


    Here's my code, as it stands now:


    As a side note, this has been redacted a bit to remove some info that shouldn't be posted publicly and I know that this is set up to create a separate document for each row on the spreadsheet and that it replaces each sheet when it saves so I'll only see one sheet. For now, this is as intended.


    Thanks in advance for any assistance.

  • Re: Excel to Word


    In your redacted code, the first interaction with text in the Word document is

    Code
    .TypeParagraph


    To add a hyperlink:

    Code
    ActiveDocument.Hyperlinks.Add Anchor:=Selection.Range, _
            Address:="[email protected]", _
            ScreenTip:="Fred's email address", _
            TextToDisplay:="(Email Fred)"


    Haven't checked if that's simlar to your attempt, but it has been tested, and works.

  • Re: Excel to Word


    Hmmm...sorry if I'm being obtuse, but it didn't work for me.


    I made sure that

    Code
    .TypeParagraph

    is the first interaction. It goes

    Code
    with .Selection
    .TypeParagraph
    end with


    But it still is leaving a bliank line at the top of my document.


    As far as the hyperlink, that's still not working either. Just to be safe I used:

    Code
    .TypeText Text:="Email: [email protected]" '& Chr(11) 'still not working
                ActiveDocument.Hyperlinks.Add Anchor:=Selection.Range, _
                Address:="[email protected]", _
                ScreenTip:="Fred's email address", _
                TextToDisplay:="(Email Fred)"


    It is also within the

    Code
    with .Selection
    end with


    I'm sorry, I've done a lot of stuff in Excel, but working with Word is new to me.


    Thanks.

  • Re: Excel to Word


    Attach a sample copy of your workbook - adding the hyperlink works fine for me, but that's code I knocked together to test.


    Quote

    working with Word is new to me

    There's no difference, it's all VBA. It's just that Word has different methods and properties. Use the macro recordser to get an idea of the syntax and the Property/Method names. Exactly the same as Excel.

  • Re: Excel to Word


    As you can probably guess, the issue is in the line that adds the hyperlink...


    Your orginal line:

    Code
    ActiveDocument.Hyperlinks.Add Anchor:=Selection.Range, _
                 Address:="[email protected]", _
                 ScreenTip:="Fred's email address", _
                TextToDisplay:="(Email Fred)"


    You need to change this to:

    Code
    ActiveDocument.Hyperlinks.Add Anchor:= .Range, _
                 Address:="[email protected]", _
                 ScreenTip:="Fred's email address", _
                TextToDisplay:="(Email Fred)"


    as you are already within a With .Selection block, this was causing the error.


    Needless to say, trying to get something to work with an 'On Error Resume Next' statement active is not a good idea...


    You should read this and this to understand how to debug VBA code and how to implement proper error handlers.

  • Re: Excel to Word


    Ok...I feel silly for not noticing that. Any idea about the extra line at the beginning of the document?


    Also, any idea why the hyperlink text is in a different font? It's not that big a deal, but if it's an easy fix...

  • Re: Excel to Word


    Can't say about the extra line other than what I mentioned earlier - and your reply that "... it still is leaving a bliank line at the top of my document" confuses me. Of course .TYPEPARAGRAPH adds a blank line, that's what its supposed to do.


    You're complaining of a blank line at the start of the document? Take that line out...


    Word has a specific Style Called 'Hyperlink' - it sounds like that formatting is being applied automatically. I'm not aware offhand of how to turn it off so can't really suggest other than the blunt method of selecting the hyperlink text and reformatting in code...

  • Re: Excel to Word


    Quote from cytop;640838

    Can't say about the extra line other than what I mentioned earlier - and your reply that "... it still is leaving a bliank line at the top of my document" confuses me. Of course .TYPEPARAGRAPH adds a blank line, that's what its supposed to do.


    You're complaining of a blank line at the start of the document? Take that line out...


    Word has a specific Style Called 'Hyperlink' - it sounds like that formatting is being applied automatically. I'm not aware offhand of how to turn it off so can't really suggest other than the blunt method of selecting the hyperlink text and reformatting in code...


    I'm sorry...I hadn't understood. And I also misunderstood what .TYPEPARAGRAPH was doing. Removing it worked fine. Still working on the link, but I thank you again for your assistance.

  • Re: Excel to Word


    I was thinking that before my document was saved I could run a Find, select the code and add the hyperlink. But everything I've tried so far has failed. If anyone knows of a more graceful way of doing this, please let me know.

  • Re: Excel to Word


    If you really want to format the hyperlink, the following is a not very elegant way of doing it...


    Code
    With ActiveDocument.Hyperlinks(1).Range.Font              '// Change 1 to suit if more than 1 hyperlink
        .Name = ActiveDocument.Styles("Normal").Font.Name  '// Or more simply: .Name = "Arial"
        .Size = ActiveDocument.Styles("Normal").Font.Size     '// .Size = 12
        .Underline = wdUnderlineNone
        .Color = wdColorAutomatic
    End With
  • Re: Excel to Word


    I appreciate the response. Not sure what I'm doing wrong, but it isn't working for me. I tried it under the code that inserts the link, removing the activedocument like this:

    Code
    With .Hyperlinks(1).Range.Font '// Change 1 to suit if more than 1 hyperlink
                    .Name = "Times New Roman" '// Or more simply: .Name = "Arial"
                    '.Size = ActiveDocument.Styles("Normal").Font.Size '// .Size = 12
                    .Underline = wdUnderlineNone
                    .Color = wdColorAutomatic
                End With


    I also tried it outside of:

    Code
    with .selection
    end with


    and even outside of:

    Code
    with WordApp
    end with


    But doesn't seem to be applying any changes. A side note is that my pagesetup isn't being applied either, though I thought it was working previously.


    Here is my code as it stands now:

  • Re: Excel to Word


    By the way, if I comment out the "on error resume next" statement I get a run time 5941 error at the "With .Hyperlinks(1).Range.Font" statement.

  • Re: Excel to Word


    While you're developing it, leave out the 'On Error Resume Next' statements - they mask errors that will eventually rear up and bite - just like this one has.


    After you've finished developing, leave them out as well. There are very few times you actually need that statement, all errors should be handled with 'formal' error handling... My opinion only


    You're getting the error because there is no Hyperlink in the current selection (Whatever that is - I can't check at the moment as using a mobile phone). You can get around this by using


    Code
    With WordApp.ActiveDocument.Hyperlinks(1).Range.Font '// Change 1 to suit if more than 1 hyperlink
                        .Name = "Times New Roman" ' ActiveDocument.Styles("Normal").Font.Name '// Or more simply: .Name = "Arial"
                        .Size = 14   'ActiveDocument.Styles("Normal").Font.Size '// .Size = 12
                        .Underline = wdUnderlineNone
                        .Color = wdColorAutomatic
                    End With


    I notice, also, you are manually setting the font, so the same font and size is applied to the hyperlink, or change to suit yourself.


    Although not tested, that should work as far as the Hyperlink formatting goes, not sure about the page formatting... I may get a chance to check later this evening, but no promises.

  • Re: Excel to Word


    I kind of came to the same conclusion. I was looking into different methods of applying changes to hyperlinks and found one that included a cound of the hyperlinks in the document. And, as you already know, it showed zero. And yet the hyperlink works when I try it.


    And your code definitely works, and I see from it how I messed up the call, I guess it had to be done from outside the Selection. Thank you.


    As for the PageSetup, it seems that the various things I tried to resolve the hyperlink issue were interfering with it somehow. I put it back the way it was (without the .Section reference) and it works fine.


    So, I guess this thread is good. I still have another issue, but it's in another thread about text positioning.


    Thanks again for all of your help.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!