SolvedPrivate

vba code to copy data from a sheet

Posted on 2015-01-26
28
55 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
  • 13
  • 7
  • 5
  • +3
28 Comments
 
LVL 18

Expert Comment

by:SimonAdept
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 25

Assisted Solution

by:ProfessorJimJam
ProfessorJimJam earned 167 total points
Comment Utility
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 25

Expert Comment

by:ProfessorJimJam
Comment Utility
0
 

Author Comment

by:Excellearner
Comment Utility
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
Comment Utility
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
Comment Utility
Michael74, sorry I missed to include your name in my address.
0
 

Author Comment

by:Excellearner
Comment Utility
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
Comment Utility
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 25

Expert Comment

by:ProfessorJimJam
Comment Utility
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
Comment Utility
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 25

Expert Comment

by:ProfessorJimJam
Comment Utility
It can handle range too

Try and let me know
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
Comment Utility
Instead of single address put for example A1:G25.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:Excellearner
Comment Utility
Prof.

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

Expert Comment

by:ProfessorJimJam
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 25

Expert Comment

by:ProfessorJimJam
Comment Utility
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
Comment Utility
Everyone,

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

Author Closing Comment

by:Excellearner
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Boro,

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

Thank you
0
 
LVL 10

Expert Comment

by:broro183
Comment Utility
hi Excellearner,

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

Rob
0
 

Author Comment

by:Excellearner
Comment Utility
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
Comment Utility
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: 40632383 & ID: 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

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

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Modern/Metro styled message box and input box that directly can replace MsgBox() and InputBox()in Microsoft Access 2013 and later. Also included is a preconfigured error box to be used in error handling.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

743 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

14 Experts available now in Live!

Get 1:1 Help Now