jigar1276
08-30-2008, 04:56 AM
Hi Experts,
I have a folder containing many excel files with same format. I have written the code for opening each file and copying 3 cells (B3, E3 and E13) from each sheet of each file and paste it to "CheckPIdata.xls".
The code written is:
Sub CopyPIDATA()
'
'
Dim fso As Object, folder, filelist, file
Dim ws As Worksheet, wb As Workbook
Set fso = CreateObject("Scripting.filesystemobject")
Set folder = fso.getfolder("C:\Documents and Settings\baraijig\Desktop\pi")
Set filelist = folder.Files
i = 1
For Each file In filelist
Set wb = Workbooks.Open(folder & "\" & file.Name)
For Each ws In wb.Worksheets
i = i + 1
Workbooks("checkPIdata").Worksheets("Sheet1").Range("A" & i).Value = ws.Range("B3").Value
Workbooks("checkPIdata").Worksheets("Sheet1").Range("B" & i).Value = ws.Range("E3").Value
Workbooks("checkPIdata").Worksheets("Sheet1").Range("C" & i).Value = ws.Range("E13").Value
Workbooks(file.Name).Close
Next ws
Set ws = Nothing
Set file = Nothing
Next file
End Sub
The above code works fine. It is opening each file in the folder and copying data to "CheckPIdata.xls". The problem is that the data is copied only from first sheet of each file and not all the sheets of each files.
Filenames and sheetnames are not fixed.
Please help me to rectify my code so that it copies the data from each sheet instead of only one sheet.
Thanks in advance.
I have a folder containing many excel files with same format. I have written the code for opening each file and copying 3 cells (B3, E3 and E13) from each sheet of each file and paste it to "CheckPIdata.xls".
The code written is:
Sub CopyPIDATA()
'
'
Dim fso As Object, folder, filelist, file
Dim ws As Worksheet, wb As Workbook
Set fso = CreateObject("Scripting.filesystemobject")
Set folder = fso.getfolder("C:\Documents and Settings\baraijig\Desktop\pi")
Set filelist = folder.Files
i = 1
For Each file In filelist
Set wb = Workbooks.Open(folder & "\" & file.Name)
For Each ws In wb.Worksheets
i = i + 1
Workbooks("checkPIdata").Worksheets("Sheet1").Range("A" & i).Value = ws.Range("B3").Value
Workbooks("checkPIdata").Worksheets("Sheet1").Range("B" & i).Value = ws.Range("E3").Value
Workbooks("checkPIdata").Worksheets("Sheet1").Range("C" & i).Value = ws.Range("E13").Value
Workbooks(file.Name).Close
Next ws
Set ws = Nothing
Set file = Nothing
Next file
End Sub
The above code works fine. It is opening each file in the folder and copying data to "CheckPIdata.xls". The problem is that the data is copied only from first sheet of each file and not all the sheets of each files.
Filenames and sheetnames are not fixed.
Please help me to rectify my code so that it copies the data from each sheet instead of only one sheet.
Thanks in advance.