CaatalyyN
10-20-2011, 04:04 AM
Hy all ,
First of all let me apologise if this was already posted and answered . If there is a good known post linked to this issue please post me the link .
My problem :
I'm trying to understand how to modify this code below , so I can acces subfolders from 1 specific directory ( my example : " C:\Desktop\TEST MULTIPLE PATHS\ " - main folder ) , and then , from each subfolder found in my main folder , to add al the data of each .xls file in one main .xls ( this is already figured out and working - for my project .. but for each .xls file in 1 folder :( ) ...
I searched the web and found the function Dir() , but I can't understand how to make it work . ( forgot to tell that I'm using excel 2007 .. and the function/application .filesearch doesen't work ... )
My " import excel " code-macro is the one listed here .. please tell me how to twik it .. to make it work how I want it to work : pray2:
Sub Import_Excel()
'
' Macro3 Macro
' Macro recorded
'
'
Range("C6").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.ClearContents
Range("C6").Select
j = 2
myline = 6
While Sheets("Lista fisiere sucursale").Cells(j, 1) <> NIL
Cells(3, 4) = Sheets("Lista fisiere sucursale").Cells(j, 1)
myname = ActiveWorkbook.Name
MyFile = Dir("C:\Desktop\TEST MULTIPLE PATHS\")
Workbooks.Open Filename:=Cells(1, 15) & Cells(3, 4)
newname = ActiveWorkbook.Name
Windows(myname).Activate
n = 10
n = Sheets("Lista fisiere sucursale").Cells(j, 4)
shname = Sheets("Lista fisiere sucursale").Cells(j, 2)
start_col1 = Sheets("Lista fisiere sucursale").Cells(j, 3)
end_col1 = Sheets("Lista fisiere sucursale").Cells(j, 3) + 2 + Sheets("Lista fisiere sucursale").Cells(j, 5)
start_col2 = Sheets("Lista fisiere sucursale").Cells(j, 6)
end_col2 = Sheets("Lista fisiere sucursale").Cells(j, 6) + Sheets("Lista fisiere sucursale").Cells(j, 5) - 1
While Workbooks(newname).Worksheets(shname).Cells(n, start_col1) <> NIL
ind1 = 3
For ind = start_col1 To end_col1
Cells(myline, ind1) = Workbooks(newname).Worksheets(shname).Cells(n, ind)
ind1 = ind1 + 1
Next ind
ind1 = 12
For ind = start_col2 To end_col2
Cells(myline, ind1) = Workbooks(newname).Worksheets(shname).Cells(n, ind)
ind1 = ind1 + 1
Next ind
myline = myline + 1
n = n + 1
Wend
Windows(newname).Activate
ActiveWindow.Close
j = j + 1
Wend
Range("R5:AC5").Select
Selection.Copy
Range("R5").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Range("R6:AC1347").Select
ActiveSheet.Paste
Range("R5").Select
End Sub
That red bold text is the part where I'm not so good twiking it ... :( ... if anyone can help please be wise with me .. don't mock me for my english .. I know it's not so good ... but I'm trying
I wish you all the best in everyting u do !
cheers :beerchug:
PS : I'm the newest newbie in VBA ... :(
First of all let me apologise if this was already posted and answered . If there is a good known post linked to this issue please post me the link .
My problem :
I'm trying to understand how to modify this code below , so I can acces subfolders from 1 specific directory ( my example : " C:\Desktop\TEST MULTIPLE PATHS\ " - main folder ) , and then , from each subfolder found in my main folder , to add al the data of each .xls file in one main .xls ( this is already figured out and working - for my project .. but for each .xls file in 1 folder :( ) ...
I searched the web and found the function Dir() , but I can't understand how to make it work . ( forgot to tell that I'm using excel 2007 .. and the function/application .filesearch doesen't work ... )
My " import excel " code-macro is the one listed here .. please tell me how to twik it .. to make it work how I want it to work : pray2:
Sub Import_Excel()
'
' Macro3 Macro
' Macro recorded
'
'
Range("C6").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.ClearContents
Range("C6").Select
j = 2
myline = 6
While Sheets("Lista fisiere sucursale").Cells(j, 1) <> NIL
Cells(3, 4) = Sheets("Lista fisiere sucursale").Cells(j, 1)
myname = ActiveWorkbook.Name
MyFile = Dir("C:\Desktop\TEST MULTIPLE PATHS\")
Workbooks.Open Filename:=Cells(1, 15) & Cells(3, 4)
newname = ActiveWorkbook.Name
Windows(myname).Activate
n = 10
n = Sheets("Lista fisiere sucursale").Cells(j, 4)
shname = Sheets("Lista fisiere sucursale").Cells(j, 2)
start_col1 = Sheets("Lista fisiere sucursale").Cells(j, 3)
end_col1 = Sheets("Lista fisiere sucursale").Cells(j, 3) + 2 + Sheets("Lista fisiere sucursale").Cells(j, 5)
start_col2 = Sheets("Lista fisiere sucursale").Cells(j, 6)
end_col2 = Sheets("Lista fisiere sucursale").Cells(j, 6) + Sheets("Lista fisiere sucursale").Cells(j, 5) - 1
While Workbooks(newname).Worksheets(shname).Cells(n, start_col1) <> NIL
ind1 = 3
For ind = start_col1 To end_col1
Cells(myline, ind1) = Workbooks(newname).Worksheets(shname).Cells(n, ind)
ind1 = ind1 + 1
Next ind
ind1 = 12
For ind = start_col2 To end_col2
Cells(myline, ind1) = Workbooks(newname).Worksheets(shname).Cells(n, ind)
ind1 = ind1 + 1
Next ind
myline = myline + 1
n = n + 1
Wend
Windows(newname).Activate
ActiveWindow.Close
j = j + 1
Wend
Range("R5:AC5").Select
Selection.Copy
Range("R5").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Range("R6:AC1347").Select
ActiveSheet.Paste
Range("R5").Select
End Sub
That red bold text is the part where I'm not so good twiking it ... :( ... if anyone can help please be wise with me .. don't mock me for my english .. I know it's not so good ... but I'm trying
I wish you all the best in everyting u do !
cheers :beerchug:
PS : I'm the newest newbie in VBA ... :(