Search code examples
excelvbauserform

VBA Query works going step by step but skips when running

I have a simple Userform that allows the user to select 1 of two options through optionbuttons. The WHERE clause of the SQL statement is different for both options.

Once the correct option button is selected and the submit button is pressed it copies the information in to the active workbook, there it places all the results in a single cell (by setting the value of column a to be the value of all other columns mashed together and emptying all the other columns) for each row and then saves it as a new .CSV file.

When i run it the first time it goes as plan, when i change the selected optionbutton on the other hand it it still uses the old query result instead the one with the new WHERE. (everything else does work accordingly).

I tried running it step by step though the F8 key, and then it works. One of the results is about 120 rows the other 50. From what i can tell it goes wrong somewhere between setting the new String value of the query and copying the results to the excel sheet seeing the Query itself has been modified correctly, but the data on the sheet remains unchanged. 

If Optionbutton1 = True Then
Value = A
ElseIf optionbutton2= True Then
Value = b
End If


    Dim TDMConnection As ADODB.Connection
    Dim QueryResult As ADODB.Recordset
    Dim QueryField As ADODB.Field

    'Variabelen voor het samenvoegen van velden.
    Dim Rows As Range, RowNumber As Range


Set TDMConnection = New ADODB.Connection
Set QueryResult = New ADODB.Recordset
TDMConnection.ConnectionString = ConnectionString

TDMConnection.Open
On Error GoTo CloseConnection


Dim QuerySelect As String

QuerySelect = " SELECT      XXXX " & _
              " FROM        XXXX " & _
              " WHERE       XXXX= " & Value & " "


With QueryResult
    .ActiveConnection = TDMConnection
    .Source = QuerySelect
    .LockType = adLockReadOnly
    .CursorType = adOpenForwardOnly
    .Open
End With


On Error GoTo closeRecordSet


Sheets("Sheet1").UsedRange.ClearContents

Range("A1").CopyFromRecordset QueryResult

Range("A1", Range("I1").End(xlDown)).Sort Key1:=Range("B1"), Order1:=xlAscending, Key2:=Range("I1"), Order2:=xlAscending, Key3:=Range("H1"), Order3:=xlAscending, Header:=xlNo

Set Rows = Range("A1", Range("I1").End(xlDown))
For Each regelnummer In Rows .Rows
    Range("A" & RowNumber .Row).Value = Range("A" & RowNumber .Row).Value + ";" + Range("B" & RowNumber .Row).Value + ";" + And so on until I
    Range("B" & RowNumber .Row).Value = ""
    Range("C" & RowNumber .Row).Value = ""
    Range("D" & RowNumber .Row).Value = ""
    Range("E" & RowNumber .Row).Value = ""
    Range("F" & RowNumber .Row).Value = ""
    Range("G" & RowNumber .Row).Value = ""
    Range("H" & RowNumber .Row).Value = ""
    Range("I" & RowNumber .Row).Value = ""
Next
  Range("A1").CurrentRegion.EntireColumn.AutoFit


closeRecordSet:

QueryResult.Close
Set QueryResult = Nothing
CloseConnection:
    TDMConnection.Close

ThisWorkbook.Worksheets("Sheet1").Copy
ActiveWorkbook.SaveAs Filename:="C:\temp\" & Value & ".csv", FileFormat:=xlCSVWindows
End Sub

EDIT: It seems that the active workbook changes to the newly made .CSV file and does not revert back when run automatically

Solution

  • It seems that when i made the .CSV file it turned that file to the active workbook, When running step by step it reverts to the real active workbook by itself but when running normally i had to add Workbooks("WorkbookName.XLSM").Activate just before copying the data to the correct sheet.