VBA跨工作簿获取目标工作簿内已有数据的列号

Question

在不打开工作簿的情况下在A工作簿内获取目标工作簿sheet1已有数据的列号。

Answer 1

常规实现思路是障眼法，工作簿文件被打开了，但是用户看不到而已。代码如下。

Sub CSDNTest()

    Dim ExcelPath, ExcelName, ExcelShtName As String
    Dim shtExistFlag As Boolean
    Dim recorder As Integer
    recorder = 1
    
    ExcelPath = ActiveWorkbook.Path                 'A工作簿路径(默认与当前文件同目录)
    ExcelName = "A.xlsx"                            'A工作簿名(默认为A.xlsx)
    ExcelShtName = "sheet1"                         'A工作簿sheet名(默认为sheet1)
    
    '防止找不到A工作簿
    If Dir(ExcelPath & "\" & ExcelName) = "" Then
        MsgBox "The Excel file does not exist!"
        Exit Sub
    End If
    '若找到A工作簿，打开文件。这种方法工作簿文件被打开了，但是用户看不到。
    Set Excelwk = Workbooks.Open(ExcelPath & "\" & ExcelName)
    
    '防止找不到sheet1
    On Error Resume Next
    Set Excelsht = Excelwk.Worksheets(ExcelShtName)
    shtExistFlag = IIf(Excelsht Is Nothing, False, True)
    Err.Clear
    On Error GoTo 0
    If shtExistFlag = False Then
        MsgBox "The Excel file's sheetName does not exist!"
        Exit Sub
    End If
    
    '开始循环查找有数据的列号
    
    For i = 1 To Excelsht.Columns.Count
            If Application.WorksheetFunction.CountA(ActiveSheet.Columns(i)) <> 0 Then
            '将有数据的列号存放在当前文件的sheet1的A列中
            Sheet1.Cells(recorder, 1) = Split(Columns(i).Address, "$")(2) '将数字形式的列号转为字母形式
            recorder = recorder + 1
        End If
    Next
    '关闭文件
    Excelwk.Close
    
End Sub

运行结果

A.xlsx中的数据