Combining Word & Excel Macros

Batanen · May 27th 2004

I have two 'macros' that work with each other to pull information I need daily. One is in excel that will go to the first availble cell in the worksheet and paste the information. The other is in Word that will copy the information. Both will perform a certain task, then a message box will appear that is basically a 'pause' until I work on the next step in the other macro. Here's how it runs:

Excel:
Finds first empty cell in column 'b' of sheet one, then message box comes up.

Word:
Run the macro to find the information needed then copies to clipboard, then message box pauses.

Excel:
Pastes infromation from the clipboard, goes to sheet 2 to the first empty cell of column "B" and message box pauses

This is a lot of back-and-forth that I have to do (when my system doesn't lock up on me...). Is there any way that either I can combine all this into one VBA macro so I can just run it and be done with it...or is there any way to copy all the information I need from Word into the clipboard? In the past, I have been able to copy several items to the clipboard and a box would come up with showing I had these items in the clipboard, but I have no idea how it happend.

Any and all suggestions will be greatly appreciated!

Anonymous · May 27th 2004

Yes, your macros can be combined fairly easily. I have some code that I can post as an example, but it's a home so won't be able to post it until tonight. Will do that if you haven't been helped by then, so stay tuned.

The trick is to open one of the applications from the other and call its macros from the original application's macro. The sample I have runs from Excel and calls the Word to post things into word, so it sounds like the opposite of what you are doing, but if your macros are working, that shouldn't be an issue.

Batanen · May 27th 2004

Thanks! I'd appreciate it

Batanen · May 27th 2004

Actually, I think I *may* have found some code that can help me...but I still need help with the code. I want to run the VBA Macro out of Word, since the document title changes from day to day...but the Excel Spreadsheet name will stay the same. So, with the code below, how would I call a spreadsheet (Daily.xls) instead of opening a new spreadsheet? Also, if anyone knows, will this automatically open Excel, or should it be open to begin with?

Thanks!

Shane

Code:

Sub CallExcel()

' Declare an object variable to hold the object reference.

Dim ExcelSheet As Object
Set ExcelSheet = CreateObject("Excel.Sheet")

'This code starts the application creating the object, in this case, a
'Microsoft Excel spreadsheet. Once an object is created, you reference it in
'code using the object variable you defined. In the following example, you
'access properties and methods of the new object using the object variable,
'ExcelSheet, and other Microsoft Excel objects, including the Application
'object and the Cells collection.

' Make Excel visible through the Application object.
ExcelSheet.Application.Visible = True
' Place some text in the first cell of the sheet.
ExcelSheet.Application.Cells(1, 1).Value = "This is column A, row 1"
' Save the sheet to C:\test.xls directory.
ExcelSheet.ActiveWindow.WindowState = xlMaximized
ExcelSheet.SaveAs "C:\TEST.XLS"
' Close Excel with the Quit method on the Application object.
'ExcelSheet.Application.Quit
' Release the object variable.
Set ExcelSheet = Nothing

End Sub

Anonymous · May 27th 2004

This is note the code I was thinking of before, but I did find this Word macro code that opens an Excel file named "TestIt" that is located in the same folder as the Word document from which the macro is being run and grabs data to put in the Word document. If the Excel file is in a different folder, you'll need to enter its full path (in quotes) along with the filename.

Code

Sub GetTheData()
    Dim XLSheet As Object
    Dim wBook As String
    Dim Target As Integer
    Dim r As Integer, Hits As Integer, LastRow As Integer
    Dim ColB As Variant, ColD As Variant, ColI As Variant


CreateObject ("Excel.Application")
wBook = ThisDocument.Path & "\TestIt.xls"
Set XLSheet = GetObject(wBook)
XLSheet.Sheets("Data").Activate


LastRow = XLSheet.ActiveSheet.Cells(65536, 1).End(xlUp).Row


StartAgain:
Target = InputBox("What number are you searching for?")
Hits = 0


Selection.TypeText Text:="Columns B, D, and I values for column A=" & Target
Selection.TypeParagraph


For r = 2 To LastRow
With XLSheet
    If .ActiveSheet.Cells(r, 1).Value = Target Then
        Hits = Hits + 1
        ColB = .ActiveSheet.Cells(r, 2).Value
        ColD = .ActiveSheet.Range("D" & r).Value
        ColI = .ActiveSheet.Range("I" & r).Value
        Selection.TypeText Text:=ColB & vbTab & ColD & vbTab & ColI
        Selection.TypeParagraph
    End If
End With
Next r




If Hits = 0 Then
    Selection.TypeText Text:="Sorry, there are no matches for " & Target
End If


Selection.TypeParagraph
Selection.TypeParagraph


If MsgBox("Search for another number?", vbYesNo) = vbYes Then GoTo StartAgain


End Sub

Display More

Batanen · May 27th 2004

Thank you ever so much. This is really helping me ALOT! I'll be posting back if I have any other questions about this project (I'm sure I will......)

Anonymous · May 27th 2004

You've already posted back, but in case you return to this thread, I added more code to my previous post (should have done it initially) that also provides some examples of grabing data from Excel and inserting it into Word. It's a pretty bland application, but should help get you started.

Combining Word & Excel Macros

Participate now!

Share

Users Viewing This Thread