Excel VBA SQL查询未保存的工作簿

Question

我有一个Excel工作簿与一些宏。使用一个宏，我打开另一个现有工作簿(已经包含一些数据)并向其添加一些行。然后，使用另一个宏(ebergebene_wo)，对该工作簿进行SQL查询，但该查询仅查找磁盘上工作簿中的行。没有添加的行。如果在添加行后保存工作簿，则可以找到所有数据。当我将行添加到宏所在的工作簿时，我不需要先保存工作簿。我怎么做才能从外部工作簿中获取数据而不保存?

这些是宏的相关部分:

Dim conn As Object 'SQL-Connection
' Path to the files
Public Const strReportDir As String = "Q:ReportingKPIs"
' Name of the raw-data-file
Public Const strBewegRoh As String = "Bewegung Rohdaten.xlsx"
Function connect_sql(strWbPath As String)
'Connect to the Data Source
Set conn = CreateObject("ADODB.Connection")
With conn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source=" & strWbPath & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
.Open
End With
End Function
Sub uebergebene_wo()
' Fragt die Daten für Übergebenw WO ab.
Dim rs As Object 'RecordSet
Dim sql As String
Dim start_row As Integer: start_row = 1
Dim start_col As Integer: start_col = 1
Dim iCol As Integer
connect_sql (strReportDir & strBewegRoh) ' Verbindung herstellen

'write SQL
sql = "Select * from [WO-Bewegung Rohdaten$] Where `Datum Übergabe` BETWEEN 45021 AND 767010"

'run SQL
Set rs = conn.Execute(sql) 'delete data from previous runs in worksheet "Results" Worksheets("Results").Cells(start_row, start_col).CurrentRegion.Clear

Set wksBewegResults = Workbooks(strBewegRoh).Worksheets("Results")
'insert header row worksheet "Results" (only if HDR=YES)
For iCols = 0 To rs.Fields.Count - 1
wksBewegResults.Cells(start_row, start_col + iCols).Value = _
rs.Fields(iCols).Name
Next

'paste result of sql to worksheet "Results"
wksBewegResults.Cells(start_row + 1, start_col).CopyFromRecordset rs

'clean up and free memory
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
End Sub

Answer 1

我怎么做才能从外部工作簿中获取数据而不保存?

回答:可能没什么

然而，如果你的想法是获得这些工作簿中的信息，这可能是一个好主意，保存所有的Excel工作簿代码:

Sub SaveAllWorkbooks()
Dim wbk As Workbook
For Each wbk In Excel.Workbooks
wbk.Save
Next
End Sub

考虑在Sub uebergebene_wo()下面的行添加代码这里，像这样:

Sub uebergebene_wo()
SaveAllWorkbooks
' Fragt die Daten für Übergebenw WO ab.