sql - 从关闭的文件中获取最后一行和最后一列或 UsedRange(并保持关闭 - ADO)

Question

我需要将 UsedRange 从七个已关闭文件复制到新工作簿。每个源文件大约 200M，因此我想在不打开它们的情况下提取信息。
我从这个网站 https://www.rondebruin.nl/win/s3/win024.htmthis 阅读了一些很好的信息，但这里的函数需要将要提取的特定范围作为参数。不幸的是，这个范围发生了变化，所以我很想找到一种方法来知道如何获取最后一列和最后一行。
提前致谢，
麦克风

Answer 1

Get last row and last column or UsedRange from a closed file (and keep it closed - ADO)...I'd love to find a way to know how to get the last column and last row.

这是你正在尝试的吗？

Option Explicit

Private Const adOpenKeyset As Integer = 1
Private Const adCmdText As Integer = 1

Sub Sample()
    Dim MyExcelFile As String
    Dim MyConnectionString As String
    Dim SQLString As String
    Dim MyCon As Object, MyRecordset As Object
    Dim lRow As Long, lCol As Long
    
    '~~> Change this to the relevant excel file
    MyExcelFile = "C:\Users\routs\Desktop\Test.xlsx"
    
    '~~> Connection string
    MyConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
                         MyExcelFile & _
                        ";Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
                        
    Set MyCon = CreateObject("ADODB.Connection")
    MyCon.Open ConnectionString:=MyConnectionString
    
    '~~> Getting details from Sheet1. Change as applicable
    SQLString = "SELECT * FROM [Sheet1$]"
    
    Set MyRecordset = CreateObject("ADODB.RecordSet")
    MyRecordset.Open Source:=SQLString, _
                     ActiveConnection:=MyCon, _
                     CursorType:=adOpenKeyset, _
                     Options:=adCmdText
                     
    MyRecordset.MoveLast
    
    '~~> Last Row
    lRow = MyRecordset.RecordCount + 1
    '~~> Last Column
    lCol = MyRecordset.Fields.Count
    
    MsgBox "Last Row:=" & lRow & vbNewLine & _
           "Last Column:=" & lCol
    
    MyRecordset.Close
    MyCon.Close
End Sub