Excel VBA SQL Query on not saved Workbook

I have an Excel workbook with some macros. With one macro I open another existing workbook (which already contains some data) and add some rows to it. Then, using another macro (uebergebene_wo), I make a SQL query on that workbook, but the query only finds rows that are in the workbook on disk. None of the added rows. If I save the workbook after adding the rows, all the data is found.
When I add the rows to the workbook that the macro is in, I don't need to save the workbook first.
What can I do to get the data from the external workbook without saving?

These are the relevant parts of the macro:

Dim conn As Object 'SQL-Connection
' Path to the files
Public Const strReportDir As String = "Q:\Reporting\KPIs\"
' 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


</details>


# 答案1
**得分**: 1

*如何在不保存的情况下获取外部工作簿中的数据？*

*答：可能无法*

但是，如果您的想法是获取这些工作簿中的信息，可能需要使用以下代码保存所有的Excel工作簿：

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

考虑将此代码添加到Sub uebergebene_wo()下面的行中，就像这样：

Sub uebergebene_wo()
SaveAllWorkbooks
' 获取传递的WO的数据。

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

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