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.

While not perfect, the closest you can get when you give control like that might be MSWord's RecentFiles().

e.g.

Code:

---

'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

---

Code:

---

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

---

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?

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.

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.

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?

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.

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.

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.