none
Open a word document from excel via a prompt

    Question

  • I need to be able to open a word document from Excel, but the file is different every time the macro is run. I have seen lots of examples of opening a static word doc, but struggling with being able to choose the file. I know how to do it in Excel to open another excel file, but it does not translate cleanly.  Anyone have any ideas? Below is the code I use to open a prompt to select an Excel file. If someone can help me modify it, I would be so happy! Thank you in advance!

      Dim sFil   As String
        Dim sTitle As String
        Dim sWb    As String
        Dim iFilterIndex As Integer
        ' Set up list of file filters
        sFil = "Excel Files (*.xls*),*.xls*"
        ' Display *.xls by default
        iFilterIndex = 1
        ' Set the dialog box caption
        sTitle = "Select Last Month's File for Comments."
        ' Get the filename
        sWb = Application.GetOpenFilename(sFil, iFilterIndex, sTitle)
        Workbooks.Open sWb
        MyName = ActiveWorkbook.Name

    Monday, May 14, 2012 7:43 PM

All replies

  • Do you merely wish to open the document, so that the user can view it or edit it, or do you need to manipulate the document using code?

    Regards, Hans Vogelaar

    Monday, May 14, 2012 8:32 PM
  • I will be finding data and copy a specific set of data to paste into excel. 

    Basically the word files are large amounts of data entries that have been exported to word (I have no control over the export). I need to find specific vendors and then find the vendors grand total. Below is an example. I  am ok at VBA/Macros in Excel, but I am not very familiar or good at it in Word. Goal will be to search for Vendor Name, then next instance of Vendor Total, Copy Next set of text, then paste into an Excel spreadsheet. Fun Fun :) Any advice would be appreciated!

    11758

    Vendor Name INC                                                              45025

    30261N

    29-MAR-2012

    NET 30

    305.34

    305.34

    305.34

    45025

    30337N

    29-MAR-2012

    NET 30

    58.38

    58.38

    58.38

    45025

    30358N

    29-MAR-2012

    NET 30

    29.50

    29.50

    29.50

    45025

    30362N

    29-MAR-2012

    NET 30

    49.63

    49.63

    49.63

    45025

    30363N

    30-MAR-2012

    NET 30

    59.33

    59.33

    59.33

    45025

    30376N

    29-MAR-2012

    NET 30

    16.90

    16.90

    16.90

    45025

    30381N

    30-MAR-2012

    NET 30

    82.52

    82.52

    82.52

    45025

    30459N

    03-APR-2012

    NET 30

    44.64

    44.64

    44.64

    45032

    30491N

    03-APR-2012

    NET 30

    304.25

    304.25

    304.25

    45130

    30803N

    17-APR-2012

    NET 30

    438.76

    438.76

    438.76

    45207

    30868N

    19-APR-2012

    NET 30

    174.33

    174.33

    174.33

    Vendor Total:

    1,563.58

    1,563.58

    1,563.58

    Monday, May 14, 2012 8:52 PM
  • The following is an example only, you'll have to experiment and adapt it.

    Sub OpenDoc()
        Dim strFile As String
        Dim objWord As Object
        Dim objDoc As Object
        Dim blnStart As Boolean
        ' Display Open dialog
        With Application.FileDialog(1) ' msoFileDialogOpen
            .Filters.Clear
            .Filters.Add "Word documents", "*.doc*"
            If .Show Then
                strFile = .SelectedItems(1)
            Else
                MsgBox "No document selected.", vbExclamation
                Exit Sub
            End If
        End With
        On Error Resume Next
        ' See if Word is already running; if not, start it
        Set objWord = GetObject(Class:="Word.Application")
        If objWord Is Nothing Then
            Set objWord = CreateObject(Class:="Word.Application")
            If objWord Is Nothing Then
                MsgBox "Can't start Word.", vbExclamation
                Exit Sub
            End If
            blnStart = True
        End If
        On Error GoTo ErrHandler
        ' Open document
        Set objDoc = objWord.Documents.Open(Filename:=strFile)
        ' Find vendor and total
        objWord.Selection.HomeKey Unit:=6 ' wdStory
        With objWord.Selection.Find
            .Text = "Specific Vendor Name"
            If .Execute Then
                .Text = "Vendor Total"
                If .Execute Then
                    ' Found, so select and copy the text after it
                    objWord.Selection.Collapse Direction:=1 ' wdCollapseEnd
                    objWord.Selection.MoveEndUntil CSet:=vbCr
                    objWord.Selection.Copy
                    ' Paste into active worksheet
                    ActiveSheet.Paste Destination:=Range("A1")
                End If
            End If
        End With
    
    ExitHandler:
        ' Clean up
        If blnStart And Not objWord Is Nothing Then
            objWord.Quit SaveChanges:=False
        End If
        Exit Sub
    
    ErrHandler:
        ' Display error message
        MsgBox Err.Description, vbExclamation
        Resume ExitHandler
    End Sub


    Regards, Hans Vogelaar

    Monday, May 14, 2012 9:42 PM
  • 

    Hi Hans,

    Thank you so much for your help you have gotten me farther than I anticipated!

    But I am in need a little more assistance, if possible. These are the problems I am running into.

     If Word is not opened, then it just stops. No error message. I see there is a sub routine for it, but it just seems to ignore it. Small problem really.

    So, when I ran the macro with the vendor name it seems to just not do anything. So, I went line by line with run to cursor and discovered that it is finding the text (Vendor Name), but then copies the Vendor Name to the Clipboard instead of the Vendor total. Nothing is actually pasted into Excel. Am I missing something?

    Thank you!

    Tuesday, May 15, 2012 7:00 PM
  • As I wrote, the code that I posted was only an example, it was not intended as a complete solution.

    However, I don't understand why it doesn't work if Word isn't running.

    How are the data stored in the Word document? As text, with tab characters between the information, or in the form of a table?


    Regards, Hans Vogelaar

    Tuesday, May 15, 2012 7:16 PM
  • I understand and appreciate your help. I am very grateful that I can at least get the word doc open.

    The problems is always the data. The export is not a clean export. Everything except the vendor total is stored as a table. Vendor total is on its on line and tabbed out. I would prefer to export the data to access or at least excel since they are are a little easier to manipulate, but I have no say of the export. 

    Currently what I do with about 100 sets of data spread over 25 worksheets and 10 word files is use find to find the vendor then the vendor total. Copy the data and then paste it in excel. I am just trying to automate this a little since it can take up a good portion of my day and must be done daily. 

    Thank you

    Tuesday, May 15, 2012 7:35 PM
  • Are you sure that "Vendor Total" is not in a table?


    Regards, Hans Vogelaar

    Tuesday, May 15, 2012 8:05 PM
  • yes it is very strange how it exports. The table export into text boxes and then the Vendor Totals are not part of the text box and look like they are in line, but if you try to move it into excel through a cvs file you will get several vendor totals in one cell. 

    I am getting farther just not close enough. If I get some workable code I will post it here. I am trying to break it down into components and then seeing if I can get it to work all together.

    Tuesday, May 15, 2012 8:41 PM
  • Would it be possible to upload a sample document (without sensitive information) to a site such as https://skydrive.live.com or http://www.dropbox.com ? Then share the uploaded file and post a link to it here.

    That would give me (and others) a chance of experimenting.


    Regards, Hans Vogelaar

    Tuesday, May 15, 2012 9:35 PM
  • The instance of Word is not visible unless you use

            objWord.Visible = True

    after

    Set objWord = CreateObject(Class:="Word.Application")

    • Proposed as answer by Jonesy-boy Friday, October 05, 2012 9:24 AM
    Friday, October 05, 2012 9:23 AM