Word / Excel Question

[raven39]

I have 3 salesmen that want to know if they can copy the name of their word document and put it into an Excel spreadsheet. I have tried a bunch of ways, no luck. I have never even heard of doing this. It is not the pathname they want just the name of the file. ie: "quote-10202", not the c:\mydocuments\quote-10202.

Any suggestions?

 

[SkipVought]

Hi,

"...copy the name of their word document..."

Copy from WHERE?

Skip,

_[red][/red]

 

[buitenhek]

Not sure either what you need. 2 suggestions
1. Copy (cntl C) from Word and paste into cell of Excel
2. In Excel, Insert-Object-Microft Word Doc, copy and paste from Word. This will allow movement to any location within spreatsheet (like EMBED function)
Regards
Peter

Regards
Peter Buitenhek
ProfitDeveloper.com

 

[TonyJollans]

Are you looking for a manual way to do this? Or a macro? if a macro, do you want it to run from Word or Excel? Do you want a way to copy the name to somewhere specific in some specific worksheet? Or just to the clipboard, or what?

Put another way, can you give a bit more detail of your requirement please?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[fumei]

Quite right. We need the actual requirements of what you want to do.

The general answer to the question: "if they can copy the name of their word document and put it into an Excel spreadsheet."

Yes.

Gerry
My paintings and sculpture

 

[dcompto]

If you just want the file name inserted as text in Excel, you could...

In your Word document (maybe at the end of the doc),
Insert > Field
Categories: Document Information
Filed names: FileName
OK

This will insert the file name in the Word document.
Then you can click once in the left margin of the line containing the file name then press Ctrl-C to copy to the clipboard. Now, paste it into Excel.

Of course a simple macro assigned to a toolbar button could do everything through copying it to the clipboard, in addition to deleting the field code from the Word document if you don't want it to stay there, thereby becoming only 2 steps:

(1) Press button to run the macro
(2) Press Ctrl-V (paste) in the Excel cell where you want the Word document file name to be placed.

 

[dcompto]

Using the macro recorder (with comments in red added by me), it created:

Sub GetFileName()
'
' GetFileName Macro
'
[COLOR=red]' move to end of document[/color red]
    Selection.EndKey Unit:=wdStory
[COLOR=red]' create FileName field code[/color red]
    Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, Text:= _
        "FILENAME  ", PreserveFormatting:=True
[COLOR=red]' select the entire field code[/color red]
    Selection.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend
[COLOR=red]' copy the field code[/color red]
    Selection.Copy
[COLOR=red]' cut the field code[/color red]
    Selection.Cut
End Sub

So now it's sitting on the clipboard waiting to be pasted into Excel.

P.S. Of course, copy AND cut is redundant.

 

[fumei]

Actually, you don't need to do all that Selection stuff with a field. Further, that would only works on one document at a time. You can get the ActiveDocument name with:

ActiveDocument.Name

It is a string.

Or, if you want, build an array of all the documents Word has open with:

Sub GetFileNames()
Dim DocNames() As String
Dim oDoc As Document
Dim i As Integer
For Each oDoc In Application.Documents
    ReDim Preserve DocNames(i)
    DocNames(i) = oDoc.Name
    i = i + 1
Next
End Sub

Now you can use that array to put each item into cells in Excel.

Gerry
My paintings and sculpture

 

[Gavona]

Low tech way:
Open the word document
File, Properties, General tab
Highlight the filename
Ctrl-C to copy
Go to excel document
Ctrl-v to paste


Gavin

 

[dcompto]

Now you can use that array to put each item into cells in Excel.

What does this mean? Use it how in Excel?

 

[SkipVought]

You loop thru the array using a For...Next statement.

Where, in the workbook.worksheet do you want the data.

You really ought to post your question in a NEW THREAD in VBA Visual Basic for Applications (Microsoft) forum707

Skip,

_[red][/red]

 

[KenWright]

If they have the web toolbar open then they can just highlight the filename within that and copy and paste.

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------