当未使用vb6安装Office时，遍历excel工作表名称 [英] Iterate through excel worksheet names, when office not installed using vb6

问题描述

由于ACE.OLEDB提供程序，我可以在不安装excel的情况下阅读excel表格.我还可以使用以下方法遍历工作表，但是如果我没记错的话，它要求安装Office:

I can read an excel sheet without excel being installed thanks to the ACE.OLEDB provider. I can also iterate through the worksheets using the following, but it requires that office is installed if I'm not mistaken:

 Set xlApp = CreateObject("Excel.Application")
    Set wb = xlApp.Workbooks.Open(txtExcelFile, ReadOnly:=True, editable:=False)
    If wb.Worksheets.Count = 0 Then
    MsgBox "Excel file contains no worksheets"
    GoTo SubEnd
    End If
    Dim i        As Integer

    For i = 1 To wb.Worksheets.Count
        cboWorksheet.AddItem (wb.Worksheets(i).Name)
    Next

我希望能够查询Excel表格以填充下拉菜单，但是即使未安装Office也可以这样做吗?为了解释我们目前的情况，我为crm构建了一个动态导入器，通常，我们可以通过他们的服务器更轻松地访问公司，与客户端pc相比，其安装办公室的可能性较小.

I want to be able to query excel sheets to fill a drop down, but can i do that even if office is not installed? To explain our current situation, I've built a dynamic importer for our crm, and usually we have easier access to a company via their server which, compared to a client pc, will have a less probability of office being installed.

因此，我不想在导入excel文件时完全依靠office.当然，这不是主要功能，我可以将工作表名称存储为文本字段，但是如果可以选择相关的工作表名称，那就更好了.

So I want to not rely on office completely while importing an excel file. Of course this is not a major feature, I can store the worksheet name as a text field, but it would be much nicer if one could pick the relevant worksheet name.

这可以在vb6中完成吗?谢谢

Can this be done, and in vb6? Thanks

推荐答案

您可以使用普通ADO(而非ADOX)进行枚举

You can use plain ADO (not ADOX) to do the enumeration like this

Option Explicit

Private Sub Command1_Click()
    Dim vElem       As Variant

    For Each vElem In GetSheets("d:\temp\aaa.xlsx")
        Debug.Print vElem
    Next
End Sub

Private Function GetSheets(sFileName As String) As Collection
    Const adStateOpen As Long = 1
    Const adSchemaTables As Long = 20

    Set GetSheets = New Collection
    With CreateObject("ADODB.Connection")
        If LCase$(Right$(sFileName, 5)) = ".xlsx" Then
            .Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sFileName & ";Extended Properties=Excel 12.0 Xml"
        Else
            .Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sFileName & ";Extended Properties=Excel 8.0"
        End If
        If .State <> adStateOpen Then
            Exit Function
        End If
        With .OpenSchema(adSchemaTables)
            Do While Not .EOF
                If LCase$(!TABLE_NAME) <> "database" Then
                    GetSheets.Add !TABLE_NAME.Value
                End If
                .MoveNext
            Loop
        End With
    End With
End Function

这篇关于当未使用vb6安装Office时，遍历excel工作表名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！