Jacques
Fri May 09 15:18:41 CDT 2008
Hello Yossy
Sorry, If you begin in VBA, this example is a few complexe ... and my
english is very bad
For scan the folder the is :
Function MSOStockerFichiers(Chemin As String) As Integer
'_Stocker les fichiers d'un dossier
MSOStockerFichiers = 0
Erase TZ5_Fichier,
TZ5_nb = 0
If MonFSO.FolderExists(Chemin) = False Then GoTo E1
For Each ForObject In MonFSO.GetFolder(Chemin).Files
With ForObject
TZ5_nb = TZ5_nb + 1
TZ5_Fichier(TZ5_nb) = .Name
End With
Next
MSOStockerFichiers = TZ5_nb
FP: Exit Function
'=
E1: MsgBox "Dossier non trouvé " & Chemin: GoTo FP
End Function
For scan the workbook the code is :
'Consolider les fiches
For I = 1 To TZ5_nb
If TZ5_Fichier(I) = Empty Then GoTo F1
On Error GoTo E3
Workbooks.Open Filename:=O_Chemin & "\" & TZ5_Fichier(I), _
ReadOnly:=True, UpdateLinks:=0
Worksheets(1).Select
W_Pfin = MSOLfin(Cells(A_Ldeb - 1, 1))
For L = A_Ldeb To W_Pfin
T2_nbL = T2_nbL + 1
On Error Resume Next
For C = 1 To T2_nbC
T2_Val(T2_nbL, C) = Cells(L, C)
Next
On Error GoTo 0
Next
S1: On Error GoTo 0
ActiveWorkbook.Close SaveChanges:=False
F1: On Error GoTo 0
Next
For creat the result the code is :
'Actualiser la consolidation
L = A_Ldeb - 1
Worksheets(1).Select
For I = 1 To T2_nbL
L = L + 1
For C = 1 To T2_nbC
Cells(L, C) = T2_Val(I, C)
Next
Next
A_Fichier = A_Prefixe & "." & Format(Date, "yyyy-mm-dd") & R_Ext
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=O_Chemin & "\" &
A_Fichier
Application.DisplayAlerts = True
You modify this code for your object
J a c q u e s
"Yossy" <Yossy@discussions.microsoft.com> a écrit dans le message de
news:94BCF3FF-F0C0-4421-8080-AE10AF33753E@microsoft.com...
> Thanks for your help. I am new to macro and I have no idea what you have
> explained below.
>
> Please elaborate further. I looked into Ron's code
>
http://www.rondebruin.nl/copy3.htm but there is no detail info about
> copying
> and seperating multiple worksheet in respective multiple workbook to
> another
> worksheet
>
> "Jacques ALARDET" wrote:
>
>> Hello Yossy
>>
>> The methode is :
>> 1. Read the Folder and stocke the name workbook in a table T1 in memory
>> 2. For each Workbook in the table T1 in memory
>> 2a Open the workbook
>> 2b Stocke the contain of the sheet in a other table T2 in memory
>> 2c Close the workbook
>> 3. Create a new workbook
>> 4. Fill the sheet with the table T2
>>
>> J a c q u e s
>>
>> "Yossy" <Yossy@discussions.microsoft.com> a écrit dans le message de
>> news:F219F1C1-955A-4BCF-A835-2C48B728B056@microsoft.com...
>> > how do i Merge muliple sheets in multiple workbook (in same folder)
>> > below
>> > each other. However each respective sheet should go to a new sheet.
>> > Note
>> > all
>> > sheets in individual workbook are named same.
>> >
>> > Please help me. all responses appreciated.
>> >
>> > Thanks
>> >
>>
>