Grab data from files and put to sheet with names:

Hello All,

I have a VBA question.

a. Loop through a folder in desktop and do this

b. if the current file in folder whose filename contains "collec1", copy data from sheet1 of that file to the "collec1" sheet of togetherDataSet.xlsx

c. if the current file in folder whose filename contains "volm1", copy data from sheet1 of that file to the "volm1" sheet of togetherDataSet.xlsx

I have attached the three sample files.
togetherDataSet.xlsx has two sheets "collec1","volm1"
345volm1Book3.xlsx
togetherDataSet.xlsx
xyzCollec1rwerwere.xlsx
RayneAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
als315Connect With a Mentor Commented:
May be in your case will be better simply link cells from other sheets and update these links? It can be done in Auto_Open sub or manually from button on sheet Run.
GetFiles.xlsm
0
 
als315Commented:
Look at sample (Save file to same folder with your files, enable macro and press button)
GetFiles.xlsm
0
 
gowflowCommented:
you want to copy from these files to sheet "collec1" or "volm1" at the END of the last row in the sheet or you want to replace the existing sheets and start from the beginning ?
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
gowflowCommented:
Well here it is
Load the attach file and make sure your macroes are enabled and activate the button in the sheet Main I created for you. It will look into the Desktop folder of the loged user who runs this macro and will look for both files containing "collec1" and "volm1" in their name and will append the content of the first sheet it opens at the last row of each respective sheet. The desktop may contain several occurences of these files at random sequence it will append them all following each others.

Pls check it and make sure when you test it that your files reside on the desktop as it is this that you requested. We may change that if you wish.

Let me know outcome.
gowflow
togetherDataSet.xlsm
0
 
RayneAuthor Commented:
Hello Als and Gowflow,

Thank you so much for you approaches and hard work. I would prefer if the sheet copy happens to the [together] workbook instead of creating a new workbook and pasting the data vlaues there....

Gowflow - when I am starting your code,  your code is stopping to work at this line
Fil = Dir


not sure why

Thanks all
0
 
gowflowCommented:
what version of excel do you have ? I tested it here and it works fine I hv 2007 and 2003
gowflow
0
 
RayneAuthor Commented:
Thanks Gowflow, I actually have 2010
0
 
RayneAuthor Commented:
Is it possible to have something that is version independent assuming this file that I am creatiing has  a possiblity of being used by both the 2007 and 2010 users?
0
 
gowflowCommented:
Well I was just asking, don't hv 2010 but will find why can you tell me exactly what error you get ? you said the macro stops it should highlight in yellow and give you an error what is the error ?
gowflow
0
 
RayneAuthor Commented:
Thanks Gowflow,

When i press the button and  I get "Are you ready to import files?)
I hit yes and then I get the attached box.
On hitting debug, this line is highlighted in yellow...
Fil = Dir

Let me know what else you need to know...
error.png
0
 
gowflowCommented:
when you stop the macro and the yellow goes does this line turn into brown for some reason ?? if yes then for some reason it remembered a break so click on this line and press F9 and the brown should go. Save the file and run it again.

If not then maybe 2010 does not like 'no brackets' so do this:

try replacing this
Fil = Dir

by this
Fil = Dir()

Let me know
gowflow
0
 
als315Commented:
Test sample. File togetherDataSet.xlsx should be in folder. Existing sheets will be replaced.
GetFiles.xlsm
0
 
RayneAuthor Commented:
Hello Gowflow, I still getting the same error after changing

Hello Als315 - is it possible to change your line
  .Sheets("sheet1").Copy After:=wbNew.Sheets(wbNew.Sheets.Count) ?


so that the copy happens to cell B2 of the two destination sheets....I would NOT like to delete the two sheets as they have other important information


thank you
0
 
RayneAuthor Commented:
The data coming from the two source sheets should be copy pasted as values to the cell B2 of each destination sheet of the destnation workbook. In the process, there should not be any creation of new worksheet to paste the data as that is not a requirement
0
 
als315Commented:
"I would NOT like to delete the two sheets" - what if in new files will have less rows or columns then in source list? Should we analyze existing and imported data?
You have different sampe files. xyzCollec1rwerwere.xlsx has data from cell B2, when 345volm1Book3.xlsx - from A1. Will it be always or not?
0
 
RayneAuthor Commented:
Hello Als315

what if in new files will have less rows or columns then in source list? Should we analyze existing and imported data?-
Both the  sheets have important data stored on the first Row, so maybe clear contents the two desitnation sheets totally Except for the first row and then do the copy over from the source


You have different sample files. xyzCollec1rwerwere.xlsx has data from cell B2, when 345volm1Book3.xlsx - from A1. Will it be always or not?
-My apologies - actually in reality both the source data sheets will start from A1 cell in the both the source workbook...and that will always remain the same. I made a typo in the attached files showing different - that is A1,B2
0
 
gowflowCommented:
Sorry was out just came back. you still did not tell me the error ? what error what does it say what is the error ??? I cannot troubleshoot if you don't tell me what is the error?
Furthermore will you have more than 1 file of each type ?

Anyway check out this version and tell if you still hv an error.
gowflow
togetherDataSet.xlsm
0
 
gowflowConnect With a Mentor Commented:
Hv you tried the last solution I posted ?
gowflow
0
 
RayneAuthor Commented:
Thanks Gowflow and Als for your comments.

I appreciate both of your efforts and suggested ideas. . I decided to do a FSO to loop through the files as Filename = DIR was not working properly for some weird reoson i don't know. but FSO worked perfect if I had to loop through.
0
 
RayneAuthor Commented:
I got the idea from here:
http://mariaevert.dk/vba/?p=99
0
 
gowflowCommented:
Well good for you. It would hv been my next suggestion if you had replied with what was the error. Unfortunately I do not hv 2010 and seems for some reason it does not appreciate Dir. Glad it workked for you at the end.
gowflow
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.