Copying data without opening another excel workbook

Hi

I have a few  200 excel files which are all of the same format(i.e. 1 worksheets per Excel file which name is "sheet1". The corresponding worksheets from each Excel file are named exactly the same as are the column headers.  i want to copy  them on one sheet. Finally, after searching, I Write following codes:

Entrance Data For Running Code is:

1-Directory File

2-Name Sheets (Name Sheet Should be the same in All excel Files)

3-Number Of Row

4-Start Number Of Row

5-Number Of column

6-Start Number Of Column

Sub ReadDataFromAllWorkbooksInFolder()

Dim FolderName As String, wbName As String, cValue, t As Variant

Dim wbList() As String, wbCount As Integer, i, j, r, s, k, l, p As Integer

Dim q As String

t = Application.InputBox("input Directory Files ")              ' input Directory Excel Files

    FolderName = t

      m = Application.InputBox("input Sheet Name Of Files ")    ' input Sheet Name

      k = InputBox("input number of row ")                      ' input number of row

      l = InputBox("input Start number of row")                 '  input Start number of row

     p = InputBox("input number of Column")                     ' input number of Column

     s = InputBox("input Start number of Column")               '  input Start number of Column

    ' create list of workbooks in foldername' --- Comment

    wbCount = 0

    wbName = Dir(FolderName & "" & "*.xls")

    While wbName <> ""

        wbCount = wbCount + 1

        ReDim Preserve wbList(1 To wbCount)

        wbList(wbCount) = wbName

        wbName = Dir

    Wend

    If wbCount = 0 Then Exit Sub

    ' get values from each workbook' --- Comment

    For i = 0 To wbCount - 1

         For r = 1 To k

          For j = 1 To p

                q = Cells(r + l - 1, j + s - 1).Address(RowAbsolute:=False, ColumnAbsolute:=False)

                 cValue = GetInfoFromClosedFile(FolderName, wbList(i + 1), (m), (q))

                 Cells(r + i * k + 1, 1).Formula = wbList(i + 1)

                 Cells(r + i * k + 1, j + 1).Formula = cValue

        Next j

         Next r

          Next i

  End Sub

Private Function GetInfoFromClosedFile(ByVal wbPath As String, _

    wbName As String, wsName As String, cellRef As String) As Variant

Dim arg As String

    GetInfoFromClosedFile = ""

    If Right(wbPath, 1) <> "" Then wbPath = wbPath & ""

    If Dir(wbPath & "" & wbName) = "" Then Exit Function

    arg = "'" & wbPath & "[" & wbName & "]" & _

        wsName & "'!" & Range(cellRef).Address(True, True, xlR1C1)

    On Error Resume Next

    GetInfoFromClosedFile = ExecuteExcel4Macro(arg)

End Function

Best Regards

Mr. Qasem Abbasi

Now this part of the code is giving me fits

.FormulaR1C1 = "=IF('C:\Users\ben biggs\Desktop" _

            & "[test.xlsm]Sheet1'!RC="""",NA(),'C:\Users\ben biggs\Desktop" _

            & "[test.xlsm]Sheet1'!RC)"

My data in the first workbook is in row 22, column 2.  My data in the 2nd workbook is in range of i5 to i11, so I changed the code to:

FormulaR22C2 = "=IF('C:\Users\ben biggs\Desktop\BOMServerFolder" _

& "[wwtconsumptionentry.xlsm]sheet1'!R5C9="""",NA(),'C:\Users\ben biggs\Desktop\BOMServerFolder" _

& "[wwtconsumptionentry.xlsm]sheet1'!R5C5)"

and it exploded, j/k, I get the error "object doesn't support this property or method"

There is unprotect command right?  But I don't know how to put that in the middle of that line of code to make it function.

Hi Bill thanks for the info,

I actually just got out of my newbie mindset and realized that for vba, the sheets remain named sheet1,sheet2 etc..  So i just referred to them as that and got the first line of code to work.  I also had to the RC to R1C1 because I guess the way the phrase is written it used the RC from earlier in the code (22,2).  

So solved two problems.

Now, how would I go about setting up the password thing.  The big problem is that the main workbook is accessing several smaller workbooks across the server that certain people are required to put information into.  The main workbook accesses all of them and places the values into the specified areas.  It would just be inefficient to have the boss man open all of the workbooks but at the same time - the information is highly confidential and I need the password protection.

Thanks,

Ben

Worksheets("BEBOM").Cells rather than BEBOM.Cells

>Is there a code in vba to automatically type the password?

You could try using SendKeys.  Otherwise no.

Is it a big problem to open the workbook? 

In that case you can specify the password in the Workbooks.Open statement and you can copy the data directly.

Bill Manville. Excel MVP, Oxford, England. www.manville.org.uk