Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple files question
i have several files with many rows of data. Each file is in the format: filename-1.xls filename-2.xls filename-3.xls and so on... Is there an easy way to combine these files into 1 large file named: filename-total.xls ? I get about 3 to 12 of these files a day and i'm sick and tired of combining them manually! Please help... -- alexm999 ------------------------------------------------------------------------ alexm999's Profile: http://www.excelforum.com/member.php...fo&userid=4918 View this thread: http://www.excelforum.com/showthread...hreadid=519543 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple files question
Hi Alex
Look here for examples http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl "alexm999" wrote in message ... i have several files with many rows of data. Each file is in the format: filename-1.xls filename-2.xls filename-3.xls and so on... Is there an easy way to combine these files into 1 large file named: filename-total.xls ? I get about 3 to 12 of these files a day and i'm sick and tired of combining them manually! Please help... -- alexm999 ------------------------------------------------------------------------ alexm999's Profile: http://www.excelforum.com/member.php...fo&userid=4918 View this thread: http://www.excelforum.com/showthread...hreadid=519543 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple files question
I looked in there. Just examples - now how can I get them to automate. What if I have 3 files today, but only 2 tomorrow and 10 the next day? I need a copy and paste code... Can anyone help? -- alexm999 ------------------------------------------------------------------------ alexm999's Profile: http://www.excelforum.com/member.php...fo&userid=4918 View this thread: http://www.excelforum.com/showthread...hreadid=519543 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple files question
If the files are formatted in a fairly consistent way, I suggest either
writing an addin, or create a "master" workbook that goes out and grabs the files (maybe in a specific folder, or by using a File dialog to pick the files individually) and then pull them all into a new workbook. The code here could get fairly involved, depending on what the spreadsheets look like. If you want, you can send me a couple of the workbooks and I can shoot you back an example. My email address should be provided in the header of the post under view profile. Thanks, Johnny |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple files question
I have used this (from Ron's example) that allows me to select the exact
number of files to combine. It is not fully automated because you need to tell it which files to combine using a browser dialogue box. It has to be quicker than what you are doing now. Sub DAC_Report() Dim basebook As Workbook Dim mybook As Workbook Dim sourceRange As Range Dim destrange As Range Dim SourceRcount As Long Dim N As Long Dim rnum As Long Dim MyPath As String Dim SaveDriveDir As String Dim FName As Variant SaveDriveDir = CurDir 'MyPath = "C:\Data" 'ChDrive MyPath 'ChDir MyPath FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls", _ MultiSelect:=True) If IsArray(FName) Then Application.ScreenUpdating = False Set basebook = ThisWorkbook rnum = 1 basebook.Worksheets(1).Cells.Clear 'clear all cells on the first sheet For N = LBound(FName) To UBound(FName) Set mybook = Workbooks.Open(FName(N)) Set sourceRange = mybook.Worksheets(1).Range("A3:F53") SourceRcount = sourceRange.Rows.Count Set destrange = basebook.Worksheets(1).Cells(rnum, "A") basebook.Worksheets(1).Cells(rnum, "G").Value = mybook.Name ' This will add the workbook name in column D if you want sourceRange.Copy destrange ' Instead of this line you can use the code below to copy only the values ' With sourceRange ' Set destrange = basebook.Worksheets(1).Cells(rnum, "A"). _ ' Resize(.Rows.Count, .Columns.Count) ' End With ' destrange.Value = sourceRange.Value mybook.Close False rnum = rnum + SourceRcount Next End If Columns("G:G").Font.Size = 8 Columns("G:G").Font.Bold = True ' ChDrive SaveDriveDir ' ChDir SaveDriveDir Application.ScreenUpdating = True End Sub Mike F "alexm999" wrote in message ... I looked in there. Just examples - now how can I get them to automate. What if I have 3 files today, but only 2 tomorrow and 10 the next day? I need a copy and paste code... Can anyone help? -- alexm999 ------------------------------------------------------------------------ alexm999's Profile: http://www.excelforum.com/member.php...fo&userid=4918 View this thread: http://www.excelforum.com/showthread...hreadid=519543 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple files question
Hey, thanks for the code! Works great, but I need some modifications. I need the Data to copy all the columns. Sometimes they end with colum P sometimes they go to AJ. Is there a way to copy to the last column? Also, How do I make it an Add-In for a simple button. -- alexm999 ------------------------------------------------------------------------ alexm999's Profile: http://www.excelforum.com/member.php...fo&userid=4918 View this thread: http://www.excelforum.com/showthread...hreadid=519543 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple files question
Hi alex
You can find all the code on the page http://www.rondebruin.nl/copy3.htm For example this one http://www.rondebruin.nl/copy3.htm#header If you have the files in the same folder you can run this macro from a button Play with the code and learn from it -- Regards Ron de Bruin http://www.rondebruin.nl "alexm999" wrote in message ... Hey, thanks for the code! Works great, but I need some modifications. I need the Data to copy all the columns. Sometimes they end with colum P sometimes they go to AJ. Is there a way to copy to the last column? Also, How do I make it an Add-In for a simple button. -- alexm999 ------------------------------------------------------------------------ alexm999's Profile: http://www.excelforum.com/member.php...fo&userid=4918 View this thread: http://www.excelforum.com/showthread...hreadid=519543 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple files question
Hi Johnny,
I have a similar problem, can I send you my sample files. Briefly my problem is like .. I want to combine sheets such as advance(sheet1),deposits(sheet2), creditors(sheet3), so on...Sheet names are unique. In advance sheet validation must be done at h column starting row 6 for value & grab the row until value = "LLINE" or BLANK, similarly it should check value in g column for deposit sheet, i column in prepaid sheet, & so on... The consol file should have sheets advance, deposit, prepaid, & so on with data from all files. I am using excel 2002. "Johnny" wrote: If the files are formatted in a fairly consistent way, I suggest either writing an addin, or create a "master" workbook that goes out and grabs the files (maybe in a specific folder, or by using a File dialog to pick the files individually) and then pull them all into a new workbook. The code here could get fairly involved, depending on what the spreadsheets look like. If you want, you can send me a couple of the workbooks and I can shoot you back an example. My email address should be provided in the header of the post under view profile. Thanks, Johnny |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple files question
Sure
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple files question
Wow Johnny,
Where you on world tour. With Mr Ron's guidance, I finished it. Anyway I thank you very much for responding. "Johnny" wrote: Sure |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
merge multiple worksheets from multiple excel files into oneworksheet | Excel Discussion (Misc queries) | |||
how do i link multiple files (tif files) to one cell | Excel Discussion (Misc queries) | |||
view multiple files in multiple windows on multiple screens. | Excel Discussion (Misc queries) | |||
How can I view files chronologically when opening multiple files | Excel Discussion (Misc queries) | |||
Import multiple files macro can't find files | Excel Programming |