SolvedPrivate

vba code to copy data from a sheet

Posted on 2015-01-26
28
59 Views
Last Modified: 2016-02-10
Dear experts,

I want a vba code which can perform the following:

1. Copy the data from a range in a sheet (to be named) from a excel file in the folder on to a new sheet. The column A will include the name of the file from which the data has been sourced.
2. The vba will append the data (one below the earlier) from the various sheets in the folder.

Thank you
0
Comment
Question by:Excellearner
[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
  • 13
  • 7
  • 5
  • +3
28 Comments
 
LVL 18

Expert Comment

by:Simon
ID: 40571696
Is it a single spreadsheet you want to retreive data from?
Do you want to prompt the user to browse and select the file each time?
How is the worksheet within the workbook to be identified - i.e. always sheet1 or a known sheet name?
Is it a single row range? - assume so if you want the source file name to go in column A.
0
 
LVL 23

Expert Comment

by:Michael74
ID: 40571697
What you are asking for should not be too difficult but we need more detail
eg Number of workbooks that need to be read from, Named range identifer, sheet number named range is on, number of columns etc

If you could you please post a couple of example workbooks with dummy data (same format) it will provide a lot of the information we need
0
 
LVL 10

Accepted Solution

by:
broro183 earned 167 total points
ID: 40572253
hi Excel Learner,

This question seems quite similar to one that I have helped you with previously (way back in 2011!). Can you use Ron DeBruin's Merge Addin this time as well?

The addin is a FREE soundly developed & tested addin by Ron DeBruin, a well known excel wizz (a certified "Microsoft Most Valued Professional") and I believe the screenshot on the page shows that it will do something very similar, if not exactly, to what you are asking for. Once you have (re)installed the addin, choose the "Select files...", "Fixed Range", "add file names" and other options as you see fit.

http://www.rondebruin.nl/win/addins/rdbmerge.htm

hth
Rob
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 26

Assisted Solution

by:ProfessorJimJam
ProfessorJimJam earned 167 total points
ID: 40572332
Hi ,

I think the below is what you are looking for. so this will get you started.

Capture.PNG
http://www.likeoffice.com/image/users/28057/ftp/my_files/excel-product/importer.zip

this is a trial copy from Likeoffice web
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40572403
0
 

Author Comment

by:Excellearner
ID: 40572610
Hi Simonadept,

Thank you for your response.

Is it a single spreadsheet you want to retreive data from?
YeS,

Do you want to prompt the user to browse and select the file each time?
No, a specific cell range within a sheet will be hardcoded in the vba code. But ofcourse the cell range should be amendable in the code to tailor the vba to each of the specific situations

How is the worksheet within the workbook to be identified - i.e. always sheet1 or a known sheet name?
Yes, a specific sheet which will be standard across all the excel files

Is it a single row range? - assume so if you want the source file name to go in column A.
As specificied it will be a range like say c2:d55


Further as this an accumulation of similar nature of data from several of the spreadsheets (files), the individual ranges copied should be pasted one below the other. As a identifier of the data set, column A will host the file name. In other words, say
Say range A2: A55 will have file name as ‘xyz23march2014’ and cell range c2:d55 will include the pasted data
Next A56:109 will have file name as ‘xyz24march2014’ and cell range c56:d109 will include the pasted data and so on
0
 

Author Comment

by:Excellearner
ID: 40572631
Hi Simonadept,

Please find attached an example

Further I would like one complication to this process. The excel could be located in the subfolder level. for instance

Main folder (this has only sub-folders and no spreadsheets)
->Sub-folder 1 (this has only sub-folders and no spreadsheets)
---> Sub-folder 1.1 (this has only spreadsheets, say 4 spreadhseets)

The Macro should detect any folder name starting iwth 'xyz' and seek to perform the code only on these excel files.

Hope it is clear now.

Apologies for not being clear in the first instance.

Thank you
0
 

Author Comment

by:Excellearner
ID: 40572639
Michael74, sorry I missed to include your name in my address.
0
 

Author Comment

by:Excellearner
ID: 40572756
ProfessorJimJam,

the chandoo's solution does include a template where we can capture the source file name but not the sheet name.

thank you
0
 

Author Comment

by:Excellearner
ID: 40573002
boro,

I did not update you.

Two issues cropped up to use your suggestions

1. A particular licence on my pc for ms office has expired, due to which I am not able to install the add-in
2. Even if I could install the add-in I am not sure my pc would let me turn-on the macro trust certification in excel options.

Hence I am request for a vba solution.
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40573062
why didn't you use the tool from officelike?

see attached. i created new file Book1 and it works. added info and it works.
Book1.zip
0
 

Author Comment

by:Excellearner
ID: 40573690
ProfessorJimJam,

Thank you. Does it handle range or only cell at a time.

I need a macro which can handle the range.

thank you
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40573823
It can handle range too

Try and let me know
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40573828
Instead of single address put for example A1:G25.
0
 

Author Comment

by:Excellearner
ID: 40574082
Prof.

Please find attached the sample of my experiment.
Master.xls
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40574120
can you please attach the other files as well?  I can see that you did not change the source worksheet name in the file.

as I can see it is still the old sheet name, as I sent it.
0
 

Author Comment

by:Excellearner
ID: 40574221
Prof.

Thank you for response.

I will get back to my job in 3 hrs and then I will try to reperform this utilityou have given and gcomeback with the result.

But I think this should be a simplace macro to develop. Probably because of lack of knowledge I am underestimating the effort.

Thank you
0
 
LVL 10

Expert Comment

by:broro183
ID: 40576952
hi Excel Learner,

1. A particular licence on my pc for ms office has expired, due to which I am not able to install the add-in.
I am not aware of any licenses being required for installing addins within Excel. To help my general understanding, what is the name of the license?
Is the license for an anti-virus/malware programme or for Winzip/WinRAR (to unzip the addin file)?
Are using a Trial version of MS Office?
What steps do you take when you try to install the addin?


2. Even if I could install the add-in I am not sure my pc would let me turn-on the macro trust certification in excel options.
Hence I am request for a vba solution.

For many intents & purposes (esp in Excel), "macros" & "VBA" are just different words for the same thing. The RDB Merge addin is an "xlam" (excel addin file) which is just a type of excel file that can contain VBA code. An advantage of using an addin over a normal Excel file containing code is that once it is installed it "opens silently in the background" when excel is opened & doesn't clutter up your screen by appearing as another visible file.

Rob
0
 
LVL 32

Assisted Solution

by:Robberbaron (robr)
Robberbaron (robr) earned 166 total points
ID: 40579154
try this one.  likely needs work on formatting and subfolder search
Dim curWb As Workbook, curWS As Worksheet, curRng As Range
Dim destWB As Workbook, destWS As Worksheet, destRNG As Range

Dim ziFolderStart As String, ziFilePrefix As String, ziWorksheet As String, ziRange As String
Dim zoSheet As String, zoRange As String

Sub Start()
    Dim curFileName As String

    
    ziFolderStart = Range("Folder_Start").Value
    ziFilePrefix = Range("Workbook_Prefix").Value
    ziWorksheet = Range("Worksheet_Name").Value
    ziRange = Range("Data_Range").Value
    
    zoSheet = Range("OutputSheet").Value
    zoRange = Range("OutputRange").Value


    Set destWB = ActiveWorkbook
    Set destWS = destWB.Sheets(zoSheet)
    Set destRNG = destWS.Range(zoRange)
    
    'process start folder
    processFolder (ziFolderStart)
    
    Dim subfolder1 As Variant
    Dim subfolder2 As Variant
    
    subfolder1 = ListFolder(ziFolderStart)
    
    For i = 1 To UBound(subfolder1)
        'process all matching files
        processFolder (subfolder1(i))
        
        'check for next level subfolder
        subfolder2 = ListFolder(subfolder1(i))
        For j = 1 To UBound(subfolder2)
            processFolder (subfolder2(j))
        Next j
    Next i
    

End Sub

Sub processFolder(fldrName As String)
    Dim curFileName As String
    curFileName = Dir$(fldrName & "\" & Range("Workbook_Prefix").Value & "*.xls?")
    Do While curFileName <> ""
        'copy source data range to current output location
        Set curWb = Workbooks.Open(Filename:=fldrName & "\" & curFileName, ReadOnly:=True, AddToMru:=False)
        Set curRng = curWb.Sheets(ziWorksheet).Range(ziRange)
        curRng.Copy Destination:=destRNG.Offset(0, 1)
        
        'set source name cells
        destRNG.Resize(curRng.Rows.Count, 1).Value = curFileName

        'move to next output location
        Set destRNG = destRNG.Offset(curRng.Rows.Count, 0)
        
        curWb.Close (False)
        
        'get next filename
        curFileName = Dir$
    Loop

End Sub

Function ListFolder(sFolderPath As String) As Variant
     
    Dim FS As New FileSystemObject
    Dim FSfolder As folder
    Dim subfolder As folder
    Dim i As Integer

    Set FSfolder = FS.GetFolder(sFolderPath)
    i = FSfolder.SubFolders.Count
    ReDim folderlist(i) As String
    
    For Each subfolder In FSfolder.SubFolders
        i = i + 1
        folderlist(i) = subfolder
    Next subfolder
     
    Set FSfolder = Nothing
    
    ListFolder = folderlist
       
End Function

Open in new window

GroupData.xls
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40590677
ExcelLearner,

can you give us feedback, if the provided solution did not work?  so that i come up with alternative?
0
 

Author Comment

by:Excellearner
ID: 40609695
Everyone,

I am back in action, will give the feedback over today/tomrrow and close the question.
0
 

Author Closing Comment

by:Excellearner
ID: 40626922
My office laptop has issues with the macro access.
But thank  you all for the several thoughts, time and inputs.
0
 
LVL 10

Expert Comment

by:broro183
ID: 40627878
hi Excel Learner,

I feel that we haven't got to root cause of your issue yet. Since you have assigned points for this question (although I don't feel I deserve any), do you want to raise a new question to see if anyone can help resolve your "issues with the macro access"?

Rob
0
 

Author Comment

by:Excellearner
ID: 40630187
Boro,

You are correct, honestly what I have done is not a professional way of treating of your (expert's precious) time when you try to help resolve my issues.

I wish to start with a new question on this subject today.

Kindly help.

Thank you
0
 

Author Comment

by:Excellearner
ID: 40632005
Boro,

Could you please spare some time and help me on ID: 28624651.

Thank you
0
 
LVL 10

Expert Comment

by:broro183
ID: 40632583
hi Excellearner,

I can't find that question. Can you please post the whole URL?

Rob
0
 

Author Comment

by:Excellearner
ID: 40632636
Rob/broro,

I think the admin has deleted that question.

I am happy to create a new question once agian.

but here is the content

Dear experts

Kindly provide a macro which can copy select range for a spreadsheet at a time but repeat this exercise over several sheets listed in the range

Inputs to macro:
Range for the several sheets provided above

What is expected from the macro
1. Selected a path from column starting with the first folder from row 2
2. Open the file
3. Do not activate( update) for linkages
4. copy the range into the destination sheet from column B (Pattern is shown in sheet 'destinate sheet' in this file), then leave two blank before copying the next range
5. Column A will include the file name  (details in the column above)
6. close the file in the current folder without saving
7. Move to the next file in row 3 above and repeat exercise from point 1 onward and complete this iteration untill all the rows have been completed
8. Provide message when all the actions have been executed

All linked files to the file mentioned above will be in the same folder and there are no linked to files outside the folder.
File-folder-range-details-for-copy---mac
Update-msg-screen-dump.docx
0
 
LVL 10

Expert Comment

by:broro183
ID: 40632771
hi Excellearner,

If it is possible please delete the content of your previous message, as it doesn't belong in this thread. It belongs in a question of its own.

By viewing your History via your User Profile I can see that you have created two questions that are very similar to what you have posted in the last message (ID: 40632636):
- Macro for deleting values in several sheets (Q_28624034)
- Macro to copy and paste (as values) from several files listed (Q_28624121)

My understanding of the purpose of this site, & my reason for being here, is to voluntarily help people with specific issues. The above questions appear to be entire projects rather than specific issues. So I agree with the comments made by Aikimark & Phillip Burton comments (ID: 40632383ID: 40632383ID: 40632644 resp.) that you should investigate the "hire me" option which some experts have. I don't have this option & I'm not actually sure where it is displayed but I'm sure it is easy to find if you look for it.

I also feel that the questions are so similar that an Excel Learner, such as yourself, could combine the common features & draw techniques from the answers to your previous questions to attempt your projects. Then you can raise questions for the aspects which you can't do yourself ie specific issues. Demonstrating that you've made an attempt by including your attempted code in your questions/uploaded files could very well encourage experts to be more enthusiastic participants in your future questions.

I look forward to seeing your attempts, progress & development in future questions.
Good luck!

Rob
0

Featured Post

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

759 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