Combining Word & Excel Macros

  • 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:

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

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

    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!

  • 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.

  • 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?




    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.
    ' Release the object variable.
    Set ExcelSheet = Nothing

    End Sub

  • 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.

  • 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......)

  • 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.

Participate now!

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