Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 19

Thread: VBA to extract & combine data from multiple excel file from a specific folder

  1. #1
    Join Date
    29th August 2012
    Posts
    55

    VBA to extract & combine data from multiple excel file from a specific folder

    conditions:-
    a long list of excel files in FIXED folder (eg. C Drive: Data Folder)
    no fix number of files
    file comes in different file names
    file come in different worksheet name.

    how to have a VBA to automatically:-
    1) pull the data from this files with a FIXED column range but row will depends on each file
    2) combine it to one excel & auto save it

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    18th August 2011
    Location
    Brisbane, Austalia
    Posts
    3,020

    Re: VBA to extract & combine data from multiple excel file from a specific folder

    Hi Luvnet

    This is a very commonly asked question on this and other forums. THere is a lot of information not supplied. Like where is the data stored in the opened files, where is it going to, as single column or multiple columns. Asking the right questions leads to a more specific answer. Here is some generic code which should do what you wish. If you can manipulate it from your end all the better.

    Take care

    Smallman

    VB:
    Option Explicit 
    Sub OpenFile() 
        Dim sPath As String 
        Dim sFil As String 
        Dim strName As String 
        Dim twbk As Workbook 
        Dim owbk As Workbook 
        Dim ws As Worksheet 
         
        Set twbk = ActiveWorkbook 
        sPath = "C:\Data Folder\" 'Change to suit
        sFil = Dir(sPath & "*.xls") 
         
        Do While sFil <> "" 
            strName = sPath & sFil 
            Set owbk = Workbooks.Open(strName) 
            Set ws = owbk.Sheets(1) 
            ws.Range("A1", Range("A" & Row.Count).End(xlUp)).Copy 
            twbk.Sheets(1).Range("A65536").End(xlUp)(2).PasteSpecial xlPasteValues 
            owbk.Close False 'Close no save
            sFil = Dir 
        Loop 
         
        twbk.Save 
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    29th August 2012
    Posts
    55

    Re: VBA to extract & combine data from multiple excel file from a specific folder

    Folder Path - "C:\Users\Chau_Yee_Wong\Desktop\SI Orders"
    File type might be .xls or .xlsx
    Data to copy from each file is From A19 to J19
    Rows will be different for each file. Normally i use Relative Reference+End+Down after selecting the Col to range the visible row
    Combining data from all files available on that folder into 1 new file. Do the data should have 10col

    one new request.
    the excel file that been saved onto the folder will surely have a Date Modified details (Date & Time). can this details be populated onto excel file.
    meaning when i open the 1stFile, will copy the above range (eg A19:J21) & paste onto a new file. then add in the Date & Time details of 1stFile onto Col L.
    Then open 2ndFile from the folder, again go to the same col & relative ref down for the number of rows available (A19:J28), copy & paste it onto 1 row after 1stFile (A22:J31) & add the date & time details.
    continue this steps until all the file been open & copied the data onto the specific new file.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    29th August 2012
    Posts
    55

    Re: VBA to extract & combine data from multiple excel file from a specific folder

    note - all the files might have different worksheet name ^_^

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    18th August 2011
    Location
    Brisbane, Austalia
    Posts
    3,020

    Re: VBA to extract & combine data from multiple excel file from a specific folder

    Hi

    All of the items you requested on Post 3 are possible. This however

    note - all the files might have different worksheet name ^_^
    does concern me somewhat. Is the sheet object name the same in each workbook, for example is the data to collect on Sheet1? If not perhaps there is only 1 sheet in each file? Failing that is there some characteristic about the names on the workbooks which is unique? If the answer is no to all of those questions then there is nothing I can do to help. There has to be a unique item about the sheet you want to import.

    Take care

    Smallman
    Last edited by Smallman; August 31st, 2012 at 11:44. Reason: tpyo

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    29th August 2012
    Posts
    55

    Re: VBA to extract & combine data from multiple excel file from a specific folder

    can do a rename of the active worksheet whenever the file is been open, save it onto original file & then only pull the the data out.
    no concern if to rename the worksheet. but original worksheet name will be different when 1st open.
    thanks in advance for helping.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    18th August 2011
    Location
    Brisbane, Austalia
    Posts
    3,020

    Re: VBA to extract & combine data from multiple excel file from a specific folder

    In that case then the sample code I provided should work for you. However you have not answered the questions I posed on Post 2. Answer these or better, much better provide a workbook. That gives me all the information I need and you don't need to say anything.

    Take care

    Smallman

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    29th August 2012
    Posts
    55

    Re: VBA to extract & combine data from multiple excel file from a specific folder

    how do i attach file to you?

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    18th August 2011
    Location
    Brisbane, Austalia
    Posts
    3,020

    Re: VBA to extract & combine data from multiple excel file from a specific folder

    Hi

    Click on Go Advanced - use the paperclip and follow directions. Note your file needs to be less than 100kb. Just take some data out if it is.

    I am about to fly down the East Coast of Oz so will probably be off line for a day or two so, appologies if i am unable to respond quickly.

    Take care

    Smallman

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    29th August 2012
    Posts
    55

    Re: VBA to extract & combine data from multiple excel file from a specific folder

    file attached.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Macro To Updat .dot File With Variable Data And Save In Specific Folder
    By Anupam Shrivastava in forum Excel and/or Word Help
    Replies: 2
    Last Post: April 4th, 2008, 05:53
  2. Replies: 2
    Last Post: April 13th, 2007, 22:16
  3. Extract data from a specific location in a text file
    By Torque2much in forum EXCEL HELP
    Replies: 2
    Last Post: August 7th, 2006, 21:39
  4. Extract specific data from multiple sheets
    By Sable in forum EXCEL HELP
    Replies: 2
    Last Post: January 7th, 2005, 02:44
  5. Extract specific data from multiple sheets
    By Sable in forum EXCEL HELP
    Replies: 2
    Last Post: January 7th, 2005, 02:32

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno