Code for pulling data from multiple files into one master worksheet

Hi all,

Sorry for posting this as I know it has been asked before, however I don't know the right terminology to be able to complete the search and get what I am looking for, so hope you can help and point me in the right direction.

I don't really know much about forms in excel so normally create a worksheet that looks like a word document with blank cells for text etc. to be entered. This then links to a hidden worksheet that pulls the data from the cells into a row. This row can then be copied and pasted into a master spreadsheet. I appreciate this may not be the best way to do it, however its the only way I know and works.

However, this time I want to send out the spreadsheet to multiple people (100s) and request them to complete with their data and return. this means I will have 100s of files containing rows and don't want to have to manually copy and paste each line into the master worksheet. I have seen something where you put all the files into a folder and then run VBA/macro and it populates each row of data onto a new line in the master table.

My questions are;
What is the code?
Will this work with a hidden worksheet?
Will this work with a protected worksheet? (the hidden row is also locked to prevent editing)

Would also be interested if there is a better way to create a form in excel that puts the data into a separate worksheet, but would still have the same issue that i would receive 100s of files so would then need to run the same vba/macro to put all the data into a master worksheet.

Thanks in advance for your help,

I'm sorry as I cannot help you for the code, but a suggestion....

Originally Posted by MP1989

However, this time I want to send out the spreadsheet to multiple people (100s)
and request them to complete with their data and return.

If you have Google Account, maybe you can try Google Spreadsheet.
Let those hundreds people to access your spreadsheet and ask them to fill their data.

If you can do that, then

this means I will have 100s of files containing rows
and don't want to have to manually copy and paste each line into the master worksheet.

you will not have
hundred files and you don't have to copy each of them manually.

In order to merge many files/sheet into one, there should be unique header or number in all 100 files.

Re: In order to merge many files/sheet into one, there should be unique header or number in all 100 files.
Why?
One can copy/paste any column or row you want.
If data under a specific header and these headers are not all under the same column number is to be copied only then you might have a valid argument.

My worksheet that will be completed will contain a hidden worksheet (called data) and contain 2 rows of data (1 contains headers and 1 contains the data).

I want to put the files, when received into a folder and then be able to execute the VBA in the master workbook to pull the data from row 2 into the next blank line in a master spread sheet. I want to be able to keep adding new files received to the master and will move the files from the folder so that they aren't duplicated.

I found a similar function that works for word documents into excel but thought this may be a lot simpler, however I am struggling to find a solution.

Would be grateful for anyone's help

Originally Posted by jolivanes

Re: In order to merge many files/sheet into one, there should be unique header or number in all 100 files.
Why?
One can copy/paste any column or row you want.
If data under a specific header and these headers are not all under the same column number is to be copied only then you might have a valid argument.

I mean, if you want to merge the data through vba, there should be unique header or code.

I managed to find a solution on google

Sub GetFormData()
'Note: this code requires a reference to the Word object model
Application.ScreenUpdating = False
Dim wdApp As New Word.Application, wdDoc As Word.Document, CCtrl As Word.ContentControl
Dim strFolder As String, strFile As String, WkSht As Worksheet, i As Long, j As Long
strFolder = "FOLDER NAME HERE"
If strFolder = "" Then Exit Sub
Set WkSht = ActiveSheet
i = WkSht.Cells(WkSht.Rows.Count, 1).End(xlUp).Row
strFile = Dir(strFolder & "\*.docx", vbNormal)
While strFile <> ""
i = i + 1
Set wdDoc = wdApp.Documents.Open(Filename:=strFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False)
With wdDoc
j = 0
For Each CCtrl In .ContentControls
With CCtrl
Select Case .Type
Case Is = wdContentControlCheckBox
j = j + 1
WkSht.Cells(i, j).Value = .Checked
Case wdContentControlDate, wdContentControlDropdownList, wdContentControlRichText, wdContentControlText
j = j + 1
WkSht.Cells(i, j).Value = .Range.Text
Case Else
End Select
End With
Next
.Close SaveChanges:=False
End With
strFile = Dir()
Wend
wdApp.Quit
Set wdDoc = Nothing: Set wdApp = Nothing: Set WkSht = Nothing
Application.ScreenUpdating = True
End Sub