×
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

Loop through a folder, Copy (first few lines) data from .docx, Paste into individual rows (Excel)

Loop through a folder, Copy (first few lines) data from .docx, Paste into individual rows (Excel)

Loop through a folder, Copy (first few lines) data from .docx, Paste into individual rows (Excel)

(OP)
Hi, Totally newbie here, please bear with me.

I need to copy a specific data from multiple word documents/files. All files are in a single folder. The contents of the Word Documents are not in a specific format.
No specific marks or anything to distinguish required data from the rest of the content. What I figure out is that, the data I need is in the first few lines (Line 1 to 7).

After copying, the data need to paste into Excel. Each document will have their own row with individual column for each line.

I will be very thankful for your efforts and time.

Thanks

RE: Loop through a folder, Copy (first few lines) data from .docx, Paste into individual rows (Excel)

Hi,

Welcome to Tek-Tips.

What exactly have you done so far to accomplish your task?

Please post any code that you have so far. Explain where you’re have difficulties.

What do you mean by a “line” in MS Word. Word has sentences snd paragraphs. Please post a descriptive sample.

How much experience do you have writing VBA code in Excel?

Please answer each of these questions.

Skip,

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

RE: Loop through a folder, Copy (first few lines) data from .docx, Paste into individual rows (Excel)

(OP)
Thanks Skip for your quick reply, Highly Appreciated.

What exactly have you done so far to accomplish your task?
I just found VB Code on the internet, which is almost doing what I required. But that code extract data from Content Controls

Please post any code that you have so far. Explain where you have difficulties.
Code is attached below. Problem I am facing is Dim CCtl As Word.ContentControl, My Files dont have Content Controls.

What do you mean by a “line” in MS Word. Word has sentences snd paragraphs. Please post a descriptive sample.
Line means Enter. User type few words then pressed enter. Again few words, then Enter

How much experience do you have writing VBA code in Excel?
No Experience, But I have a basic understanding of codes so I can understand what means of syntexs, but have NO ability to write one of my own.

Please answer each of these questions.

CODE --> VB

Sub DateFromDocx()

Dim wdApp As New Word.Application
Dim myDoc As Word.Document
Dim CCtl As Word.ContentControl
Dim myFolder As String, strFile As String
Dim myWkSht As Worksheet, i As Long, j As Long


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


Application.ScreenUpdating = False

If myFolder = “” Then Exit Sub
Set myWkSht = ActiveSheet
ActiveSheet.Cells.Clear

Range(“A1”) = "Name"
Range(“A1”).Font.Bold = True

Range(“B1”) = "Address"
Range(“B1”).Font.Bold = True

Range(“C1”) = “Gender”
Range(“C1”).Font.Bold = True

Range(“D1”) = "Date of Birth"
Range(“D1”).Font.Bold = True

Range(“E1”) = “Email”
Range(“E1”).Font.Bold = True

Range(“F1”) = “Mobile”
Range(“F1”).Font.Bold = True


i = myWkSht.Cells(myWkSht.Rows.Count, 1).End(xlUp).Row
strFile = Dir(myFolder & "\*.docx", 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 .ContentControls
j = j + 1
myWkSht.Cells(i, j) = CCtl.Range.Text
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: Loop through a folder, Copy (first few lines) data from .docx, Paste into individual rows (Excel)

>My Files dont have Content Controls.

They generally don't unless the user has manually added any of the various content controls (normally when creating a form)

>Line means Enter

Ok, so paragraphs, in your scenario you mean paragraphs.

So instead of .ContentControls you might want to have a look at .Paragraphs



RE: Loop through a folder, Copy (first few lines) data from .docx, Paste into individual rows (Excel)

Change...

CODE

Dim CCtl As Word.ContentControl 
...to...

CODE

Dim CCtl As Word.Paragraph 

Change...

CODE

For Each CCtl In .ContentControls
j = j + 1
myWkSht.Cells(i, j) = CCtl.Range.Text
Next
myWkSht.Columns.AutoFit
End With 
...to...

CODE

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

ALSO, change these SLANTED QUOTES “” to NORMAL QUOTES "" in EVERY usage.

BTW, do you care what document the 7 lines reside in?

Skip,

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

RE: Loop through a folder, Copy (first few lines) data from .docx, Paste into individual rows (Excel)

(OP)
Thanks Skip, Thankyou very much, its worked.

RE: Loop through a folder, Copy (first few lines) data from .docx, Paste into individual rows (Excel)

CODE

Dim CCtl As Word.Paragraph 
Paragraph SINGULAR!!!

Skip,

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

RE: Loop through a folder, Copy (first few lines) data from .docx, Paste into individual rows (Excel)

(OP)
Worked for me. Previously its was not, But I copy paste again and Vola

RE: Loop through a folder, Copy (first few lines) data from .docx, Paste into individual rows (Excel)

👍

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