Get data from closed workbook - ADO connection

[marzook]

Hi..

attached the file which get the data from closed workbook, basically it works with below steps. the code works fine only the problem is timing. it took long time to get the retrieve the data, is there any way to improve the code and speed up the process

1. select the multiple files from certain folder called "Report"
2. once its selected its copy the name of the worksheets from selected workbook and store in the sheet "Market Table"
3. then its loop through each sheets in the closed workbook and get the data from the sheets and stored in the sheet "Consolidation"

Please help..how i could speed the ado connection and get the data quickly

Thanks
Marzook

[patel45]

Your code opens the file, why do you use ADO connection for getting data from sheets ?

sorry, the code does not open files

[marzook]

thanks Patel- Yes, its open the files but the it takes long time to pull the data from the selected workbooks. there is aroung 50 workbooks to select and retreive data from and tooks aroung 20-25 minuts

I get the result as i required but mainly looking for how i could speed up the process...

[Kyle123]

Patel's point is that if you use ADO, you don't open the workbook - so don't open it Opening large workbooks is slooow

Edit: Actually having had a look at your code, I don't see where it does open the workbooks.

[patel45]

here

Sub GetData_FCST_MERGE_DPRT_LEVEL()
     SaveDriveDir = CurDir
    'MyPath = Application.DefaultFilePath    'or use "C:\Data"
    'MyPath = "E:\CENTRAL LOGISTICS\2013\Consolidation All\Consolidation Productivity Report\Prod Report"
    MyPath = ThisWorkbook.Path & "\Reports"
    
    ChDrive MyPath
    ChDir MyPath
    FName = Application.GetOpenFilename(filefilter:="Excel Files,*.xl*", _
                                        MultiSelect:=True)

[Kyle123]

That doesn't open the file, it just gets the filename/s

[patel45]

My bad, you are right, I changed my post

[marzook]

Its not actually opens the files,

first its list the sheetnames from selcted workbook
and then look through the each sheets and retreive the data.

Thanks
MM

[marzook]

any one can assit please..

[patel45]

Your code seems correct, given that your report files have many sheets, you can try opening them instead of using ADO connection