无论如何，ADO在保存之前从只读Excel文件读取更新的数据？ （VBA） [英] Anyway for ADO to read updated data from a read-only excel file before save? (VBA)

问题描述

我正在使用以下代码从SAME Excel工作表Sheet1中读取数据。我将数据加载到返回数组中。 Excel表格文件已选中只读，并始终在只读模式下打开。

问题是，如果我更改Sheet1上的任何数据，因为该文件被打开为只读，它赢得了反映在ADO查询中。 ADO继续输出保存文件中的内容，并忽略temp只读版本中已更新的内容。
例如，以下从单元格E6中提取值Col5：6。如果我将值替换为test，ADO仍然输出Col5：6

如何使ADO读取Sheet1上的当前数据， 保存为？

  Sub sbADO（）
 Dim sSQLSting As String 
 Dim Conn As New ADODB.Connection 
 Dim mrs As New ADODB.Recordset 
 Dim DBPath As String，sconnect As String 
 Dim returnArray 
 
 DBPath = ThisWorkbook.FullName 
 sconnect =Provider = Microsoft.ACE.OLEDB.12.0; Data Source =& DBPath _ 
& ;扩展属性=Excel 12.0; HDR =是; IMEX = 1; 
 
 Conn.Open sconnect 
 sSQLSting =SELECT * From [Sheet1 $]
 
 mrs.Open sSQLSting，Conn 
 
 returnArray = mrs.GetRows 
 
 mrs.Close 
 Conn.Close 
 
 Debug.Print returnArray（4，4）'>> Col5：6
 
 End Sub 
  

解决方案>

由于未保存的数据位于内存（RAM和可能的交换文件）中，您无法从具有ADO的Excel工作表中读取未保存的更改，而ADO旨在连接到DB 文件或基于服务器的数据库。

如果您认为SQL是唯一的方法，而您的 WHERE 子句非常简单，那么您可以使用ADO Recordset内置功能，用于过滤和排序，而不进行连接。执行以下操作：

1. 以XML格式获取源范围的值，修复字段名称。


2. 创建XML DOM文档并加载XML字符串。


3. 创建ADO Recordset并转换文档。


4. 进行必要的过滤和排序。请注意，
   
   
   

   然后我得到结果 Sheet2 ：

   
   
   
   
   

   I am using the following code to read data from Sheet1 of SAME Excel sheet. I load the data into the return array. The Excel sheet file has "read only" checked and is always opened in "READ ONLY" mode.

   The issue is that if I change any of the data on Sheet1, because the file is opened as "read only", it won't be reflected in the ADO query. ADO Continues to output what is in the "saved" file and ignores what has been updated in the temp read only version. For example the below pulls value "Col5:6" from cell "E6". If I replace the value to be "test", ADO still outputs "Col5:6"

   How can I make ADO read the current data on Sheet1 without having to "save as"?

   Sub sbADO()
       Dim sSQLSting As String
       Dim Conn As New ADODB.Connection
       Dim mrs As New ADODB.Recordset
       Dim DBPath As String, sconnect As String
       Dim returnArray
   
       DBPath = ThisWorkbook.FullName
       sconnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBPath _
       & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"
   
       Conn.Open sconnect
       sSQLSting = "SELECT * From [Sheet1$] "
   
       mrs.Open sSQLSting, Conn
   
       returnArray = mrs.GetRows
   
       mrs.Close
       Conn.Close
   
       Debug.Print returnArray(4, 4) '>> "Col5:6"
   
   End Sub
   

   解决方案

   You can't read unsaved changes from Excel worksheet with ADO since the unsaved data is located in the memory (RAM, and probably swap file), and ADO designed to connect to DB files or server-based DBs.

   If you believe that SQL is the only way, and your WHERE clause is quite simple then you can use an ADO Recordset built in functionality for filtering and sorting, without making connection. Do the following:

   1. Get the value of the source range in XML format, fix field names.
   2. Create XML DOM Document and load the XML string.
   3. Create ADO Recordset and convert the document.
   4. Make necessary filtering and sorting. Note, there is some limitations on filter criteria syntax.
   5. Process the resulting recordset further, e. g. output to another worksheet.

   There is an example of the code:

   Option Explicit
   
   Sub FilterSortRecordset()
       Dim arrHead
       Dim strXML As String
       Dim i As Long
       Dim objXMLDoc As Object
       Dim objRecordSet As Object
       Dim arrRows
   
       ' get source in XML format
       With Sheets("Sheet1")
           arrHead = Application.Index(.Range("A1:G1").Value, 1, 0)
           strXML = .Range("A2:G92").Value(xlRangeValueMSPersistXML)
       End With
   
       ' fix field names
       For i = 1 To UBound(arrHead)
           strXML = Replace(strXML, "rs:name=""Field" & i & """", "rs:name=""" & arrHead(i) & """", 1)
       Next
   
       ' load source XML into XML DOM Document
       Set objXMLDoc = CreateObject("MSXML2.DOMDocument")
       objXMLDoc.LoadXML strXML
   
       ' convert the document to recordset
       Set objRecordSet = CreateObject("ADODB.Recordset")
       objRecordSet.Open objXMLDoc
   
       ' filtering and sorting
       objRecordSet.Filter = "City='London' OR City='Paris'"
       objRecordSet.Sort = "ContactName ASC"
   
       ' populate another sheet with resulting recordset
       arrRows = Application.Transpose(objRecordSet.GetRows)
       With Sheets("Sheet2")
           .Cells.Delete
           .Cells.NumberFormat = "@"
           For i = 1 To objRecordSet.Fields.Count
               .Cells(1, i).Value = objRecordSet.Fields(i - 1).Name
           Next
           .Cells(2, 1).Resize(UBound(arrRows, 1), UBound(arrRows, 2)).Value = arrRows
           .Columns.AutoFit
       End With
   End Sub
   

   The sourse data on Sheet1 is as follows:

   Then I got the result on Sheet2:

   这篇关于无论如何，ADO在保存之前从只读Excel文件读取更新的数据？ （VBA）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！