CODEWITHSUNDEEP
excelvbams-word
gregm
gregm

Reputation: 177

Is it possible to pass a variable from MS Word to Excel with VBA?

I have a situation where I need to pass a variable from Word to Excel via a macro. The reasoning is a little complicated, but basically I want to use this as a way to create a customized index. I point that out in case there might be an easier way to create the index. This particular index is created based on the paragraph a word or phrase is found in and not the page number. There doesn't appear to be a way to do this in Word.

So, I want to highlight the word or phrase, press my macro combo (or add a right click menu item to do it) which assigns the highlighted text to a variable and then sends it to one of three open worksheets (there will be 3 sections to the index) and adds it to the bottom row of the spreadsheet. From there I need to alphabetize the list and combine all the same phrases into one cell.

I know how to send items from Excel to Word, but when I try to research the idea of sending from Word to Excel all I can find are examples of Excel retrieving something from Word and returning it back to Excel. This is not applicable to what I'm doing. There will be thousands of index entries (before I combine them).

If it isn't possible to go directly from Word to Excel then I suppose I could send the selected text to another another document, but again I don't know how to do this.

Please help!

Upvotes: 0

Views: 283

Answers (1)

FaneDuru
FaneDuru

Reputation: 42236

I did not understand why you need to work in Excel from Word, but I prepared a piece of code to show you how you can (easily) acces Excel object and work using its objects:

Sub testWorkingInExcel()
 Dim objEx As Excel.Application, wb As Excel.Workbook, W As Excel.Workbook, sh As Excel.Worksheet
 Dim lastEmptyRow As Long, boolFound As Boolean
 
 On Error Resume Next
 Set objEx = GetObject(, "Excel.Application") 'find Excel open session if any
  If Err.Number <> 0 Then
    Err.Clear: On Error GoTo 0
    Set objEx = CreateObject("Excel.Application")
    Set wb = objEx.Workbooks.Open("Workbook full name")
  Else
    For Each W In objEx.Workbooks
        If W.Name = "Needed workbook" Then
            Set wb = W: boolFound = True: Exit Sub
        End If
    Next
    If Not boolFound Then Set wb = objEx.Workbooks.Open("Workbook full name")
  End If
 On Error GoTo 0
 Set sh = wb.Worksheets("NeededSheet")
 lastEmptyRow = sh.Range("A" & sh.Rows.Count).End(xlUp).Row
 'Now, you have the last empty row of the needed page and you can do
 'whatever you could do directly in Excel...
End Sub

Upvotes: 2

Related Questions