excel - VBScript 从 Excel、Automation 或 ADODB 中的单个单元格读取？

Question

不是编码员，第一次提出问题……所以请放轻松!在应用程序中使用 VBScript 进行空中图形。

我有两种方法可以从 Excel 电子表格中读取数据。我认为它们被称为自动化和 ADODB 连接。

我可以让两者都工作，但有一个问题:自动化速度较慢，但​​它可以轻松读取单个单元格。 ADODB 非常快，但我不能指向单个单元格，只能指向列。那有意义吗？

我希望能够使用 ADODB 来读取单个单元格。我疯了吗？

这是我的代码:

自动化(有效，但需要 10 秒):

Dim SelectedboxSpreadsheet

    'InputBox: ask for spreadsheet
    Dim shell : Set shell = CreateObject("Shell.Application")
    Dim boxSpreadsheet : Set boxSpreadsheet = shell.BrowseForFolder(0, "Select box Spreadsheet:", &H4000, "%SystemRoot%\explorer.exe")
    SelectedboxSpreadsheet = boxSpreadsheet.self.path

Dim objExcel
Dim currentWorksheet
Dim Cells

Sub OpenExcelSheet(SheetNumber)
    Set objExcel = CreateObject("Excel.Application")
    objExcel.Workbooks.open SelectedboxSpreadsheet, false, true
    Set currentWorksheet = objExcel.ActiveWorkbook.Worksheets(SheetNumber)
    Set Cells = currentWorksheet.Cells 
End Sub

Sub CloseExcelSheet()
    objExcel.Workbooks.Close
    objExcel.Quit
    Set currentWorkSheet = Nothing
    Set objExcel = Nothing
    Set Cells = Nothing
End Sub


Call OpenExcelSheet("box")

    TrioCmd("page:set_property H111 " & (Cells(2,2).Value))
    TrioCmd("page:set_property I111 " & (Cells(2,8).Value))
    TrioCmd("page:update")

Call CloseExcelSheet

和 ADODB(有效，但无法读取特定单元格，必须像表格一样格式化电子表格):

Dim SelectedSpreadsheetFromTxt

Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
Set file =fso.OpenTextFile("C:\ProgramData\vizrt\Trio\WorldCupDrawSpreadsheetPath.txt")
Do Until file.AtEndOfStream
line = file.ReadLine
Loop
file.Close

SelectedSpreadsheetFromTxt = line

dim objConnection, objRecordSet

set objConnection = CreateObject("ADODB.Connection")
set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open "Provider=Microsoft.ACE.OLEDB.12.0;" &_
"Data Source=" & SelectedSpreadsheetFromTxt & ";" &_
"Extended Properties=""Excel 12.0; HDR=Yes; IMEX=1"";"

dim A1name,A1tri,A2name,A2tri,A3name,A3tri,A4name,A4tri

objRecordSet.Open "SELECT * FROM [Trio Data Group A$]", objConnection

Do Until objRecordSet.EOF
   A1name = objRecordSet.Fields.Item("A1name")
   A1tri = objRecordSet.Fields.Item("A1tri")
   A2name = objRecordSet.Fields.Item("A2name")
   A2tri = objRecordSet.Fields.Item("A2tri")
   A3name = objRecordSet.Fields.Item("A3name")
   A3tri = objRecordSet.Fields.Item("A3tri")
   A4name = objRecordSet.Fields.Item("A4name")
   A4tri = objRecordSet.Fields.Item("A4tri")

   TrioCmd("page:set_property 0006 " & A1tri)
   TrioCmd("page:set_property 0007 " & A2tri)
   TrioCmd("page:set_property 0008 " & A3tri)
   TrioCmd("page:set_property 0009 " & A4tri)

   TrioCmd("page:set_property 0401 " & A1name)
   TrioCmd("page:set_property 0411 " & A2name)
   TrioCmd("page:set_property 0421 " & A3name)
   TrioCmd("page:set_property 0431 " & A4name)

objRecordSet.MoveNext
Loop

无论如何，这似乎是在黑暗中拍摄，但任何建议将不胜感激......谢谢!

Answer 1

你应该只使用自动化，因为即使它需要 10 秒，它也能更好地工作