Copy sheet from closed workbook?



i was wondering why its keeps forever to load  this code to copy the sheet from a closed workbook to the open workbook

    Set objConnection = CreateObject("ADODB.Connection")
    Set objRecordset = CreateObject("ADODB.Recordset")
    objConnection.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & DbFile & ";Extended Properties=""Excel 12.0 Xml;HDR=Yes;IMEX=0"";"
    objRecordset.Open "Select * FROM [ArtDB$]", objConnection
    Sheets("ArtDB").Range("A2").CopyFromRecordset objRecordset

Its extremely slow for copieen 1 sheet from closed workbook to my open workbook

Are they some other ways to make it faster to copy the sheet to my open workbook?





It's because you copy phenominal 17 billion cells (17,179,869,184 to be precise). That is all cells in the specified sheet. Presumbly you do that in the belief that by using the data connection you can avoid "opening" the workbook. You do not see why you should "open" a workkbook while you want to extract data from a closed one. So, let's dwell on the action of "opening" a workbook for a moment.

A workbook consists of a string of 1s and 0s on a disk. That string has a beginning somewhere. "Opening" a file does not much more than assigning the disc address of that first byte to a variable. From there, by following the string, the end can be found and everything in between can be extracted by parsing it. Parsing is done in memory but Excel will only load those parts of a workbook into memory that it needs to access. At opening time that isn't specified.

You can see that using a data connection will not dispense with the need of either finding the file or parsing the parts that you want to access. Therefore your intention not to "open" the file is thoroughly thwarted. You can also see that "opening" the file, by any name, isn't such a fearfully big thing. Excel opens and closes files all the time - in fact, every XLSX workbook is a collection of zip-files in itself - and you don't even notice because Excel doesn't tell you as it opens, unzips and closes its files while you merrily punch the keyboard.

So, instead of venturing into big data territory with a data connection, why not stick with what VBA has provided for your purpose? Open the file invisibly, copy your worksheet, and close it again - all in a fast blink of an eye.

You can adapt this code to your requirements.


Answer the Question

You must create an account to use the forum. Create an Account or Login