Macro to pull date from multiple workbooks in a single workbook

[omeirm]

Hello Everyone, I am trying to create a macro that will pull all the data from 80+ files into one sheet. In each of these 80 files they have individual names (ex: Upload_ABC, Upload_DEF,...) The date is in the 3rd tab of each sheet from A:T. I need a macro where I can put in the individual files in a top sheet. I can attach the end file template where the date needs to go in, just not sure how to attach it here. Any help is appreciated.

Thank you
Omeir

 

[pmich]

Re: Help with a macro to pull date from multiple workbooks in a single workbook

Thanks @mumps: I searched through the web and got some more info on this DIR. It is really great. Thanks for your guidance. I am still learning.

 

[mumps]

Re: Help with a macro to pull date from multiple workbooks in a single workbook

My pleasure.

 

[omeirm]

Re: Help with a macro to pull date from multiple workbooks in a single workbook

Thank you so Much!! it works great!. Any tips on where I should start as I do want to learn the basics of VBA and work my way from there.

 

[mumps]

Re: Help with a macro to pull date from multiple workbooks in a single workbook

You are very welcome. You can start by looking at the very first thread in this Forum. It is highlighted in blue. Then try hiker95's link: https://www.mrexcel.com/forum/excel...e-template-excel-spreadsheet.html#post4972842

 

[pmich]

Re: Help with a macro to pull date from multiple workbooks in a single workbook

@mumps In an example on the use of DIR, 23 is used.

strFn = Dir(strPath & "*.*", 23)

I could not see any tutorial on this 23.
Your code works even in the absence of 23.
Kindly explain what is 23.

 

[mumps]

Re: Help with a macro to pull date from multiple workbooks in a single workbook

Have a look at these 2 links:
https://www.exceltrick.com/formulas_macros/vba-dir-function/
https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/dir-function
The number is a file attribute that defines what type of files and folders are returned. In the example you mentioned, the "23" is actually a sum of attributes (1, 2, 4, 16) so it would look for a folder and include system files, hidden files and read only files.

 

[pmich]

Re: Help with a macro to pull date from multiple workbooks in a single workbook

Thanks for the valuable info @mumps.
Yes, if 23 is included it pulls "D:\$RECYCLE.BIN" and "D:\System Volume Information", which I wanted to avoid.
You have given the URLs of the very same sites which I already visited. I am glad that I am moving on the right direction. Thanks for the links.
In the table of attributes given in the msdn site, 23 was not mentioned. Thanks for informing that it is the addition of the selected attributes. Hope if I will exclude the attribute of the system file then the system file names wont be pulled.
Your explanation is of great help. Thanks.

 

[mumps]

Re: Help with a macro to pull date from multiple workbooks in a single workbook

My pleasure.