Go | New | Find | Notify | Tools | Reply | |
| Eating elephants one bite at a time  |
It might be worth reading from the bottom to the top in this thread as the solution was found... In the past, I scrounged a macro from the internet that took multiple workbooks (single sheet) and combined them into one workbook. The process was basically to place all workbooks into a folder and then run the macro. The end result was a new workbook with all the data in one place. I am trying to do this again with the same macro, but something is broken. It is only grabbing two workbooks(first two in folder) and combining them. It also errors out at the end. As I am not a macro guru, is there any chance anyone would like to take a crack at it? Here is the macro: Sub simpleXlsMerger() Dim bookList As Workbook Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object Application.ScreenUpdating = False Set mergeObj = CreateObject("Scripting.FileSystemObject") 'change folder path of excel files here Set dirObj = mergeObj.Getfolder("C:\path") Set filesObj = dirObj.Files For Each everyObj In filesObj Set bookList = Workbooks.Open(everyObj) 'change "A2" with cell reference of start point for every files here 'for example "B3:IV" to merge all files start from columns B and rows 3 'If you're files using more than IV column, change it to the latest column 'Also change "A" column on "A65536" to the same column as start point Range("A3:IV" & Range("A65536").End(xlUp).Row).Copy ThisWorkbook.Worksheets(1).Activate 'Do not change the following column. It's not the same column as above Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Application.CutCopyMode = False bookList.Close Next End Sub Thanks in advance.  This message has been edited. Last edited by: ffips, | ||
|
| Eating elephants one bite at a time |
I sort of found the issue. It is a data standardization issue on my side. I found some sheets with a gap of about 400 rows that were empty. I am sorting through that. At this time, it has pulled data into one location, but is throwing an error at the end: Run-Time error '1004': Application-defined or object-defined error I have no clue what that means. I also thought it used to work by opening a blank spreadsheet and running the macro. At this time, I am opening the macro and it is saving the spreadsheet as the macro at the end of the routine? | |||
|
His Royal Hiney |
I haven't used "Scripting.FileSystemObject" before so I can't help at his time. I also don't understand how you "open a blank spreadsheet and run the macro" as you have to open the spreadsheet with the macro and then run the macro. I also don't understand "saving the spreadsheet as the macro at the end of the routine." Are you saying it tries to save the created file as simpleXIsMerger.xls? One thing I see is the reference "A65536" cell. this is the old limitation of 65536. If the files you are trying to copy have more than 65536 rows of data, this would cause an issue and it's saying start from row 65536 and find the first row above it with data which would be row 1. I'd offer to help more but I'm in the middle of working on my retirement macro and it's been slow going and I think your need is time sensitive. I know you described your problem in your OP. I'd like to ask a question a different way and maybe I can help you. And I'm asking you the question because I have a library of macros that I created and maybe I can put a couple together for you. So here is my question: "What is it that you want to accomplish? What does it look like when you have succeeded in what you are trying to accomplish?" You start with several Excel files with single sheets and you want those sheets copied into a single workbook? Is that all that you want to accomplish? The closest experience I have is I have the user open several excel files and then the macro searches for specific file names and copy data from them into a workbook. I'll wait for your answer and in the meantime, i'll look up how to copy whole sheets. Are you overwriting old data? Are you appending data to existing data? You might want to try those excel vba forums. I've done that in the past. Sorry, i'm not much help at this time. ETA: one other thing is you could open the macro and step through each line an d see where you get the error. You do this by F8 in the macro. where it errors may give you a clue as to what is causing it to break. "It did not really matter what we expected from life, but rather what life expected from us. We needed to stop asking about the meaning of life, and instead to think of ourselves as those who were being questioned by life – daily and hourly. Our answer must consist not in talk and meditation, but in right action and in right conduct. Life ultimately means taking the responsibility to find the right answer to its problems and to fulfill the tasks which it constantly sets for each individual." Viktor Frankl, Man's Search for Meaning, 1946. | |||
|
| Eating elephants one bite at a time |
I will try to put my answers in your quote below in blue. Thanks for the help.
Thank you. | |||
|
| Eating elephants one bite at a time |
It appears I found this when I was using an older version of Excel on this website. I am going to root around there for a bit and see if it gets me on track. Here is a video of how it used to work: Link to original video: https://www.youtube.com/watch?v=MsbdMiruX68 I need to confirm if something has changed in Excel or if I have a data issue that is causing some "extra" pop ups and errors when I run this macro. I'll report back my findings. | |||
|
| Eating elephants one bite at a time |
Okay, memory was jogged enough by site. I also realized that some formulas in the spreadsheets were part of what was causing the "random pop-ups" when running the macro. My solution was to copy the values in the existing sheet to a new sheet and delete the original sheet from within each Excel file. Then, I opened the macro and ran it. SUCCESS (except for that pesky final error mentioned previously). Even with that error, the data is getting combined as desired and I am not missing any records. So, error be damned, I am ignoring that. Rey HRH, I don't know if you want to add this one to you library of macros, but it has helped me many times in the past, and now it will likely be a help again currently. I didn't write it, I simply found it at the above mentioned website. I am good to call this one a wrap. THANKS I guess I was "thinking out loud." | |||
|
| His Royal Hiney |
Good that you figured it out. I literally talk myself through macro development out loud. I googled "scripting.filesystemobject" and captured a page on objects. I'm sure it'll be useful someday. I just usually manually open all the files I want to work with. it's easy for me to do when they're in a directory or even a zip file. Just cntrl+a to select all then right click open. "It did not really matter what we expected from life, but rather what life expected from us. We needed to stop asking about the meaning of life, and instead to think of ourselves as those who were being questioned by life – daily and hourly. Our answer must consist not in talk and meditation, but in right action and in right conduct. Life ultimately means taking the responsibility to find the right answer to its problems and to fulfill the tasks which it constantly sets for each individual." Viktor Frankl, Man's Search for Meaning, 1946. | |||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
|
© SIGforum 2024