0

I am a new programmer and I am trying to find a way to extract one range of data from multiple workbooks and copy them into a master file. I have already wrote the code to do this below, but the problem that I am having is that my code physically opens the xlsm files > copies the data > then goes back into the master file to paste. Since this is being done to thousands of files at once, it takes hours to complete. My boss told me there is a way to copy the data from the xlsm files without having the code actually open the file if it is read as xml or as a .txt file. I have searched online for this, but cannot find anything on how this would be done. Any help would be greatly appreciated.

The code I have that physically opens the workbooks:

Option Explicit

Sub ReadDataFromCloseFile()
    On Error GoTo ErrHandler

    Application.EnableEvents = False
    Application.ScreenUpdating = False

    Dim FileType As String
    Dim FilePath As String

    FileType = "*.xlsm*" 'The file type to search for
    FilePath = "C:\Users\hasib\xlsm's\" 'The folder to search

    Dim src As Workbook
    Dim OutputCol As Variant
    Dim Curr_File As Variant

    OutputCol = 9 'The first row of the active sheet to start writing to

    Curr_File = Dir(FilePath & FileType)

    Do Until Curr_File = ""
        ' OPEN THE SOURCE EXCEL WORKBOOK IN "READ ONLY MODE".
        Set src = Workbooks.Open(FilePath & Curr_File, True, True)

        Sheets("Reporting").Range("I7:I750").Copy

        Workbooks("Master.xlsm").Activate
        Sheets("Sheet2").Select
        Sheets("Sheet2").Cells(4, OutputCol).Select
        ActiveCell.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
        Application.CutCopyMode = False
        OutputCol = OutputCol + 1

        ' CLOSE THE SOURCE FILE.
        src.Close False             ' FALSE - DON'T SAVE THE SOURCE FILE.
        Curr_File = Dir
    Loop
    Set src = Nothing


    Application.EnableEvents = True
    Application.ScreenUpdating = True

ErrHandler:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub
Community
  • 1
  • 1
Hasib Ibradzic
  • 29
  • 1
  • 12
  • Can you make the vba code do that? Since there are thousands of files I don't want the user to have to manual do anything. – Hasib Ibradzic Apr 29 '16 at 13:55
  • 2
    to overcome your Performance Problem you should implement a better copy and paste manner, DON'T Select the cells copy it directly ... I would recommend you to read this -> http://www.ozgrid.com/VBA/SpeedingUpVBACode.htm – Doktor OSwaldo Apr 29 '16 at 13:56
  • I will definitely try to update the copy/paste code now, but why don't you think getting the data extracted without opening/closing the workbooks will speed it up? – Hasib Ibradzic Apr 29 '16 at 14:01
  • 1
    Forget it, i am sorry, there is a vba method for that Problem. Look at that -> http://stackoverflow.com/questions/25287549/how-to-optimize-opening-and-closing-excel-workbooks-to-extract-data-to-run-faste But you should update your copy and paste functions too =) – Doktor OSwaldo Apr 29 '16 at 14:06
  • Thanks I really appreciate it! and I tried updating the code to below code, but it never actually pastes the data. I never get an error though. It runs through each workbook but nothing happens.... – Hasib Ibradzic Apr 29 '16 at 14:08
  • src.Sheets("Reporting").Range("I7:I750").Value = ThisWorkbook.Sheets("Sheet2").Cells(4, OutputCol) – Hasib Ibradzic Apr 29 '16 at 14:09
  • The execute for macro thing is a pretty good answer. Besides directly outputing the variable as suggested above, you could also store everything in an array before hand which will greatly increase run time. Of course you'll need to learn to redim the array, but if youre new I think it would be a good exercise. Check this link http://codereview.stackexchange.com/questions/121434/looping-through-files-in-a-folder the suggestion marked as answer if a really good example of this – Doug Coats Apr 29 '16 at 14:17
  • Thank you. That really helps a lot! – Hasib Ibradzic Apr 29 '16 at 14:20
  • 1
    If performance is an issue, and the data you want is in a "flat table" format, use an OLE DB connection to access the data rather than opening the workbooks in Excel. Reading the XML is possible, but something of a pain in VBA as there's nothing built-into the language for opening/closing the Office Open XML "zip" files. – Cindy Meister Apr 29 '16 at 17:47
  • i am asking out of curiosity, but have you solved it ? did you use ExecuteExcel14Macro ? I am interested in the Performance results =) – Doktor OSwaldo May 03 '16 at 14:00
  • No not yet. One of the treads took me to a link to try and do it via ADO which works perfectly when all the data is of the same type. Unfortunately I am carrying over numbers, texts, dates, and everything so ADO cannot handle that. I have created a very small test workbook that uses the ExecuteExcel14Macro and that works correctly. But my test only works for a single file and I am having trouble figuring out how to implement it in my current loop. As soon as I do I will update you though! Also, any advice on how to do this would help :) – Hasib Ibradzic May 03 '16 at 15:56
  • ADO cna work with different types, but i have Problems with this all the time too ;) if you Need help with the ExecuteExcel14Macro post your code, and i am sure somebody will help you =) – Doktor OSwaldo May 11 '16 at 10:36
  • Yeah I am having problems with it. Should I edit this question with the new code, or start a new question? I am still relatively new to this site, so I am unsure of what the best protocol is. – Hasib Ibradzic May 11 '16 at 13:51
  • @DoktorOSwaldo so I wasn't able to resolve is using the ExecuteExcel14Macro, but I did find a very quick and easy way to do it. 'ThisWorkbook.Sheets("Batch Results").Cells(OutputRow, OutputCol).Formula = "='" & FolderPath & "[" & FileName & "]Reporting'!$I7" OutputCol = OutputCol + 1' – Hasib Ibradzic May 13 '16 at 16:50

1 Answers1

0

I found out that there is a formula you can use in a cell that will pull in data from a closed workbook. If you type ='folderpath[filename]Sheetname'Cell into a cell it will automatically pull in that information. Using this logic I created the below to loop through all my files and paste data into my workbook from the files being called:

Set fd = Application.FileDialog(msoFileDialogFilePicker)

fd.InitialFileName = "c:\"
fd.InitialView = msoFileDialogViewList
fd.AllowMultiSelect = True

FileChosen = fd.Show

FilePath = fd.SelectedItems(1)
FolderPath = Left(FilePath, InStrRev(FilePath, "\"))

If FileChosen = -1 Then
'open each of the files chosen
For c = 1 To fd.SelectedItems.count
FileName = Dir(fd.SelectedItems(c))

ThisWorkbook.Sheets("Batch Results").Cells(OutputRow, OutputCol).Formula = "='" & FolderPath & "[" & FileName & "]Reporting'!$I7"
OutputCol = OutputCol + 1
Next c
End If

ThisWorkbook.Sheets("Batch Results").Select
Cells(1, OutputCol).Select
EndColumn = Split(ActiveCell(1).Address(1, 0), "$")(0)
RangeName = ("A1:" & EndColumn & "1")
Range(RangeName).Select
Selection.AutoFill Destination:=Range("A1:" & EndColumn & "558"), Type:=xlFillDefualt
Hasib Ibradzic
  • 29
  • 1
  • 12