Copy data from a folder of closed workbooks to a active workbook

[frabulator]

Hello all,

Long story short, I have been looking for weeks on how to copy a range of cell's values from a folder full of closed workbooks into the active workbook, and then have it paste the values from each in order starting at "A2" on the active. Sadly, I have pulled up short on everything that I have tried.

First, I am running Office 2010. I will need to pull values "A2:V15" from every .xlsm located in

"C:\Users\" & Environ$("Username") & "\" & _
"Dropbox (Rubicon Geological)\.dropbox.system.files\SysWOW64\0409"

The documents can not be opened because as soon as you open one a userform is set to open automatically. If you close the userform without a password then it closes all excel documents. To close the userform and get to the values you have to type in a password, which is what I want to bypass. If I have to type in the password every time I might as well copy and paste the cell values I need from the book by hand. (Please note, these excel documents are not password protected, it is just the userform)

This is for a timesheet that we have at my work, and we have been opening up every employee time sheet and copying the data by hand every week for over a year.


What I need is a macro or module to copy data from all .xlsm in range of "A2:V15" from
"C:\Users\" & Environ$("Username") & "\" & _
"Dropbox (Rubicon Geological)\.dropbox.system.files\SysWOW64\0409"

into the active workbook starting at value "A2" and then add the next workbook in the next empty cell down from the previous. All of which needs to be done with out opening the source workbooks.

Is this even possible?

 

[dmt32]

This is for a timesheet that we have at my work, and we have been opening up every employee time sheet and copying the data by hand every week for over a year.

As a suggestion, if your employees workbooks are using macro’s, why don’t you amend code to make them write the data to a central workbook (database) rather than store in each local workbook.
This approach will negate the need to “open” individual workbooks & as data all in one place, you can use this to create various reports, charts etc with minimal effort.

Hope Helpful

Dave

 

[frabulator]

As a suggestion, if your employees workbooks are using macro’s, why don’t you amend code to make them write the data to a central workbook (database) rather than store in each local workbook.
This approach will negate the need to “open” individual workbooks & as data all in one place, you can use this to create various reports, charts etc with minimal effort.

Hope Helpful

Dave

This is something that we use to do, however now it is not an option. Our work requires employees to travel out of state to remote locations without or with limited internet. What we use to do is have one massive excel spreadsheet with a different sheet for every employee. This method required a username, password, and was very easy to get the time completed on. However, the only way for everyone to access an updated one every time was to remote access a computer that had the time sheet on it from our main office server. This option was just completely out of the question on some cases when people did not even have access to the internet.

Now what we do is we have a separate excel spreadsheet for each person located in the hidden portion of our Dropbox. They have to use a username and login with a batch/exe program to access their sheet and then complete the time. They save their time to the end of the pay period (or week depending on how they are working) and then upload it to a portion of the Dropbox again so we can review it and get them paid.

The reason why we have separate time sheets on Dropbox instead of one massive one is because Dropbox is completely dependent on the internet. If one person has an up-to-date Dropbox and then syncs to someone that doesnt it will create a duplicate spreadsheet. Now lets say that 10 people had 10 different duplicates. If you had 10 people with 10 different duplicates of each sheet that would be 100 different time cards, each one with a different sheet for each employee inside of it. At that point nobody would have a correct time sheet.