Excel VBA SQL Query on not saved Workbook

0 votes

I have a workbook with some macros in Excel. I open a different workbook that already exists (and has some data in it) with a single macro and add a few rows to it. I then perform a SQL query on that workbook using a different macro (uebergebene wo), however the query only returns rows that are physically present in the workbook on disc. Neither of the extra rows. After adding the rows, all the data is there if I save the workbook. I don't have to save the worksheet before adding the rows to the one where the macro is. What can I do to retrieve the data without saving it from the external workbook?

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
Apr 7, 2023 in Others by narikkadan
• 63,420 points
338 views

1 answer to this question.

0 votes

What can I do to get the data from the external workbook without saving it?

Ans: Probably nothing

The Excel workbooks should all be saved with the following code, nevertheless, if your plan is to retrieve the data in those workbooks.

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

Consider adding the code on the line below Sub uebergebene_wo() here, like this:

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

If you need to know more about SQL, it is recommended to go for the SQL Online Course today.

answered Apr 7, 2023 by Kithuzzz
• 38,010 points

Related Questions In Others

0 votes
1 answer

Excel VBA to change background image of shape by clicking on shape

You need to keep track of what ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 63,420 points
2,019 views
0 votes
1 answer

VBA, Query Vertica database and populate to excel

You can query Vertica directly from excel. I ...READ MORE

answered Sep 25, 2022 in Others by narikkadan
• 63,420 points
842 views
0 votes
1 answer
0 votes
1 answer

Excel Formula Query on Summing Annual Leave Days and Half Days

Try Something like =SUM(COUNTIF($A$1:$A$8,"A"),COUNTIF($A$1:$A$8,"HA")/2) - total count ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,420 points
1,323 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
876 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,183 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
480 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
731 views
0 votes
1 answer

Excel VBA code not running using F5 only F8

Change your code like this: Dim LastColumn As ...READ MORE

answered Mar 21, 2023 in Others by Kithuzzz
• 38,010 points
351 views
0 votes
1 answer

Excel VBA- How to loop through specific sheets in a workbook and format the same ranges in each sheet

Range(...) instructs VBA to always use the ...READ MORE

answered Mar 21, 2023 in Others by Kithuzzz
• 38,010 points
1,128 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP