Consulting

Results 1 to 10 of 10

Thread: Import Word document file name into Excel

  1. #1

    Import Word document file name into Excel

    Hi All,

    I am using Office 2010, 32 bit.

    Here is my current sequence of events:

    Click on a cell on an Excel sheet which is acting as a menu
    Within the "Private Sub Worksheet_SelectionChange" procedure execute a Select Case process as per target.address
    In this case Open an already created empty Word document using "ActiveWorkbook.FollowHyperlink" (thanks again, sassora for your solution)
    The user saves the document under another name and closes it
    Processing returns to the Excel sheet

    What I need is to import the newly created Word document file name into Excel for further processing. I have searched high and low and found some wonderful solutions for all sorts of things, but not this. I must, therefore, assume that I am being even dumber than usual and that the solution is really quite straightforward. Unless you are me. Any help would be much appreciated.

    Best regards.

  2. #2
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    While not perfect, the closest you can get when you give control like that might be MSWord's RecentFiles().

    e.g.
    'Requires Tools > References > Microsoft Word 14.0 Object Library
    Sub GetRecentFile()
        Dim wdApp As Word.Application
    
        On Error Resume Next
        Set wdApp = GetObject(, "Word.Application")
        If Err.Number <> 0 Then
            Set wdApp = CreateObject("Word.Application")
        End If
        On Error GoTo errorHandler
         
        With wdApp
          MsgBox .RecentFiles(1).Path & "\" & .RecentFiles(1).Name, vbInformation, "Most Recent File"
        End With
        
        Set wdApp = Nothing
         
    errorExit:
        On Error Resume Next
        Set wdApp = Nothing
        Exit Sub
     
    errorHandler:
        MsgBox "Unexpected error: " & Err.Number & vbLf & Err.Description
        Resume errorExit
    End Sub

  3. #3
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,646
    Sub M_snb()
        c00 = "G:\OF\nieuw_004.docx"
    
        With CreateObject("word.document")
            .saveas2 c00, 16
            .Close 0
        End With
    
        ThisWorkbook.FollowHyperlink c00
    End Sub

  4. #4
    Kenneth, thank you for your prompt response. I have implemented your code, but there is a slight glitch which I can't get around. When the message box displays it contains the name of the document as it was before being saved with a new name. I am calling your sub immediately after the FollowHyperlink statement, but even if I call it after the End Select (which is only for test purposes) it still displays the incoming document name. However, if I display a test message box between the Hyperlink statement and the call the correct name is displayed when your message box appears. Any thoughts?

  5. #5
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    Try a DoEvents in your code. As I said, there are many things that might gum it up. You might want to add an OnKey() and tell the user to press the key(s) once his thing is done.

  6. #6
    snb, thanks for your reply. If I am understanding your code correctly then I would have to know the new document name before executing it, which I don't since it is input by the user. Maybe I am misunderstanding.

  7. #7
    Kenneth, thanks again. The DoEvents doesn't do it. Also, i would rather not use OnKey as it sort of defeats the purpose of automation. Should I be using a different approach altogether?

  8. #8
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    Control is the key to automation. Once you give it up, most anything can happen. Getting control back can be done in one of several ways.

    Cases:
    1. You could tell the user to close all instances of MSWord when the task is completed. A loop would iterate until the application instance is not found.
    2. Like (1), take control back when the followed MSWord document object is no longer present by checking GetObject() in a loop.
    3. Use a folder and monitor the folder. When it changes, the user must be done saving a new file to that folder. This would only be viable if you make them work in a specific folder.
    4. etc.

  9. #9
    Thanks again, Kenneth. I will try out your suggestions a little later and let you know how it goes. I'm afraid I have to break just now to prepare supper, or else I will REALLY know what trouble means. Best regards.

  10. #10
    Hi, Kenneth. An update. I followed one of your suggestions and decided to monitor the folder, which is known. I found some pretty fancy code online and modified it slightly. Hey presto, it works. I had to do some string handling on its output, but the code does what I wanted. Great. Many thanks again for all your time, patience and assistance, Kenneth. What it is to have friends....
    Best regards.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •