Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Referring to XL from Word (WinNT4, Word97/XL97)

    I have a Word document that, on opening, needs to update its userform from an XL workbook (specific cells from a specific sheet in that workbook, to be exact); I've never quite got the hang of referring to a different application, though, so I'd be grateful if someone could point me in the right direction!

    I've got as far as creating the userform and I know the information requests need to go into the userform initialise sub, but after that ...!

    Any assistance would be greatly appreciated!
    Beryl M


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Referring to XL from Word (WinNT4, Word97/XL97)

    You must use Automation to retrieve info from a workbook.

    Private Sub UserForm_Initialize()
    Dim xlApp As Object
    Dim xlWbk As Object
    Dim xlWsh As Object
    Dim blnStartExcel As Boolean

    On Error Resume Next

    Set xlApp = GetObject(, "Excel.Application")
    If xlApp Is Nothing Then
    Set xlApp = CreateObject("Excel.Application")
    If xlApp Is Nothing Then
    MsgBox "Cannot activate Excel!", vbExclamation
    Exit Sub
    End If
    blnStartExcel = True
    End If

    On Error GoTo ErrHandler

    ' Substitute correct path/filename, worksheet, cells
    Set xlWbk = xlApp.Workbooks.Open("C:ExcelMyWorkbook.xls")
    Set xlWsh = xlWbk.Worksheets("MySheet")

    ' Do something with the worksheet, e.g.
    Me.TextBox1 = xlWsh.Range("C3")
    Me.TextBox2 = xlWsh.Range("D5")

    ExitHandler:
    On Error Resume Next
    xlWbk.Close SaveChanges:=False
    If blnStartExcel Then
    xlApp.Quit
    End If
    Set xlWsh = Nothing
    Set xlWbk = Nothing
    Set xlApp = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Referring to XL from Word (WinNT4, Word97/XL97)

    Hans, you're an angel! <img src=/S/angel.gif border=0 alt=angel width=15 height=21> Exactly what I need, as always!

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> Thank you! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Beryl M


  4. #4
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Referring to XL from Word (WinNT4, Word97/XL97)

    Just found another small matter ...

    The spreadsheet is going to have an extra row each time, and the Word document has to pick up the cell in the last row; eg the first time it would be from D3, next time D4, etc. It's always going to be the last cell of column D in the 'currentregion' that contains D3 - but how on earth do I say that?!

    Many thanks in advance (again)!
    Beryl M


  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Referring to XL from Word (WinNT4, Word97/XL97)

    To get the last non-blank cell in column D:

    xlWsh.Range("D65536").End(-4162)

    -4162 is the numeric value of the Excel constant xlUp. The code is the equivalent of starting at the very bottom of column D and pressing End followed by the up arrow to find the last filled cell.

  6. #6
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Referring to XL from Word (WinNT4, Word97/XL97)

    Hans, thanks for that, which sounds great, but I can't guarantee that the entire column will be blank - will this still work if I change D65536 to, say D56?
    Beryl M


  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Referring to XL from Word (WinNT4, Word97/XL97)

    Will everything from D3 to the cell you want be filled (non-blank)? If so, you can also use

    xlWsh.Range("D3").End(-4121)

    (-4121 = xlDown)

    Using xlWsh.Range("D56").End(-4162) will also work, as long as you are certain that your range will never extend below D55.

  8. #8
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Referring to XL from Word (WinNT4, Word97/XL97)

    Hi Hans, yes, D3 to the one I want will always be filled, that's what I was trying to say when I called it the 'currentregion'!

    I'll try that new version, ta muchly
    Beryl M


  9. #9
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Referring to XL from Word (WinNT4, Word97/XL97)

    Arrghh! They've gone and moved the goalposts on me!

    Sorry, Hans, but I now need to open Word from XL and copy the contents of a miscellaneous selection of cells to miscellaneous places in a specified Word document ... and I've a feeling it's not so simple as just reversing the code you supplied earlier!

    What would be the best way to do the information transfer - can one reliably say 'copy activesheet.range("D5") to word.document("thisdoc").fields("text3")'? Or should I save to custom properties in each and transfer the info that way?

    PS No userform required anymore!
    Beryl M


  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Referring to XL from Word (WinNT4, Word97/XL97)

    One way is to set custom document properties and to refer to them in the document using DocProperty fields.
    Another way is to set document variables and to refer to them in the document using DocVariable fields.
    Users can view/edit custom document properties. Depending on whether this is desirable or not, document properties may be better than document variables or the reverse.

    Yet another way is to put placeholder text in the document and to replace it using code.

  11. #11
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Referring to XL from Word (WinNT4, Word97/XL97)

    Hans, I'm almost there, just one bit I can't figure out - In my xl spreadsheet, the user clicks on a button which saves information to custom properties, then opens a Word document. The Word document retrieves the information on opening and places it in docvariables in the document.

    However, this means that the xl spreadsheet that the Word document is referring to is always going to be open already, causing an error with the code as it is, and I can't work out how to alter the code so it doesn't try to open it, just assumes it is open and refers to it!

    Help, please!

    Ta
    Beryl M


  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Referring to XL from Word (WinNT4, Word97/XL97)

    Could you post the relevant bit of the code (in a text file attachment if it's long)? I'm sure we'll be able to work it out.

  13. #13
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Referring to XL from Word (WinNT4, Word97/XL97)

    Well, it's using the exact code you posted earlier - first in reverse, to open the Word document from an XL spreadsheet, then exactly as posted to retrieve the XL information from the Word document - except I don't need it to open the XL spreadsheet, just refer to it! That is, in the Word document there is a line that says:

    Set xlWbk = xlApp.Workbooks.Open("[path][name].xls")

    but I don't need it to open the workbook, just set the variable to refer to it!
    Beryl M


  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Referring to XL from Word (WinNT4, Word97/XL97)

    You can use this:

    Set xlWbk = xlApp.Workbooks("[name].xls")

    (you don't need to specify the path this time, since the workbook is already open)

  15. #15
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Referring to XL from Word (WinNT4, Word97/XL97)

    Thanks, Hans, that works - although why it works like that when it didn't when I tried it earlier with the path still in I don't know!!

    Re the .End(-4162) idea - is there any way to get the code to differentiate between a truly empty cell and a cell that contains a formula but the formula's result is currently nothing? eg if [cell a] > 2, "something", "" - the current result is "" but the End command is still reacting to the fact that there is a formula there.

    Also, xlDown doesn't seem to go down - when I ran that command as a one-off and went to see where the selection point was, it had moved to the top of the column, not the bottom! And that happened whether I used the numeric command or replaced it with the word xlDown.

    I is confuggulated!! <img src=/S/sad.gif border=0 alt=sad width=15 height=15>
    Beryl M


Page 1 of 2 12 LastLast

Posting Permissions

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