|
Gather data from multiple excel files into one master excel file
I need to gather information from multiple files that all have the same
layout but different information. Is it possible to update this information into one master excel file from the other files??? Regards Mark Allen |
Gather data from multiple excel files into one master excel file
Hi Mark
See http://msdn.microsoft.com/en-us/library/cc837974.aspx Or check out my website -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... I need to gather information from multiple files that all have the same layout but different information. Is it possible to update this information into one master excel file from the other files??? Regards Mark Allen |
Gather data from multiple excel files into one master excel file
One way is to open each file and MOVE the sheets there to the master file -
Right Click on the sheet to MOVE and then select the BOOK (your master file) to move it to and the location in that BOOK. If you want to add the corresponding cells from all sheets to your master then that can also be done. "Mark Allen" wrote: I need to gather information from multiple files that all have the same layout but different information. Is it possible to update this information into one master excel file from the other files??? Regards Mark Allen |
Gather data from multiple excel files into one master excel fi
Hi Ron,
I am a complete novice to Visual Basic, I have looked at your sites and sorry but I am not sure what to do ??? Can you help please?? Mark "Ron de Bruin" wrote: Hi Mark See http://msdn.microsoft.com/en-us/library/cc837974.aspx Or check out my website -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... I need to gather information from multiple files that all have the same layout but different information. Is it possible to update this information into one master excel file from the other files??? Regards Mark Allen |
Gather data from multiple excel files into one master excel fi
I am a complete novice to Visual Basic
See http://www.rondebruin.nl/merge.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... Hi Ron, I am a complete novice to Visual Basic, I have looked at your sites and sorry but I am not sure what to do ??? Can you help please?? Mark "Ron de Bruin" wrote: Hi Mark See http://msdn.microsoft.com/en-us/library/cc837974.aspx Or check out my website -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... I need to gather information from multiple files that all have the same layout but different information. Is it possible to update this information into one master excel file from the other files??? Regards Mark Allen |
Gather data from multiple excel files into one master excel fi
Ron,
I have now completely lost myself... I have managed to fetch some data back but may be I have not explained correctly what I am looking to do: All the following files are in a folder "c:\OP Funnel" I have one master file called "Funnel OP Master.xls" I have six additional files called "Funnel OP 1.xls, 2.xls, 3.xls, 4.xls, 5.xls, 6.xls" I need to merge the data from "A36:E36" through to "A300:E300" if there is data there from the above six files I need to merge all this data in the file "Funnel OP Master.xls" into the range "A36:E36" through to "A10000:E10000". I am really not sure how to do this looking at your formulas in Visual basic.. I would very much appreciate your help on this subject Regards Mark "Ron de Bruin" wrote: Hi Mark See http://msdn.microsoft.com/en-us/library/cc837974.aspx Or check out my website -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... I need to gather information from multiple files that all have the same layout but different information. Is it possible to update this information into one master excel file from the other files??? Regards Mark Allen |
Gather data from multiple excel files into one master excel fi
Hi Mark
My code and the add-in create a new workbook with the data You can change the code to copy it in a existing workbook but first try the code and see if it is working. If it is working post back and post your code so I can change it -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... Ron, I have now completely lost myself... I have managed to fetch some data back but may be I have not explained correctly what I am looking to do: All the following files are in a folder "c:\OP Funnel" I have one master file called "Funnel OP Master.xls" I have six additional files called "Funnel OP 1.xls, 2.xls, 3.xls, 4.xls, 5.xls, 6.xls" I need to merge the data from "A36:E36" through to "A300:E300" if there is data there from the above six files I need to merge all this data in the file "Funnel OP Master.xls" into the range "A36:E36" through to "A10000:E10000". I am really not sure how to do this looking at your formulas in Visual basic.. I would very much appreciate your help on this subject Regards Mark "Ron de Bruin" wrote: Hi Mark See http://msdn.microsoft.com/en-us/library/cc837974.aspx Or check out my website -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... I need to gather information from multiple files that all have the same layout but different information. Is it possible to update this information into one master excel file from the other files??? Regards Mark Allen |
Gather data from multiple excel files into one master excel fi
RDBMERGE works great...does what I need...
Now how do I get it to populated an exsiting workbook... Star man... Regards Mark "Ron de Bruin" wrote: Hi Mark My code and the add-in create a new workbook with the data You can change the code to copy it in a existing workbook but first try the code and see if it is working. If it is working post back and post your code so I can change it -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... Ron, I have now completely lost myself... I have managed to fetch some data back but may be I have not explained correctly what I am looking to do: All the following files are in a folder "c:\OP Funnel" I have one master file called "Funnel OP Master.xls" I have six additional files called "Funnel OP 1.xls, 2.xls, 3.xls, 4.xls, 5.xls, 6.xls" I need to merge the data from "A36:E36" through to "A300:E300" if there is data there from the above six files I need to merge all this data in the file "Funnel OP Master.xls" into the range "A36:E36" through to "A10000:E10000". I am really not sure how to do this looking at your formulas in Visual basic.. I would very much appreciate your help on this subject Regards Mark "Ron de Bruin" wrote: Hi Mark See http://msdn.microsoft.com/en-us/library/cc837974.aspx Or check out my website -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... I need to gather information from multiple files that all have the same layout but different information. Is it possible to update this information into one master excel file from the other files??? Regards Mark Allen |
Gather data from multiple excel files into one master excel fi
Which code example have you try ?
I will not change the add-in for this but can show you how to change the code -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... RDBMERGE works great...does what I need... Now how do I get it to populated an exsiting workbook... Star man... Regards Mark "Ron de Bruin" wrote: Hi Mark My code and the add-in create a new workbook with the data You can change the code to copy it in a existing workbook but first try the code and see if it is working. If it is working post back and post your code so I can change it -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... Ron, I have now completely lost myself... I have managed to fetch some data back but may be I have not explained correctly what I am looking to do: All the following files are in a folder "c:\OP Funnel" I have one master file called "Funnel OP Master.xls" I have six additional files called "Funnel OP 1.xls, 2.xls, 3.xls, 4.xls, 5.xls, 6.xls" I need to merge the data from "A36:E36" through to "A300:E300" if there is data there from the above six files I need to merge all this data in the file "Funnel OP Master.xls" into the range "A36:E36" through to "A10000:E10000". I am really not sure how to do this looking at your formulas in Visual basic.. I would very much appreciate your help on this subject Regards Mark "Ron de Bruin" wrote: Hi Mark See http://msdn.microsoft.com/en-us/library/cc837974.aspx Or check out my website -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... I need to gather information from multiple files that all have the same layout but different information. Is it possible to update this information into one master excel file from the other files??? Regards Mark Allen |
Gather data from multiple excel files into one master excel fi
I used your tool that you supplied....RDBMERGE...
Regards "Ron de Bruin" wrote: Which code example have you try ? I will not change the add-in for this but can show you how to change the code -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... RDBMERGE works great...does what I need... Now how do I get it to populated an exsiting workbook... Star man... Regards Mark "Ron de Bruin" wrote: Hi Mark My code and the add-in create a new workbook with the data You can change the code to copy it in a existing workbook but first try the code and see if it is working. If it is working post back and post your code so I can change it -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... Ron, I have now completely lost myself... I have managed to fetch some data back but may be I have not explained correctly what I am looking to do: All the following files are in a folder "c:\OP Funnel" I have one master file called "Funnel OP Master.xls" I have six additional files called "Funnel OP 1.xls, 2.xls, 3.xls, 4.xls, 5.xls, 6.xls" I need to merge the data from "A36:E36" through to "A300:E300" if there is data there from the above six files I need to merge all this data in the file "Funnel OP Master.xls" into the range "A36:E36" through to "A10000:E10000". I am really not sure how to do this looking at your formulas in Visual basic.. I would very much appreciate your help on this subject Regards Mark "Ron de Bruin" wrote: Hi Mark See http://msdn.microsoft.com/en-us/library/cc837974.aspx Or check out my website -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... I need to gather information from multiple files that all have the same layout but different information. Is it possible to update this information into one master excel file from the other files??? Regards Mark Allen |
Gather data from multiple excel files into one master excel fi
I will add this option in the add-in in the next version but until then you must use code
Use a macro from this page http://www.rondebruin.nl/copy3.htm Copy the code in "Funnel OP Master.xls" And have the sheet active where you want the data For example in the first macro change 'Add a new workbook with one sheet Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1) To 'Point to the activesheet Set BaseWks = ActiveSheet -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... I used your tool that you supplied....RDBMERGE... Regards "Ron de Bruin" wrote: Which code example have you try ? I will not change the add-in for this but can show you how to change the code -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... RDBMERGE works great...does what I need... Now how do I get it to populated an exsiting workbook... Star man... Regards Mark "Ron de Bruin" wrote: Hi Mark My code and the add-in create a new workbook with the data You can change the code to copy it in a existing workbook but first try the code and see if it is working. If it is working post back and post your code so I can change it -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... Ron, I have now completely lost myself... I have managed to fetch some data back but may be I have not explained correctly what I am looking to do: All the following files are in a folder "c:\OP Funnel" I have one master file called "Funnel OP Master.xls" I have six additional files called "Funnel OP 1.xls, 2.xls, 3.xls, 4.xls, 5.xls, 6.xls" I need to merge the data from "A36:E36" through to "A300:E300" if there is data there from the above six files I need to merge all this data in the file "Funnel OP Master.xls" into the range "A36:E36" through to "A10000:E10000". I am really not sure how to do this looking at your formulas in Visual basic.. I would very much appreciate your help on this subject Regards Mark "Ron de Bruin" wrote: Hi Mark See http://msdn.microsoft.com/en-us/library/cc837974.aspx Or check out my website -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... I need to gather information from multiple files that all have the same layout but different information. Is it possible to update this information into one master excel file from the other files??? Regards Mark Allen |
Gather data from multiple excel files into one master excel fi
I think I have the code that needs changing...
I downloaded the "For VBA code go to my FSO code page" from your website... I then ran macro's and choose the **** Browse macro... This runs but only gets one bit of data from each file... Mark "Ron de Bruin" wrote: Which code example have you try ? I will not change the add-in for this but can show you how to change the code -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... RDBMERGE works great...does what I need... Now how do I get it to populated an exsiting workbook... Star man... Regards Mark "Ron de Bruin" wrote: Hi Mark My code and the add-in create a new workbook with the data You can change the code to copy it in a existing workbook but first try the code and see if it is working. If it is working post back and post your code so I can change it -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... Ron, I have now completely lost myself... I have managed to fetch some data back but may be I have not explained correctly what I am looking to do: All the following files are in a folder "c:\OP Funnel" I have one master file called "Funnel OP Master.xls" I have six additional files called "Funnel OP 1.xls, 2.xls, 3.xls, 4.xls, 5.xls, 6.xls" I need to merge the data from "A36:E36" through to "A300:E300" if there is data there from the above six files I need to merge all this data in the file "Funnel OP Master.xls" into the range "A36:E36" through to "A10000:E10000". I am really not sure how to do this looking at your formulas in Visual basic.. I would very much appreciate your help on this subject Regards Mark "Ron de Bruin" wrote: Hi Mark See http://msdn.microsoft.com/en-us/library/cc837974.aspx Or check out my website -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... I need to gather information from multiple files that all have the same layout but different information. Is it possible to update this information into one master excel file from the other files??? Regards Mark Allen |
Gather data from multiple excel files into one master excel fi
Hi Ron,
I have got completley lost now... I have no idea what code I am meant to put where and do what with it... ahhhhhhhh this is so frustrating when I have no idea what to do...and things are so close with you RDBMERGE programme.... Help again please. Mark "Ron de Bruin" wrote: I will add this option in the add-in in the next version but until then you must use code Use a macro from this page http://www.rondebruin.nl/copy3.htm Copy the code in "Funnel OP Master.xls" And have the sheet active where you want the data For example in the first macro change 'Add a new workbook with one sheet Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1) To 'Point to the activesheet Set BaseWks = ActiveSheet -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... I used your tool that you supplied....RDBMERGE... Regards "Ron de Bruin" wrote: Which code example have you try ? I will not change the add-in for this but can show you how to change the code -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... RDBMERGE works great...does what I need... Now how do I get it to populated an exsiting workbook... Star man... Regards Mark "Ron de Bruin" wrote: Hi Mark My code and the add-in create a new workbook with the data You can change the code to copy it in a existing workbook but first try the code and see if it is working. If it is working post back and post your code so I can change it -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... Ron, I have now completely lost myself... I have managed to fetch some data back but may be I have not explained correctly what I am looking to do: All the following files are in a folder "c:\OP Funnel" I have one master file called "Funnel OP Master.xls" I have six additional files called "Funnel OP 1.xls, 2.xls, 3.xls, 4.xls, 5.xls, 6.xls" I need to merge the data from "A36:E36" through to "A300:E300" if there is data there from the above six files I need to merge all this data in the file "Funnel OP Master.xls" into the range "A36:E36" through to "A10000:E10000". I am really not sure how to do this looking at your formulas in Visual basic.. I would very much appreciate your help on this subject Regards Mark "Ron de Bruin" wrote: Hi Mark See http://msdn.microsoft.com/en-us/library/cc837974.aspx Or check out my website -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... I need to gather information from multiple files that all have the same layout but different information. Is it possible to update this information into one master excel file from the other files??? Regards Mark Allen |
Gather data from multiple excel files into one master excel fi
Simple Copy the sheet that RDBMerge create in your workbook
A few seconds work and no need for VBA code for you. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... Hi Ron, I have got completley lost now... I have no idea what code I am meant to put where and do what with it... ahhhhhhhh this is so frustrating when I have no idea what to do...and things are so close with you RDBMERGE programme.... Help again please. Mark "Ron de Bruin" wrote: I will add this option in the add-in in the next version but until then you must use code Use a macro from this page http://www.rondebruin.nl/copy3.htm Copy the code in "Funnel OP Master.xls" And have the sheet active where you want the data For example in the first macro change 'Add a new workbook with one sheet Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1) To 'Point to the activesheet Set BaseWks = ActiveSheet -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... I used your tool that you supplied....RDBMERGE... Regards "Ron de Bruin" wrote: Which code example have you try ? I will not change the add-in for this but can show you how to change the code -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... RDBMERGE works great...does what I need... Now how do I get it to populated an exsiting workbook... Star man... Regards Mark "Ron de Bruin" wrote: Hi Mark My code and the add-in create a new workbook with the data You can change the code to copy it in a existing workbook but first try the code and see if it is working. If it is working post back and post your code so I can change it -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... Ron, I have now completely lost myself... I have managed to fetch some data back but may be I have not explained correctly what I am looking to do: All the following files are in a folder "c:\OP Funnel" I have one master file called "Funnel OP Master.xls" I have six additional files called "Funnel OP 1.xls, 2.xls, 3.xls, 4.xls, 5.xls, 6.xls" I need to merge the data from "A36:E36" through to "A300:E300" if there is data there from the above six files I need to merge all this data in the file "Funnel OP Master.xls" into the range "A36:E36" through to "A10000:E10000". I am really not sure how to do this looking at your formulas in Visual basic.. I would very much appreciate your help on this subject Regards Mark "Ron de Bruin" wrote: Hi Mark See http://msdn.microsoft.com/en-us/library/cc837974.aspx Or check out my website -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... I need to gather information from multiple files that all have the same layout but different information. Is it possible to update this information into one master excel file from the other files??? Regards Mark Allen |
Gather data from multiple excel files into one master excel fi
So close but yet so far....
I understand that you must be getting very frustrated with me and you are being very helpful but I really need to get this to work.... This would cut don alot of my time to make this work... If you can help further...please ?? Regards Mark "Ron de Bruin" wrote: Simple Copy the sheet that RDBMerge create in your workbook A few seconds work and no need for VBA code for you. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... Hi Ron, I have got completley lost now... I have no idea what code I am meant to put where and do what with it... ahhhhhhhh this is so frustrating when I have no idea what to do...and things are so close with you RDBMERGE programme.... Help again please. Mark "Ron de Bruin" wrote: I will add this option in the add-in in the next version but until then you must use code Use a macro from this page http://www.rondebruin.nl/copy3.htm Copy the code in "Funnel OP Master.xls" And have the sheet active where you want the data For example in the first macro change 'Add a new workbook with one sheet Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1) To 'Point to the activesheet Set BaseWks = ActiveSheet -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... I used your tool that you supplied....RDBMERGE... Regards "Ron de Bruin" wrote: Which code example have you try ? I will not change the add-in for this but can show you how to change the code -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... RDBMERGE works great...does what I need... Now how do I get it to populated an exsiting workbook... Star man... Regards Mark "Ron de Bruin" wrote: Hi Mark My code and the add-in create a new workbook with the data You can change the code to copy it in a existing workbook but first try the code and see if it is working. If it is working post back and post your code so I can change it -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... Ron, I have now completely lost myself... I have managed to fetch some data back but may be I have not explained correctly what I am looking to do: All the following files are in a folder "c:\OP Funnel" I have one master file called "Funnel OP Master.xls" I have six additional files called "Funnel OP 1.xls, 2.xls, 3.xls, 4.xls, 5.xls, 6.xls" I need to merge the data from "A36:E36" through to "A300:E300" if there is data there from the above six files I need to merge all this data in the file "Funnel OP Master.xls" into the range "A36:E36" through to "A10000:E10000". I am really not sure how to do this looking at your formulas in Visual basic.. I would very much appreciate your help on this subject Regards Mark "Ron de Bruin" wrote: Hi Mark See http://msdn.microsoft.com/en-us/library/cc837974.aspx Or check out my website -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... I need to gather information from multiple files that all have the same layout but different information. Is it possible to update this information into one master excel file from the other files??? Regards Mark Allen |
Gather data from multiple excel files into one master excel fi
Hi Mark
Copy the first macro from this page http://www.rondebruin.nl/copy3.htm Copy the code in "Funnel OP Master.xls" And have the sheet active where you want the data Replace this 'Add a new workbook with one sheet Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1) To 'Point to the activesheet Set BaseWks = ActiveSheet -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... So close but yet so far.... I understand that you must be getting very frustrated with me and you are being very helpful but I really need to get this to work.... This would cut don alot of my time to make this work... If you can help further...please ?? Regards Mark "Ron de Bruin" wrote: Simple Copy the sheet that RDBMerge create in your workbook A few seconds work and no need for VBA code for you. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... Hi Ron, I have got completley lost now... I have no idea what code I am meant to put where and do what with it... ahhhhhhhh this is so frustrating when I have no idea what to do...and things are so close with you RDBMERGE programme.... Help again please. Mark "Ron de Bruin" wrote: I will add this option in the add-in in the next version but until then you must use code Use a macro from this page http://www.rondebruin.nl/copy3.htm Copy the code in "Funnel OP Master.xls" And have the sheet active where you want the data For example in the first macro change 'Add a new workbook with one sheet Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1) To 'Point to the activesheet Set BaseWks = ActiveSheet -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... I used your tool that you supplied....RDBMERGE... Regards "Ron de Bruin" wrote: Which code example have you try ? I will not change the add-in for this but can show you how to change the code -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... RDBMERGE works great...does what I need... Now how do I get it to populated an exsiting workbook... Star man... Regards Mark "Ron de Bruin" wrote: Hi Mark My code and the add-in create a new workbook with the data You can change the code to copy it in a existing workbook but first try the code and see if it is working. If it is working post back and post your code so I can change it -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... Ron, I have now completely lost myself... I have managed to fetch some data back but may be I have not explained correctly what I am looking to do: All the following files are in a folder "c:\OP Funnel" I have one master file called "Funnel OP Master.xls" I have six additional files called "Funnel OP 1.xls, 2.xls, 3.xls, 4.xls, 5.xls, 6.xls" I need to merge the data from "A36:E36" through to "A300:E300" if there is data there from the above six files I need to merge all this data in the file "Funnel OP Master.xls" into the range "A36:E36" through to "A10000:E10000". I am really not sure how to do this looking at your formulas in Visual basic.. I would very much appreciate your help on this subject Regards Mark "Ron de Bruin" wrote: Hi Mark See http://msdn.microsoft.com/en-us/library/cc837974.aspx Or check out my website -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... I need to gather information from multiple files that all have the same layout but different information. Is it possible to update this information into one master excel file from the other files??? Regards Mark Allen |
Gather data from multiple excel files into one master excel fi
Ok done that...as below... But then get error !!!
I have marked it with *******ERROR HERE******** Sub Basic_Example_1() Dim MyPath As String, FilesInPath As String Dim MyFiles() As String Dim SourceRcount As Long, Fnum As Long Dim mybook As Workbook, BaseWks As Worksheet Dim sourceRange As Range, destrange As Range Dim rnum As Long, CalcMode As Long 'Fill in the path\folder where the files are MyPath = "C:\OP Funnel" 'Add a slash at the end if the user forget it If Right(MyPath, 1) < "\" Then MyPath = MyPath & "\" End If 'If there are no Excel files in the folder exit the sub FilesInPath = Dir(MyPath & "*.xl*") If FilesInPath = "" Then MsgBox "No files found" Exit Sub End If 'Fill the array(myFiles)with the list of Excel files in the folder Fnum = 0 Do While FilesInPath < "" Fnum = Fnum + 1 ReDim Preserve MyFiles(1 To Fnum) MyFiles(Fnum) = FilesInPath FilesInPath = Dir() Loop 'Change ScreenUpdating, Calculation and EnableEvents With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False .EnableEvents = False End With 'Point to the activesheet Set BaseWks = ActiveSheet 'Loop through all files in the array(myFiles) If Fnum 0 Then For Fnum = LBound(MyFiles) To UBound(MyFiles) Set mybook = Nothing On Error Resume Next Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum)) On Error GoTo 0 If Not mybook Is Nothing Then On Error Resume Next With mybook.Worksheets(1) Set sourceRange = .Range("A36:E36") End With If Err.Number 0 Then Err.Clear Set sourceRange = Nothing Else 'if SourceRange use all columns then skip this file If sourceRange.Columns.Count = BaseWks.Columns.Count Then Set sourceRange = Nothing End If End If On Error GoTo 0 If Not sourceRange Is Nothing Then SourceRcount = sourceRange.Rows.Count If rnum + SourceRcount = BaseWks.Rows.Count Then MsgBox "Sorry there are not enough rows in the sheet" BaseWks.Columns.AutoFit mybook.Close savechanges:=False GoTo ExitTheSub Else 'Copy the file name in column A With sourceRange ********* ERROR HERE*****BaseWks.Cells(rnum, "A"). _ Resize(.Rows.Count).Value = MyFiles(Fnum) End With 'Set the destrange Set destrange = BaseWks.Range("B" & rnum) 'we copy the values from the sourceRange to the destrange With sourceRange Set destrange = destrange. _ Resize(.Rows.Count, ..Columns.Count) End With destrange.Value = sourceRange.Value rnum = rnum + SourceRcount End If End If mybook.Close savechanges:=False End If Next Fnum BaseWks.Columns.AutoFit End If ExitTheSub: 'Restore ScreenUpdating, Calculation and EnableEvents With Application .ScreenUpdating = True .EnableEvents = True .Calculation = CalcMode End With End Sub "Ron de Bruin" wrote: Hi Mark Copy the first macro from this page http://www.rondebruin.nl/copy3.htm Copy the code in "Funnel OP Master.xls" And have the sheet active where you want the data Replace this 'Add a new workbook with one sheet Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1) To 'Point to the activesheet Set BaseWks = ActiveSheet -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... So close but yet so far.... I understand that you must be getting very frustrated with me and you are being very helpful but I really need to get this to work.... This would cut don alot of my time to make this work... If you can help further...please ?? Regards Mark "Ron de Bruin" wrote: Simple Copy the sheet that RDBMerge create in your workbook A few seconds work and no need for VBA code for you. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... Hi Ron, I have got completley lost now... I have no idea what code I am meant to put where and do what with it... ahhhhhhhh this is so frustrating when I have no idea what to do...and things are so close with you RDBMERGE programme.... Help again please. Mark "Ron de Bruin" wrote: I will add this option in the add-in in the next version but until then you must use code Use a macro from this page http://www.rondebruin.nl/copy3.htm Copy the code in "Funnel OP Master.xls" And have the sheet active where you want the data For example in the first macro change 'Add a new workbook with one sheet Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1) To 'Point to the activesheet Set BaseWks = ActiveSheet -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... I used your tool that you supplied....RDBMERGE... Regards "Ron de Bruin" wrote: Which code example have you try ? I will not change the add-in for this but can show you how to change the code -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... RDBMERGE works great...does what I need... Now how do I get it to populated an exsiting workbook... Star man... Regards Mark "Ron de Bruin" wrote: Hi Mark My code and the add-in create a new workbook with the data You can change the code to copy it in a existing workbook but first try the code and see if it is working. If it is working post back and post your code so I can change it -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... Ron, I have now completely lost myself... I have managed to fetch some data back but may be I have not explained correctly what I am looking to do: All the following files are in a folder "c:\OP Funnel" I have one master file called "Funnel OP Master.xls" I have six additional files called "Funnel OP 1.xls, 2.xls, 3.xls, 4.xls, 5.xls, 6.xls" I need to merge the data from "A36:E36" through to "A300:E300" if there is data there from the above six files I need to merge all this data in the file "Funnel OP Master.xls" into the range "A36:E36" through to "A10000:E10000". I am really not sure how to do this looking at your formulas in Visual basic.. I would very much appreciate your help on this subject Regards Mark "Ron de Bruin" wrote: Hi Mark See http://msdn.microsoft.com/en-us/library/cc837974.aspx Or check out my website -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... I need to gather information from multiple files that all have the same layout but different information. Is it possible to update this information into one master excel file from the other files??? Regards Mark Allen |
Gather data from multiple excel files into one master excel fi
Hi Mark
Where have you copy he code ? is the sheet where you want to copy to protected ? Be sure that this file outside the folder with the other files -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... Ok done that...as below... But then get error !!! I have marked it with *******ERROR HERE******** Sub Basic_Example_1() Dim MyPath As String, FilesInPath As String Dim MyFiles() As String Dim SourceRcount As Long, Fnum As Long Dim mybook As Workbook, BaseWks As Worksheet Dim sourceRange As Range, destrange As Range Dim rnum As Long, CalcMode As Long 'Fill in the path\folder where the files are MyPath = "C:\OP Funnel" 'Add a slash at the end if the user forget it If Right(MyPath, 1) < "\" Then MyPath = MyPath & "\" End If 'If there are no Excel files in the folder exit the sub FilesInPath = Dir(MyPath & "*.xl*") If FilesInPath = "" Then MsgBox "No files found" Exit Sub End If 'Fill the array(myFiles)with the list of Excel files in the folder Fnum = 0 Do While FilesInPath < "" Fnum = Fnum + 1 ReDim Preserve MyFiles(1 To Fnum) MyFiles(Fnum) = FilesInPath FilesInPath = Dir() Loop 'Change ScreenUpdating, Calculation and EnableEvents With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False .EnableEvents = False End With 'Point to the activesheet Set BaseWks = ActiveSheet 'Loop through all files in the array(myFiles) If Fnum 0 Then For Fnum = LBound(MyFiles) To UBound(MyFiles) Set mybook = Nothing On Error Resume Next Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum)) On Error GoTo 0 If Not mybook Is Nothing Then On Error Resume Next With mybook.Worksheets(1) Set sourceRange = .Range("A36:E36") End With If Err.Number 0 Then Err.Clear Set sourceRange = Nothing Else 'if SourceRange use all columns then skip this file If sourceRange.Columns.Count = BaseWks.Columns.Count Then Set sourceRange = Nothing End If End If On Error GoTo 0 If Not sourceRange Is Nothing Then SourceRcount = sourceRange.Rows.Count If rnum + SourceRcount = BaseWks.Rows.Count Then MsgBox "Sorry there are not enough rows in the sheet" BaseWks.Columns.AutoFit mybook.Close savechanges:=False GoTo ExitTheSub Else 'Copy the file name in column A With sourceRange ********* ERROR HERE*****BaseWks.Cells(rnum, "A"). _ Resize(.Rows.Count).Value = MyFiles(Fnum) End With 'Set the destrange Set destrange = BaseWks.Range("B" & rnum) 'we copy the values from the sourceRange to the destrange With sourceRange Set destrange = destrange. _ Resize(.Rows.Count, .Columns.Count) End With destrange.Value = sourceRange.Value rnum = rnum + SourceRcount End If End If mybook.Close savechanges:=False End If Next Fnum BaseWks.Columns.AutoFit End If ExitTheSub: 'Restore ScreenUpdating, Calculation and EnableEvents With Application .ScreenUpdating = True .EnableEvents = True .Calculation = CalcMode End With End Sub "Ron de Bruin" wrote: Hi Mark Copy the first macro from this page http://www.rondebruin.nl/copy3.htm Copy the code in "Funnel OP Master.xls" And have the sheet active where you want the data Replace this 'Add a new workbook with one sheet Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1) To 'Point to the activesheet Set BaseWks = ActiveSheet -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... So close but yet so far.... I understand that you must be getting very frustrated with me and you are being very helpful but I really need to get this to work.... This would cut don alot of my time to make this work... If you can help further...please ?? Regards Mark "Ron de Bruin" wrote: Simple Copy the sheet that RDBMerge create in your workbook A few seconds work and no need for VBA code for you. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... Hi Ron, I have got completley lost now... I have no idea what code I am meant to put where and do what with it... ahhhhhhhh this is so frustrating when I have no idea what to do...and things are so close with you RDBMERGE programme.... Help again please. Mark "Ron de Bruin" wrote: I will add this option in the add-in in the next version but until then you must use code Use a macro from this page http://www.rondebruin.nl/copy3.htm Copy the code in "Funnel OP Master.xls" And have the sheet active where you want the data For example in the first macro change 'Add a new workbook with one sheet Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1) To 'Point to the activesheet Set BaseWks = ActiveSheet -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... I used your tool that you supplied....RDBMERGE... Regards "Ron de Bruin" wrote: Which code example have you try ? I will not change the add-in for this but can show you how to change the code -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... RDBMERGE works great...does what I need... Now how do I get it to populated an exsiting workbook... Star man... Regards Mark "Ron de Bruin" wrote: Hi Mark My code and the add-in create a new workbook with the data You can change the code to copy it in a existing workbook but first try the code and see if it is working. If it is working post back and post your code so I can change it -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... Ron, I have now completely lost myself... I have managed to fetch some data back but may be I have not explained correctly what I am looking to do: All the following files are in a folder "c:\OP Funnel" I have one master file called "Funnel OP Master.xls" I have six additional files called "Funnel OP 1.xls, 2.xls, 3.xls, 4.xls, 5.xls, 6.xls" I need to merge the data from "A36:E36" through to "A300:E300" if there is data there from the above six files I need to merge all this data in the file "Funnel OP Master.xls" into the range "A36:E36" through to "A10000:E10000". I am really not sure how to do this looking at your formulas in Visual basic.. I would very much appreciate your help on this subject Regards Mark "Ron de Bruin" wrote: Hi Mark See http://msdn.microsoft.com/en-us/library/cc837974.aspx Or check out my website -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... I need to gather information from multiple files that all have the same layout but different information. Is it possible to update this information into one master excel file from the other files??? Regards Mark Allen |
Gather data from multiple excel files into one master excel fi
Ron,
thanks for all your help, but I think I will give this up as I dont seem to be getting any where with it... Mark "Ron de Bruin" wrote: Hi Mark Where have you copy he code ? is the sheet where you want to copy to protected ? Be sure that this file outside the folder with the other files -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... Ok done that...as below... But then get error !!! I have marked it with *******ERROR HERE******** Sub Basic_Example_1() Dim MyPath As String, FilesInPath As String Dim MyFiles() As String Dim SourceRcount As Long, Fnum As Long Dim mybook As Workbook, BaseWks As Worksheet Dim sourceRange As Range, destrange As Range Dim rnum As Long, CalcMode As Long 'Fill in the path\folder where the files are MyPath = "C:\OP Funnel" 'Add a slash at the end if the user forget it If Right(MyPath, 1) < "\" Then MyPath = MyPath & "\" End If 'If there are no Excel files in the folder exit the sub FilesInPath = Dir(MyPath & "*.xl*") If FilesInPath = "" Then MsgBox "No files found" Exit Sub End If 'Fill the array(myFiles)with the list of Excel files in the folder Fnum = 0 Do While FilesInPath < "" Fnum = Fnum + 1 ReDim Preserve MyFiles(1 To Fnum) MyFiles(Fnum) = FilesInPath FilesInPath = Dir() Loop 'Change ScreenUpdating, Calculation and EnableEvents With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False .EnableEvents = False End With 'Point to the activesheet Set BaseWks = ActiveSheet 'Loop through all files in the array(myFiles) If Fnum 0 Then For Fnum = LBound(MyFiles) To UBound(MyFiles) Set mybook = Nothing On Error Resume Next Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum)) On Error GoTo 0 If Not mybook Is Nothing Then On Error Resume Next With mybook.Worksheets(1) Set sourceRange = .Range("A36:E36") End With If Err.Number 0 Then Err.Clear Set sourceRange = Nothing Else 'if SourceRange use all columns then skip this file If sourceRange.Columns.Count = BaseWks.Columns.Count Then Set sourceRange = Nothing End If End If On Error GoTo 0 If Not sourceRange Is Nothing Then SourceRcount = sourceRange.Rows.Count If rnum + SourceRcount = BaseWks.Rows.Count Then MsgBox "Sorry there are not enough rows in the sheet" BaseWks.Columns.AutoFit mybook.Close savechanges:=False GoTo ExitTheSub Else 'Copy the file name in column A With sourceRange ********* ERROR HERE*****BaseWks.Cells(rnum, "A"). _ Resize(.Rows.Count).Value = MyFiles(Fnum) End With 'Set the destrange Set destrange = BaseWks.Range("B" & rnum) 'we copy the values from the sourceRange to the destrange With sourceRange Set destrange = destrange. _ Resize(.Rows.Count, .Columns.Count) End With destrange.Value = sourceRange.Value rnum = rnum + SourceRcount End If End If mybook.Close savechanges:=False End If Next Fnum BaseWks.Columns.AutoFit End If ExitTheSub: 'Restore ScreenUpdating, Calculation and EnableEvents With Application .ScreenUpdating = True .EnableEvents = True .Calculation = CalcMode End With End Sub "Ron de Bruin" wrote: Hi Mark Copy the first macro from this page http://www.rondebruin.nl/copy3.htm Copy the code in "Funnel OP Master.xls" And have the sheet active where you want the data Replace this 'Add a new workbook with one sheet Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1) To 'Point to the activesheet Set BaseWks = ActiveSheet -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... So close but yet so far.... I understand that you must be getting very frustrated with me and you are being very helpful but I really need to get this to work.... This would cut don alot of my time to make this work... If you can help further...please ?? Regards Mark "Ron de Bruin" wrote: Simple Copy the sheet that RDBMerge create in your workbook A few seconds work and no need for VBA code for you. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... Hi Ron, I have got completley lost now... I have no idea what code I am meant to put where and do what with it... ahhhhhhhh this is so frustrating when I have no idea what to do...and things are so close with you RDBMERGE programme.... Help again please. Mark "Ron de Bruin" wrote: I will add this option in the add-in in the next version but until then you must use code Use a macro from this page http://www.rondebruin.nl/copy3.htm Copy the code in "Funnel OP Master.xls" And have the sheet active where you want the data For example in the first macro change 'Add a new workbook with one sheet Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1) To 'Point to the activesheet Set BaseWks = ActiveSheet -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... I used your tool that you supplied....RDBMERGE... Regards "Ron de Bruin" wrote: Which code example have you try ? I will not change the add-in for this but can show you how to change the code -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... RDBMERGE works great...does what I need... Now how do I get it to populated an exsiting workbook... Star man... Regards Mark "Ron de Bruin" wrote: Hi Mark My code and the add-in create a new workbook with the data You can change the code to copy it in a existing workbook but first try the code and see if it is working. If it is working post back and post your code so I can change it -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mark Allen" wrote in message ... Ron, I have now completely lost myself... I have managed to fetch some data back but may be I have not explained correctly what I am looking to do: All the following files are in a folder "c:\OP Funnel" I have one master file called "Funnel OP Master.xls" I have six additional files called "Funnel OP 1.xls, 2.xls, 3.xls, 4.xls, 5.xls, 6.xls" |
| All times are GMT +1. The time now is 03:44 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com