I am completely new to Excel macros, so bare with me as I work through this. After watching the video, I need a few things specified, as well as clear up some confusing stuff that's a little confusing for new people like me: 0:07 This is representative of the folder location on your computer. It doesn't have to be a temp folder. I'm not sure if the Master Excel Document ("Master Workbook") needs to be in the same folder as the Various Excel Docs that contain the data ("Current Workbook")... 0:18 "Master Workbook" is the Workbook all of the data is being copied then pasted in to. So, like, if you have a device that records data over time, but it saves a new workbook every hour, then you'll probably want a Master Workbook that compiles all of the data from the various workbook files in to one. Then you can use Excel to show a huge plot of this data (guess what I'm doing). For the sake of consistency, I will call each little excel file with data that's being copied the "Current Workbook". 0:33 Note how in this "Current Workbook" the data starts on row #2. This is important, because later on we're going to reference where in the workbook we're starting, and how far down it'll go until the bottom row of data. 0:46 Hand copy all of this code in a new Excel document. Go to File : Options : Customize Ribbon : Developer. This will allow you to get access to the little Excel Macro program he has here (especially for those of us who can't press f8 because the laptop is not set up to use it for some reason). 2:27 Notice he restricted the code so it only reads ".xlsx" files. If it helps, I believe the default file extension for Excel documents are xlsx files. This is important, because if you have other files in there (like pdf, or whatever), it will skip this file type automatically. I'm sure there's a way to open all applicable files, but I'm not sure. 3:07 Remember this part of the code is looking through the "Current Workbook" for this info, NOT the Master Workbook. 3:50 Note he mentions "for each sheet." This means if you have an Excel Worksheet file with, like, 3 sheets, it'll copy the data from each sheet in that workbook. Ex: Current Workbook has 3 sheets. Sheet 1, Sheet 2, Sheet 3 in that order. It will copy Sheet 1's contents, then go to Sheet 2 and copy that contents, then go to Sheet 3 and copy that contents, and only THEN close that Workbook to move on to the next one. To prevent potential issues, I've set up our data logger so it spits out one Workbook with one sheet. 4:11 I think I understand what this means. It's goal is to go from one designated row to finding the last row with data. rows.Count,1 where the 1 represents "Start from row 1, then work your way down." This is, I assume, a way you could eliminate header info a device auto puts in a program. So if the data starts at row 12, then it would be rows.Count,12 instead. 4:38 This is where it gets a little confusing. This x = 2 I THINK represents in the CURRENT Workbook , it will begin taking data from row 2. From there, x will remain the same once it goes to another Current Workbook. Just pointing this out, because it's extremely confusing and easy to mix up Master Workbook and Current Workbook. 5:01 I THINK this is the starting row where data is being prepared to be stored in the MASTER Workbook. So it takes data from Current Workbook from row 2 to end, and puts it at y = 1 at first. However, as another Current Workbook data is compiled, it puts it at the end of the Master Workbook below all the other data. If this is NOT what it is, then with all due respect, the OP should have taken a little bit more time explaining this. Next time, use more unique names for Excel Workbooks. Also note he put a +1 on the end. That's because the above part only knows how to identify the last placement where data actually exists. By putting the +1, we're shifting it down to the now empty row right below it. 6:04 His use of "Master Workbook" and "Current Workbook" is confusing. So what I THINK is happening is in the last step it prepared the data, but in THIS step it's actually putting the data in Master Workbook. That's the only thing that makes sense in this context. The Master Workbook has a name (he named this "sheet1" for some reason), and the rest is just the program jumping around grabbing data, then storing it in the Master Workbook. 6:53 What the heck? Why are you copy/pasting that line? Okay, I THINK I understand. This is so it can copy the data from any given row, but from a different column at the same time. Specifically, at row 11 (for example), it'll copy column 1, column 2, column 3, column 4, paste them in the Master Workbook at Column 1, column 2, column 3, and column 4, then move on to the next row and do it again. Pretty much, if you have data in, like, 8 columns, you'll need to copy/paste this command 8 total times for each row. Okay, got ya. 10:11 He took the exact same code inside the "loop" code, and put it in there. I'm not sure why. 10:41 I'm not entirely sure how the program knows to stop reading files once the folder is done, but I assume it somehow knows if it ever sees duplicate entries, it'll assume there's no new files to read, and just stop. That, or it's some other code at work not explored by us. Either way it apparently ends on its own. So I guess what's important when dealing with this particular code is to have ALL of the files located inside the SAME folder, because I don't see ANY indication of names its going to. I'm guessing since you've created this entire macro INSIDE the Master Workbook, it assumes to put the data from the C:\temp\ Current Workbooks that are there in to it. This therefore implies it might be easier to do the following if you're doing repetitive data collecting from a device: 1. Write out a "standard" Master Workbook with no data in it, but it does have the Macro he writes out in it. 2. Save a copy of that Master Workbook in a new folder when a new set of Worksheets with tons of data come in. 3. Change the exact address location of the new data. 4. Run it like this. I want to thank you, OP, for doing this step by step. Although I rag on you on some parts about not being specific enough, I have to acknowledge that you do a pretty good job spelling out/justifying most of the program, so someone like myself (who has ZERO experience with Excel Macros) can understand how to do it. Let's hope this translates well in to the actual procedure as I try it out on actual data. Wish me luck.
Nice tip Daniel. I could have used this a couple of months ago but I found a clumsy work around. I am sure a new use will come up again. Thanks for sharing.
Awesome! I'm working on a project that needs this exact procedure where I need to loop through the folder, then the workbook, worksheets, and rows to extract several items. I was having difficulty looping through the folder so this helps. Thanks!
Thanks for this great video! I am working on a MacBook and the line : Set fso = CreateObject("Scripting.FileSystemObject") is creating an error. After some research I found that this is not an applicable way for Mac users. Do you know a way it will also work for Mac users? Many thanks again for this great video. Cheers
@10:08 I don't think you needed to move the 'y=thisworkbook.cell.row.count + 1' code. Establishing it at the top is fine because the loop has y=y+1 after each row of data goes in. So when the next workbooks opens, the master workbook is already 1 row below the last row. Am I wrong though?
Hi! First of all I would like to thank you and your channel. I learned VBA thanks to your channel! Great work, keep it up! Now I have a question: is it possible to display the email with attachment when you are sending email from excel through GMAIL via VBA? I know that it is possible with outlook. You just press a shortcut and outlook email opens with attachment attached already. And what i need to do is just write text and choose receiver of the email. I found certain macros (with or without CDO.message) that allow me to send email with GMAIL instead of outlook. However, it is possible only to send and only if receiving email and text is included in macro. What I need is: new email should be displayed with current file already attached. Because every time excel will be sent to different people with different text. So setting it by default in macro does not work for me. Any ideas?
Hello! Does this article help? stackoverflow.com/questions/31712849/vba-code-to-send-email-through-gmail-with-pdf-attachment I'm thinking if you want a custom message each time, just update the objEmail.TextBody property with whatever message you need in the Body of the email. This could be done dynamically or via a textbox on your userform, what have you. Hope that helps. Thanks Dan
Mate love your work. I have a question and its regarding cab for word. I have a folder of RTF files. I need to convert the RTF files into txt files so I can open them in excel and macro them to another excel spreadsheet. Can you please help. Working on it for work and I cannot download any programs or anything. Thanks in advance
I’m not sure why we have to put y=y+1 Didn’t we already set y = last row + 1? So shouldn’t it always go to last row and add 1? Regardless as to how much new data we’ve entered?
Hi canefan17, Great question! Since y represents the row being written to, once we use up that row, we need to increment the row number so the next item will use the new row. The first time through, excel had to figure out the last row for y, but thereafter it was just simpler to increase the number by one, rather than having excel look it up the more resource intensive way. Thanks Dan
Dear sir can you please place a huge data with inventory with opening, purchases and sales and closing for each date and then next date opening items and current purchases with sales then closing stock for date to date and then months, years etc., can you please help us
I need your help in re-correcting the macro. My file in the folder have data for sheet 2 & 3. So what happening means maco is picking all the data from all the sheets for specified columns. I need only first sheet to be consolidated across all the files in the folder.
Hi, in the sheet if data to copy starts from row 13 in each sheets and cel (2,1) has date than we need to copy date to new column and when we paste the data in master sheet the date value for each sheets will be there. can we do that....
This is great! However I am having a bit of trouble editing it for my needs. I need to copy 1 row of data (always the same row) from a number of .xlsm files in a folder and then preferrably move those files to a new folder titled "archives". I am getting stuck at the wsLR = spot in your code. Is there a way i can contact you for help? Id be willing to pay for your services if needed
I'm on a site that has several tabs. i can click on it but each clickable tab has no id, name or class. It just has: Title Title ("Music Tab") I have tried IE.Document.getElementsByTitle("ElementName")(0).click IE.Document.getElementsByTitle("ElementName")(0).submit IE.Document.getElementsByTitle("ElementName")(0).select IE.Document.getElementsByTitle("ElementName").click IE.Document.getElementsByTitleName("ElementName")(0).click But none of those work
I'm running into this error message: file is already open, reopening will cause any changes to be discarded. it is because I have to have one workbook open to run the macro. How can i solve this? Thank you!!!
Hi Daniel It's me again :) Could you please do the same, but based on cell value ,especially if the cell value is Date and the sheet is almost a million rows. The first column contains symbols, and the second contains dates . Dates are the same for each symbol and I have to separate it by dates. Thank you in advance
Hi canefan17, You could change the line that checks this to include other checks using the OR command, such as: If fso.GetExtensionName(wbFile.Name) = "xls" OR fso.GetExtensionName(wbFile.Name) = "xlsm" OR fso.GetExtensionName(wbFile.Name) = "xlsb" OR fso.GetExtensionName(wbFile.Name) = "xlsx" then OR... you could just use the LEFT() function so it can use any extension that starts with xls, such as: If LEFT(fso.GetExtensionName(wbFile.Name), 3) = "xls" then 'if leftmost 3 characters are xls Hope that helps Thanks Dan
I am completely new to Excel macros, so bare with me as I work through this.
After watching the video, I need a few things specified, as well as clear up some confusing stuff that's a little confusing for new people like me:
0:07 This is representative of the folder location on your computer. It doesn't have to be a temp folder. I'm not sure if the Master Excel Document ("Master Workbook") needs to be in the same folder as the Various Excel Docs that contain the data ("Current Workbook")...
0:18 "Master Workbook" is the Workbook all of the data is being copied then pasted in to. So, like, if you have a device that records data over time, but it saves a new workbook every hour, then you'll probably want a Master Workbook that compiles all of the data from the various workbook files in to one. Then you can use Excel to show a huge plot of this data (guess what I'm doing).
For the sake of consistency, I will call each little excel file with data that's being copied the "Current Workbook".
0:33 Note how in this "Current Workbook" the data starts on row #2. This is important, because later on we're going to reference where in the workbook we're starting, and how far down it'll go until the bottom row of data.
0:46 Hand copy all of this code in a new Excel document. Go to File : Options : Customize Ribbon : Developer. This will allow you to get access to the little Excel Macro program he has here (especially for those of us who can't press f8 because the laptop is not set up to use it for some reason).
2:27 Notice he restricted the code so it only reads ".xlsx" files. If it helps, I believe the default file extension for Excel documents are xlsx files. This is important, because if you have other files in there (like pdf, or whatever), it will skip this file type automatically. I'm sure there's a way to open all applicable files, but I'm not sure.
3:07 Remember this part of the code is looking through the "Current Workbook" for this info, NOT the Master Workbook.
3:50 Note he mentions "for each sheet." This means if you have an Excel Worksheet file with, like, 3 sheets, it'll copy the data from each sheet in that workbook. Ex: Current Workbook has 3 sheets. Sheet 1, Sheet 2, Sheet 3 in that order. It will copy Sheet 1's contents, then go to Sheet 2 and copy that contents, then go to Sheet 3 and copy that contents, and only THEN close that Workbook to move on to the next one. To prevent potential issues, I've set up our data logger so it spits out one Workbook with one sheet.
4:11 I think I understand what this means. It's goal is to go from one designated row to finding the last row with data. rows.Count,1 where the 1 represents "Start from row 1, then work your way down." This is, I assume, a way you could eliminate header info a device auto puts in a program. So if the data starts at row 12, then it would be rows.Count,12 instead.
4:38 This is where it gets a little confusing. This x = 2 I THINK represents in the CURRENT Workbook , it will begin taking data from row 2. From there, x will remain the same once it goes to another Current Workbook. Just pointing this out, because it's extremely confusing and easy to mix up Master Workbook and Current Workbook.
5:01 I THINK this is the starting row where data is being prepared to be stored in the MASTER Workbook. So it takes data from Current Workbook from row 2 to end, and puts it at y = 1 at first. However, as another Current Workbook data is compiled, it puts it at the end of the Master Workbook below all the other data. If this is NOT what it is, then with all due respect, the OP should have taken a little bit more time explaining this. Next time, use more unique names for Excel Workbooks.
Also note he put a +1 on the end. That's because the above part only knows how to identify the last placement where data actually exists. By putting the +1, we're shifting it down to the now empty row right below it.
6:04 His use of "Master Workbook" and "Current Workbook" is confusing. So what I THINK is happening is in the last step it prepared the data, but in THIS step it's actually putting the data in Master Workbook. That's the only thing that makes sense in this context. The Master Workbook has a name (he named this "sheet1" for some reason), and the rest is just the program jumping around grabbing data, then storing it in the Master Workbook.
6:53 What the heck? Why are you copy/pasting that line? Okay, I THINK I understand. This is so it can copy the data from any given row, but from a different column at the same time. Specifically, at row 11 (for example), it'll copy column 1, column 2, column 3, column 4, paste them in the Master Workbook at Column 1, column 2, column 3, and column 4, then move on to the next row and do it again. Pretty much, if you have data in, like, 8 columns, you'll need to copy/paste this command 8 total times for each row. Okay, got ya.
10:11 He took the exact same code inside the "loop" code, and put it in there. I'm not sure why.
10:41 I'm not entirely sure how the program knows to stop reading files once the folder is done, but I assume it somehow knows if it ever sees duplicate entries, it'll assume there's no new files to read, and just stop. That, or it's some other code at work not explored by us. Either way it apparently ends on its own.
So I guess what's important when dealing with this particular code is to have ALL of the files located inside the SAME folder, because I don't see ANY indication of names its going to. I'm guessing since you've created this entire macro INSIDE the Master Workbook, it assumes to put the data from the C:\temp\ Current Workbooks that are there in to it.
This therefore implies it might be easier to do the following if you're doing repetitive data collecting from a device:
1. Write out a "standard" Master Workbook with no data in it, but it does have the Macro he writes out in it.
2. Save a copy of that Master Workbook in a new folder when a new set of Worksheets with tons of data come in.
3. Change the exact address location of the new data.
4. Run it like this.
I want to thank you, OP, for doing this step by step. Although I rag on you on some parts about not being specific enough, I have to acknowledge that you do a pretty good job spelling out/justifying most of the program, so someone like myself (who has ZERO experience with Excel Macros) can understand how to do it.
Let's hope this translates well in to the actual procedure as I try it out on actual data. Wish me luck.
Nice tip Daniel. I could have used this a couple of months ago but I found a clumsy work around. I am sure a new use will come up again. Thanks for sharing.
WoodRodent Glad you found a way though, there's lots of ways to skin a cat, right? I prefer laser beams...
Awesome! I'm working on a project that needs this exact procedure where I need to loop through the folder, then the workbook, worksheets, and rows to extract several items. I was having difficulty looping through the folder so this helps. Thanks!
this was very well explained and in a simple manner....
This is awesome thanks a lot. Loop works fine and help my task to perform in 20 seconds.
you are my savior, came in to thank you for your guidance!!!
you're awesome!... bow to you master!!
Pastulio A thanks my friend!!
Thanks Daniel. Very cool loop.
So welcome, my friend! Thanks for the comments!
Thank you to the moon and back!
You're so welcome, Nasta!
Thank you so much for this video.. helped me a lot..
Thanks for this great video!
I am working on a MacBook and the line : Set fso = CreateObject("Scripting.FileSystemObject") is creating an error. After some research I found that this is not an applicable way for Mac users.
Do you know a way it will also work for Mac users?
Many thanks again for this great video.
Cheers
@10:08 I don't think you needed to move the 'y=thisworkbook.cell.row.count + 1' code. Establishing it at the top is fine because the loop has y=y+1 after each row of data goes in. So when the next workbooks opens, the master workbook is already 1 row below the last row. Am I wrong though?
Thank You for your Useful video! What type of MIC you use?
Hi Learn Excel,
I used to use a plantronic headset or Microsoft Lifechat. I now use a Blue Yeti, very versatile mic and it's USB.
Thanks
Dan
I am looking for the code to Loop through workbooks in a folder and manipulate! Targeting a specific sheet to merge.
Thanks so much!! How can I do it with a folder of TXT files?
Hi! Can I ask can you reference a cell value in fso.getfolder like example fso.getfolder(worksheet.range("A1")? Thank you.
Can use power query to make it easier
Fantastic!!!
So glad you like!
Is there a way to add a code to place a new column and display the file name? for each set of data? thanks
Hi!
First of all I would like to thank you and your channel. I learned VBA thanks to your channel! Great work, keep it up!
Now I have a question: is it possible to display the email with attachment when you are sending email from excel through GMAIL via VBA?
I know that it is possible with outlook. You just press a shortcut and outlook email opens with attachment attached already. And what i need to do is just write text and choose receiver of the email.
I found certain macros (with or without CDO.message) that allow me to send email with GMAIL instead of outlook. However, it is possible only to send and only if receiving email and text is included in macro. What I need is: new email should be displayed with current file already attached. Because every time excel will be sent to different people with different text. So setting it by default in macro does not work for me. Any ideas?
Hello! Does this article help? stackoverflow.com/questions/31712849/vba-code-to-send-email-through-gmail-with-pdf-attachment
I'm thinking if you want a custom message each time, just update the objEmail.TextBody property with whatever message you need in the Body of the email. This could be done dynamically or via a textbox on your userform, what have you.
Hope that helps.
Thanks
Dan
Very nice Thank you
Thanks for the amazing video
You're very welcome, Syed! Thanks for the comments!
Mate love your work.
I have a question and its regarding cab for word.
I have a folder of RTF files. I need to convert the RTF files into txt files so I can open them in excel and macro them to another excel spreadsheet.
Can you please help. Working on it for work and I cannot download any programs or anything.
Thanks in advance
I’m not sure why we have to put y=y+1
Didn’t we already set y = last row + 1?
So shouldn’t it always go to last row and add 1? Regardless as to how much new data we’ve entered?
Hi canefan17,
Great question! Since y represents the row being written to, once we use up that row, we need to increment the row number so the next item will use the new row.
The first time through, excel had to figure out the last row for y, but thereafter it was just simpler to increase the number by one, rather than having excel look it up the more resource intensive way.
Thanks
Dan
ExcelVbaIsFun makes sense. Thanks.
Subscribed - great channel so far.
@@canefan17 very welcome, my friend!
Dear sir can you please place a huge data with inventory with opening, purchases and sales and closing for each date and then next date opening items and current purchases with sales then closing stock for date to date and then months, years etc., can you please help us
Is there a way to do this using ADO, so the workbook doesn't need to open?
I need your help in re-correcting the macro. My file in the folder have data for sheet 2 & 3. So what happening means maco is picking all the data from all the sheets for specified columns. I need only first sheet to be consolidated across all the files in the folder.
I am unable to download Excel VBA - Loop Through Multiple Files in a Folder and Scrape Data From Each. Please assist.
Hi, in the sheet if data to copy starts from row 13 in each sheets and cel (2,1) has date than we need to copy date to new column and when we paste the data in master sheet the date value for each sheets will be there. can we do that....
This is great! However I am having a bit of trouble editing it for my needs. I need to copy 1 row of data (always the same row) from a number of .xlsm files in a folder and then preferrably move those files to a new folder titled "archives". I am getting stuck at the wsLR = spot in your code. Is there a way i can contact you for help? Id be willing to pay for your services if needed
I'm on a site that has several tabs. i can click on it but each clickable tab has no id, name or class.
It just has: Title
Title ("Music Tab")
I have tried
IE.Document.getElementsByTitle("ElementName")(0).click
IE.Document.getElementsByTitle("ElementName")(0).submit
IE.Document.getElementsByTitle("ElementName")(0).select
IE.Document.getElementsByTitle("ElementName").click
IE.Document.getElementsByTitleName("ElementName")(0).click
But none of those work
Gut one, thank you
Thank you, janez!
1 of the files in the folder is password protected. Where could I put in a code to handle a password protected file? And what would the code be?
You didn't dim your variables, I dont understand how this is even running because it wont on my end. Please help
u r the BoSS!!.....
I'm running into this error message: file is already open, reopening will cause any changes to be discarded. it is because I have to have one workbook open to run the macro. How can i solve this? Thank you!!!
excuse me, but why didn't you declare wbFile as a File? If you say you don't have to, why?
Hi Daniel It's me again :) Could you please do the same, but based on cell value ,especially if the cell value is Date and the sheet is almost a million rows.
The first column contains symbols, and the second contains dates . Dates are the same for each symbol and I have to separate it by dates. Thank you in advance
I'm having issues with the "y =" step. Mine has to debug there? not sure what is going on
Brianna Beesley I had the same, then realised I was referring to the sheet as ‘Sheet 1’ rather than ‘Sheet1’ 🤦♂️
Mashalllah
HTTP 500 error on your website
What if I want xlsx, xlsb, xlsm. I want all extensions so long as it’s an excel file.
Hi canefan17,
You could change the line that checks this to include other checks using the OR command, such as:
If fso.GetExtensionName(wbFile.Name) = "xls" OR fso.GetExtensionName(wbFile.Name) = "xlsm" OR fso.GetExtensionName(wbFile.Name) = "xlsb" OR fso.GetExtensionName(wbFile.Name) = "xlsx" then
OR... you could just use the LEFT() function so it can use any extension that starts with xls, such as:
If LEFT(fso.GetExtensionName(wbFile.Name), 3) = "xls" then 'if leftmost 3 characters are xls
Hope that helps
Thanks
Dan
ExcelVbaIsFun very helpful. Good stuff.