none
control of Excel from Word-VBA RRS feed

  • Question

  • Dear Experts,

    I want to control VBA-Excel from VBA-Word, just a simple search that finds out, if a certain word appearing in the Word-document is present also in an Excel-Sheet. (In the example below it is the word "entropy".) All works well, except the find-procedure within the excel sheet. The relevant part of the code is:

    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    xlApp.ScreenUpdating = True
    Set xlWB = xlApp.Workbooks.Open("S:\Excel_Workbook.xlsm")
    With xlWB.Worksheets(1)
      .Activate
      Set Found = .Cells.Find(What:="entropy", After:=ActiveCell, LookIn:= _
                    xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
                    xlNext, MatchCase:=False, SearchFormat:=False)
    (..etc)
    the last command is not accepted, because the "types are not compatible".
    Thank you
    Martin

    Tuesday, October 2, 2012 8:56 AM

Answers

  • Hi Martin

    A xlsm file is not a macro file, as alexander suggests, but an Excel workbook that may contain macros (as opposed to an Excel workbook that may NOT contain macros). That shouldn't be an issue here...

    But the version of Office you're using may be playing a role.

    Which version of Office is this, and what's the source of the code you're using?

    My best guess, though, would be that you haven't declared the Found object and that Word VBA may be having trouble recognizing what the type is. Try adding the following at the top of your code:

    Dim Found as Excel.Range

    Both Word and Excel have a Range data type and Word will tend to assume that the Range being returned is a WORD range, not an Excel range.


    Cindy Meister, VSTO/Word MVP, my blog

    Wednesday, October 3, 2012 3:45 AM
    Moderator

All replies

  • Hallo Martin, hier nun auf English.

    Everything is fine with your source.
    But .xlsm is maybe the mistake. You want to open a worksheet and not the makrofile.
    German Wikipedia says: Excel Spreadsheet mit Makros

    Check sour References and be sure the invoked excel-Version is correct.
    before Etc missing a end with but sure the original got it.

    Regards Alexander

    Tuesday, October 2, 2012 1:27 PM
  • Hi Martin

    A xlsm file is not a macro file, as alexander suggests, but an Excel workbook that may contain macros (as opposed to an Excel workbook that may NOT contain macros). That shouldn't be an issue here...

    But the version of Office you're using may be playing a role.

    Which version of Office is this, and what's the source of the code you're using?

    My best guess, though, would be that you haven't declared the Found object and that Word VBA may be having trouble recognizing what the type is. Try adding the following at the top of your code:

    Dim Found as Excel.Range

    Both Word and Excel have a Range data type and Word will tend to assume that the Range being returned is a WORD range, not an Excel range.


    Cindy Meister, VSTO/Word MVP, my blog

    Wednesday, October 3, 2012 3:45 AM
    Moderator
  • Hallo Cindy,

    thank you for your answer and excuse for my delay in responding due to an illness.

    My Visual Basic Version is 6.5 in Office 2007, and Excel.Range is not detected as a defined type.

    Maybe you know any further help?

    Kind regards,

    Martin

    Thursday, October 11, 2012 1:30 PM
  • Hi Martin

    Did you set a reference to the Excel object library in the VBA Editor's Tools/References dialog? I had assumed you had, since you're using Enums such as xlWhole which shouldn't work unless you do have the reference.

    How are you declaring ("Dimming") the variables used in your code? You don't show us any of that...


    Cindy Meister, VSTO/Word MVP, my blog

    Thursday, October 11, 2012 1:55 PM
    Moderator
  • Hallo Cindy,

    the VBA Editor's Tools/References dialog is somewhat beyond my knowledge, I have not yet made the step into the world of integrated office VBA-solutions/vb.net. Where can I find it?

    Best I give here the code as I am using it. It all works up to the line starting with "Set found..". "Types are incompatible" is the error message.

    Kind regards,

    Martin

    Sub CheckKeywordConsistency()

    Dim xlApp, xlWB As Object

        WorkWindow = ActiveDocument.Name
        Set aTable = ActiveDocument.Tables(1)
        cRows = aTable.Range.Rows.Count
        For iRow = 1 To cRows
            aTable.Rows(iRow).Cells(3).Select
            Selection.MoveLeft Extend:=wdExtend
            Keyword = Selection.text

            Set xlApp = CreateObject("Excel.Application")
            xlApp.Visible = True
            xlApp.ScreenUpdating = True
            Set xlWB = xlApp.Workbooks.Open("S:\SM\LB Team\Mueller\Ontologies\Taxonomy_Keywords_check_test.xlsm")
            With xlWB.Worksheets(18)
                .Activate
                Set Found = .Cells.Find(What:="enthalpy", After:=ActiveCell, LookIn:= _
                    xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
                    xlNext, MatchCase:=False, SearchFormat:=False)
                If Not Found Is Nothing Then
                    MsgBox Keyword
                End If
            End With

            xlWB.Close False ' close the workbook without saving
            xlApp.Quit ' close the Excel application
            Set xlWB = Nothing
            Set xlApp = Nothing
            Windows(WorkWindow).Activate
        Next iRow
    End Sub

                    
    Tuesday, October 16, 2012 3:29 PM
  • Hi Martin

    <<the VBA Editor's Tools/References dialog is somewhat beyond my knowledge>>

    Make sure the macro project you're working with is the selected one in the VBA editor.

    Go to the VBA Editor's Tool menu and select the menu point References.

    Locate the Microsoft Excel entry for the version of Office you have and make sure the checkbox next to it is activated. OK.

    It's considered good programming to declare all variables you plan to use at the beginning of your macro and also to specify the "data type" when it is known. And to help prevent problems due to typos when writing your code (typing xWB instead of xlWB, for example) it's also a very good idea to activate "Require variable declaration" in Tools/Options/Editor.

    Whether you choose to do that or not you must make the following declaration after setting the reference:

      Dim Found as Excel.Range

    Because your code is running in Word and Word has a Range object, Word will assume that any Range you work with is a Word Range. You must tell Word explicitly that you want to work with an Excel Range. That should take care of the message about types being incompatible (the two kinds of Ranges are not the same, even though both are called Range).


    Cindy Meister, VSTO/Word MVP, my blog

    Wednesday, October 17, 2012 2:28 PM
    Moderator