Grab used range of a closed workbook with ADO

[Glory]

Any hints on how to accomplish this?

 

[Glory]

Bump.

 

[Norie]

You can't with ADO.

You could do something with ADOX which has a Catalog object.

This object has a tables properties and I think you can guess what that consists of when working with a 'normal' database.

When working with Excel it will show named ranges and worksheets.

Here's a very simple example.

Option Explicit
Sub test()
Dim cn As ADODB.Connection
Dim cat As adox.Catalog
Dim tbl As adox.Table
Dim ws As Worksheet
    Set cn = New ADODB.Connection
    
    Set cat = New adox.Catalog
    

    
    With cn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source=" & ThisWorkbook.Path & "\CatalogTest.xlsm;" & _
                            "Extended Properties=Excel 8.0;"
        .Open
    End With
    
    cat.ActiveConnection = cn
    For Each tbl In cat.Tables
        Debug.Print tbl.Name
    Next tbl
    Set cat = Nothing
    
    cn.Close
    
    Set cn = Nothing
    
End Sub

I only created one simple named range and it was listed.

Don't know how dynamic named ranges (DNRs), empty ranges, blank worksheets are dealt with exactly.

Empty ranges and blank worksheets are listed though. (didn't check DNRs)

 

[Glory]

I'm going to need to copy an entire (dynamic) used range. I've been dreading it, and putting it off. But I'm going to need to make a choice about whether to implement this soon. I'll keep looking into it. Thanks Norie.

 

[Norie]

Did you try the code I posted to see if it worked with dynamic ranges?

I didn't really sy it wouldn't, just that I never really tried.

By the way, what's so difficult about copying a dynamic named range.

Open workbook, copy range, paste range/whatever, close workbook.

 

[Glory]

Each data workbook takes.. here lemme check..

11.98 seconds to open, and there are five of them.

Would using ADO be a way to eliminate this latency?