PDA

View Full Version : Import Word document file name into Excel



El_Diabolo
12-12-2013, 07:38 AM
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.

Kenneth Hobs
12-12-2013, 08:38 AM
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

snb
12-12-2013, 09:24 AM
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

El_Diabolo
12-12-2013, 09:24 AM
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?

Kenneth Hobs
12-12-2013, 09:30 AM
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.

El_Diabolo
12-12-2013, 09:53 AM
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.

El_Diabolo
12-12-2013, 09:57 AM
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?

Kenneth Hobs
12-12-2013, 10:04 AM
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.

El_Diabolo
12-12-2013, 11:03 AM
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.

El_Diabolo
12-13-2013, 06:29 AM
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.