Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Compilation Macro
Hi All,
thank you very much for your previous helps. I have one problem here. I have a folder where i get data in .xls format. I get nearly hundred files a day. I need to manually sit and cut copy paste all the data so that i could have all the data on a single excel sheet. Request you to design a macro for the same. Many thanks -- Varun Nair |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Compilation Macro
You might need to change the code to get the last cell depending upon your
data structure Option Explicit Dim oFSO Sub LoopFolders() Set oFSO = CreateObject("Scripting.FileSystemObject") selectFiles "c:\MyTest" Set oFSO = Nothing End Sub '--------------------------------------------------------------------------- Sub selectFiles(sPath) '--------------------------------------------------------------------------- Dim Folder As Object Dim Files As Object Dim file As Object Dim fldr Set Folder = oFSO.GetFolder(sPath) For Each fldr In Folder.Subfolders selectFiles fldr.Path Next fldr For Each file In Folder.Files If file.Type = "Microsoft Excel Worksheet" Then Workbooks.Open Filename:=file.Path ActiveWorkbook.Worksheets(1).UsedRange.Copy _ ThisWorkbook.Worksheets(1).Range("A1").End(xlDown) .Offset(1,0) End If Next file End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Varun Nair" wrote in message ... Hi All, thank you very much for your previous helps. I have one problem here. I have a folder where i get data in .xls format. I get nearly hundred files a day. I need to manually sit and cut copy paste all the data so that i could have all the data on a single excel sheet. Request you to design a macro for the same. Many thanks -- Varun Nair |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Compilation Macro
Hi Bob,
All the files to be compiled are in a single folder. would this macro work for even such files -- Varun Nair "Bob Phillips" wrote: You might need to change the code to get the last cell depending upon your data structure Option Explicit Dim oFSO Sub LoopFolders() Set oFSO = CreateObject("Scripting.FileSystemObject") selectFiles "c:\MyTest" Set oFSO = Nothing End Sub '--------------------------------------------------------------------------- Sub selectFiles(sPath) '--------------------------------------------------------------------------- Dim Folder As Object Dim Files As Object Dim file As Object Dim fldr Set Folder = oFSO.GetFolder(sPath) For Each fldr In Folder.Subfolders selectFiles fldr.Path Next fldr For Each file In Folder.Files If file.Type = "Microsoft Excel Worksheet" Then Workbooks.Open Filename:=file.Path ActiveWorkbook.Worksheets(1).UsedRange.Copy _ ThisWorkbook.Worksheets(1).Range("A1").End(xlDown) .Offset(1,0) End If Next file End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Varun Nair" wrote in message ... Hi All, thank you very much for your previous helps. I have one problem here. I have a folder where i get data in .xls format. I get nearly hundred files a day. I need to manually sit and cut copy paste all the data so that i could have all the data on a single excel sheet. Request you to design a macro for the same. Many thanks -- Varun Nair |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Compilation Macro
This code ONLY works on a single folder.
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Varun Nair" wrote in message ... Hi Bob, All the files to be compiled are in a single folder. would this macro work for even such files -- Varun Nair "Bob Phillips" wrote: You might need to change the code to get the last cell depending upon your data structure Option Explicit Dim oFSO Sub LoopFolders() Set oFSO = CreateObject("Scripting.FileSystemObject") selectFiles "c:\MyTest" Set oFSO = Nothing End Sub '--------------------------------------------------------------------------- Sub selectFiles(sPath) '--------------------------------------------------------------------------- Dim Folder As Object Dim Files As Object Dim file As Object Dim fldr Set Folder = oFSO.GetFolder(sPath) For Each fldr In Folder.Subfolders selectFiles fldr.Path Next fldr For Each file In Folder.Files If file.Type = "Microsoft Excel Worksheet" Then Workbooks.Open Filename:=file.Path ActiveWorkbook.Worksheets(1).UsedRange.Copy _ ThisWorkbook.Worksheets(1).Range("A1").End(xlDown) .Offset(1,0) End If Next file End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Varun Nair" wrote in message ... Hi All, thank you very much for your previous helps. I have one problem here. I have a folder where i get data in .xls format. I get nearly hundred files a day. I need to manually sit and cut copy paste all the data so that i could have all the data on a single excel sheet. Request you to design a macro for the same. Many thanks -- Varun Nair |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Project - Forms Data Compilation | Excel Discussion (Misc queries) | |||
Data Compilation Macro | Excel Programming | |||
Data compilation within a single workbook | Excel Worksheet Functions | |||
summing up daily data in a monthly compilation | Excel Worksheet Functions | |||
EXCEL VBA - Data compilation coming in from an external feed | Excel Programming |