×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Getting MS Word file name along with Paragraphs into Excel

Getting MS Word file name along with Paragraphs into Excel

Getting MS Word file name along with Paragraphs into Excel

(OP)
Hello Fellows,

Using below attached VB code to extract few starting paragraphs from Word into Excel. Working great.

Now, I also want to include the word file name to be extracted from where the paragraphs has come.

Please help me as I am totally newbie here.

CODE --> VB

Sub getWordData()
Dim wdApp As New Word.Application
Dim myDoc As Word.Document
Dim CCtl As Word.Paragraph
Dim myFolder As String, strFile As String
Dim myWkSht As Worksheet, i As Long, j As Long


'Open Folder Dialog Box Option
With Application.FileDialog(msoFileDialogFolderPicker)
    .Title = "Please select a folder"
    .Show
    .AllowMultiSelect = False
       
       If .SelectedItems.Count = 0 Then 'If no folder is selected, abort
            MsgBox "You did not select a folder"
          Exit Sub
       End If
    myFolder = .SelectedItems(1) & "\" 'Assign selected folder to MyFolder
End With
'End Open Folder Dialog Box Option


Application.ScreenUpdating = False

If myFolder = "" Then Exit Sub

Set myWkSht = ActiveSheet
ActiveSheet.Cells.Clear

 
 i = myWkSht.Cells(myWkSht.Rows.Count, 1).End(xlUp).Row
 strFile = Dir(myFolder & "\*.doc", vbNormal)
 
 While strFile <> ""
 i = i + 1
 
 Set myDoc = wdApp.Documents.Open(Filename:=myFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False)
 
 With myDoc
    j = 0
        For Each CCtl In .Paragraphs
            j = j + 1
            myWkSht.Cells(i, j) = CCtl.Range.Text
            If j = 10 Then Exit For
        Next
    myWkSht.Columns.AutoFit
 End With
 
 myDoc.Close SaveChanges:=False
 strFile = Dir()
 Wend
 wdApp.Quit
 Set myDoc = Nothing: Set wdApp = Nothing: Set myWkSht = Nothing
 Application.ScreenUpdating = True
 
End Sub 

RE: Getting MS Word file name along with Paragraphs into Excel

Hi,

CODE

‘
With myDoc
    j = 0
        For Each CCtl In .Paragraphs
            j = j + 1
            myWkSht.Cells(i, j) = CCtl.Range.Text
            If j = 10 Then Exit For
        Next
        myWkSht.Cells(i, j+1) = .Name
    myWkSht.Columns.AutoFit
 End With 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Getting MS Word file name along with Paragraphs into Excel

(OP)
Skip, You are a Legend, Working great,

Just a little bit further improvement, Code is printing name in the last column, Is it possible to get file name into first Column?

RE: Getting MS Word file name along with Paragraphs into Excel

CODE

With myDoc
    j = 1
    myWkSht.Cells(i, j) = .Name

    For Each CCtl In .Paragraphs
        j = j + 1
        myWkSht.Cells(i, j) = CCtl.Range.Text
        If j = 11 Then Exit For
    Next

    myWkSht.Columns.AutoFit
 End With 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close