copy 200 workbooks from 1 directory with 200 subdirectory

[hosanna]

i am trying to write some vba code to copy 200 workbooks from 200 folders on a network.
example i have a directory called F
Within F i have staffstreamA
within STAFFSTREAMA i have 200 folders named as forth A1,A2,A3 ETC TO A200
Within folders A1 is a folder called SALES
within the SALES folder is a WORKBOOK called SALESDATA

SO REFERENCE TO THE WORKBOOK IS F:\STAFFSTREAMA\A1\SALES\SALESDATA_A1.XLS
I HAVE 200 OF THESE WHICH GO FROM A1 TO A200, folders with THE SAME named FOLDER CALLED SALES and same workbook called salesdata_A1 ETC.

I need to be able to go through each folder eg A1,A2 ETC OPEN THE protected workbook select a cell range from a specified worksheet eg JANSALES and copy the data to one worksheet on my local drive.
please help, thanks a billion.

[hkdani]

SO REFERENCE TO THE WORKBOOK IS F:\STAFFSTREAMA\A1\SALES\SALESDATA_A1.XLS
I HAVE 200 OF THESE WHICH GO FROM A1 TO A200, folders with THE SAME named FOLDER CALLED SALES and same workbook called salesdata_A1 ETC.

I need to be able to go through each folder eg A1,A2 ETC OPEN THE protected workbook select a cell range from a specified worksheet eg JANSALES and copy the data to one worksheet on my local drive.
please help, thanks a billion.

Your answer is in the name of your folder. Fortunately, the name is somewhat consistent:

Dim strFolderName as String, , strUseThisName as String, intCounter as Integer
strFolderName = "F:\STAFFSTREAMA\A1\SALES\SALESDATA_A"
for intCounter = 1 to 200
  strUseThisName = strFolderName & cstr(intCounter) & ".XLS"
  ' Enter code here to process your request
  ' ...................
  ' ...................
next intCounter

But this is only part of your request. Sounds like you'll need to program a VBA script in a separate Excel workbook (most likely your worksheet on your local drive) to accomplish what you're asking. So you probably could use the above as a starter in a VBA script in the Excel Program.

[hosanna]

Your answer is in the name of your folder. Fortunately, the name is somewhat consistent:

Dim strFolderName as String, , strUseThisName as String, intCounter as Integer
strFolderName = "F:\STAFFSTREAMA\A1\SALES\SALESDATA_A"
for intCounter = 1 to 200
  strUseThisName = strFolderName & cstr(intCounter) & ".XLS"
  ' Enter code here to process your request
  ' ...................
  ' ...................
next intCounter

But this is only part of your request. Sounds like you'll need to program a VBA script in a separate Excel workbook (most likely your worksheet on your local drive) to accomplish what you're asking. So you probably could use the above as a starter in a VBA script in the Excel Program.

Hi,
Thanks for your reply but what I meant was that I HAVE 200 OF THESE FOLDERS WITH 1 WORKBOOK IN EACH FOLDER which I need to bring together. an example of what I mean is below:
folder one= "F:\STAFFSTREAMA\A1\SALES\SALESDATA_A1"
folder two= "F:\STAFFSTREAMA\A2\SALES\SALESDATA_A2"
folder three= "F:\STAFFSTREAMA\A3\SALES\SALESDATA_A3"
folder four = "F:\STAFFSTREAMA\A4\SALES\SALESDATA_A4"
folder five= "F:\STAFFSTREAMA\A5\SALES\SALESDATA_A5"
And so on up to 200

200 separate workbooks named in sequence (salesdata_a20.xls
in 200 folders eg sales folder
in 200 different user areas also named in sequence eg a20 folder

[hkdani]

folder one= "F:\STAFFSTREAMA\A1\SALES\SALESDATA_A1"

Dim intCounter as Integer
Dim strFileName as String

For intCounter = 1 to 200
  strFileName = "F:\STAFFSTREAMA\A" & cstr(intCounter) & "\SALES\SALESDATA_A" _
      &  cstr(intCounter) & "\salesdata_a" & cstr(intCounter) & ".xls"
      ' Code to open your spreadsheet
      ' ............................................... 
next intCounter

I don't know if that's what you're looking for? But if you have the same file name, you won't have to use the last part of the above string: you can just the same name with no variable to create a new file name.

[hosanna]

Dim intCounter as Integer
Dim strFileName as String

For intCounter = 1 to 200
  strFileName = "F:\STAFFSTREAMA\A" & cstr(intCounter) & "\SALES\SALESDATA_A" _
      &  cstr(intCounter) & "\salesdata_a" & cstr(intCounter) & ".xls"
      ' Code to open your spreadsheet
      ' ............................................... 
next intCounter

I don't know if that's what you're looking for? But if you have the same file name, you won't have to use the last part of the above string: you can just the same name with no variable to create a new file name.

THANKS A BILLION. THIS IS EXACTLY WHAT I WAS LOOKING FOR , HOW TO REFERENCE ALL FILES.I HAVE NOW ADDED THIS CODE TO YOURS . THIS CODE OPENS EACH WORKBOOK COPIES A CELL RANGE FROM A SPECIFIED WORKSHEET ON TO A MASTER WORKBOOK. THERE SEEMS TO SEVERAL PROBLEMS , I AM VERY NEW TO VBA. PLEASE PLEASE HELP. THANKS A BILLION. THIS SUB COPYDATA() IS ACTIVATED WITH A BUTTON I CREATED LINKED TO A MACRO

Sub copydata()

Sheet1.Unprotect Password:="JAFFA"
Dim sh As Worksheet, bk As Workbook
Dim sh1 As Worksheet, rw As Long
Dim sName As String, rng As Range
Dim cell As Range, icol As Long
Set sh = ActiveSheet
rw = 1
Dim intCounter as Integer
Dim strFileName as String

For intCounter = 1 to 200
strFileName = "F:\STAFFSTREAMA\A" & cstr(intCounter) & "\SALES\SALESDATA_A" _
& cstr(intCounter) & "\salesdata_a" & cstr(intCounter) & ".xls"

sName = Dir("F:\STAFFSTREAMA\SALESDSTA\*.xls")

Do While sName <> ""
rw = rw + 1
Set bk = Workbooks.Open("F:\STAFFSTREAMA\A\" & sName)
With bk.Worksheets("SALES1")
.Unprotect Password:="JAFFA"

Set sh1 = bk.Worksheets("SALES1")

Set rng = sh1.Range("b2,b3,c13,m13,c15,m15")
icol = 2
sh1.Cells(rw, 1) = sName

For Each cell In rng
sh.Cells(rw, icol).Value = cell.Value
icol = icol + 1
Next
next intCounter
bk.Worksheets("SALES1").Protect Password:="JAFFA"
bk.Close SaveChanges:=False
sName = Dir()

End With

Loop

End Sub