Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Grab data from files and put to sheet with names:

Posted on 2013-01-04
21
Medium Priority
?
357 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
  • 4
21 Comments
 
LVL 40

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 31

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 31

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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

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 31

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 31

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 31

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
 
LVL 40

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 40

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 31

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 40

Accepted Solution

by:
als315 earned 1000 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 31

Assisted Solution

by:gowflow
gowflow earned 1000 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 31

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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

715 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