Solved

Grab data from files and put to sheet with names:

Posted on 2013-01-04
21
341 Views
Last Modified: 2013-01-06
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
0
Comment
Question by:Rayne
  • 9
  • 8
  • 4
21 Comments
 
LVL 39

Expert Comment

by:als315
ID: 38746535
Look at sample (Save file to same folder with your files, enable macro and press button)
GetFiles.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38746643
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
 
LVL 29

Expert Comment

by:gowflow
ID: 38746712
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
 

Author Comment

by:Rayne
ID: 38747095
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
 
LVL 29

Expert Comment

by:gowflow
ID: 38747101
what version of excel do you have ? I tested it here and it works fine I hv 2007 and 2003
gowflow
0
 

Author Comment

by:Rayne
ID: 38747106
Thanks Gowflow, I actually have 2010
0
 

Author Comment

by:Rayne
ID: 38747108
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
 
LVL 29

Expert Comment

by:gowflow
ID: 38747113
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
 

Author Comment

by:Rayne
ID: 38747126
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
 
LVL 29

Expert Comment

by:gowflow
ID: 38747143
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 39

Expert Comment

by:als315
ID: 38747160
Test sample. File togetherDataSet.xlsx should be in folder. Existing sheets will be replaced.
GetFiles.xlsm
0
 

Author Comment

by:Rayne
ID: 38747216
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
 

Author Comment

by:Rayne
ID: 38747236
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
 
LVL 39

Expert Comment

by:als315
ID: 38747260
"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
 

Author Comment

by:Rayne
ID: 38747285
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
 
LVL 29

Expert Comment

by:gowflow
ID: 38747552
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
 
LVL 39

Accepted Solution

by:
als315 earned 250 total points
ID: 38748081
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
 
LVL 29

Assisted Solution

by:gowflow
gowflow earned 250 total points
ID: 38748127
Hv you tried the last solution I posted ?
gowflow
0
 

Author Closing Comment

by:Rayne
ID: 38748156
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
 

Author Comment

by:Rayne
ID: 38748159
I got the idea from here:
http://mariaevert.dk/vba/?p=99
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38748173
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now