(Solved) – Create query in Inserting data from Excel Userform to another Excel database using VBA


I am using excel workbook as a database (ExcelDB) and connecting to it via ADODB. In a different excel workbook, I have a userform that I am using for data entry. What I am trying to do is to have the value of a textbox and insert it into the column, FirstHeader, in the table in one of the worksheets (WorkSheet1) in ExcelDB. I hope further that any other entry in that textbox will be added to the last row of that table when I click the commandbutton. I have tried most of the way insert query is made but I can’t make it work. Here is my code.

Private Sub btn_Save_Click()
 On Error Resume Next
 Public cnn As New ADODB.Connection
 Public rst As New ADODB.Recordset

cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:Reports.xlsm;Extended Properties=""Excel 12.0 Macro;HDR=YES;FMT=Delimited;IMEX=1;"";"

Dim qry As String

qry = "Select * from [WorkSheet1$]"

rst.Open qry, cnn, adOpenDynamic, adLockOptimistic '?:adOpenKeyset, adLockPessimistic - I dont know if have this code wrong...

If rst.RecordCount = 0 Then
    MsgBox "no record"
    qry = "Insert into [WorkSheet1$]([FirstHeader]) Values (TextBox.Value)"  '... or I have this query wrong

End If

Application.ScreenUpdating = True
Set rst = Nothing
Set cnt = Nothing

 End Sub

What is the proper way to write a query so that the input entered in the textbox will be inserted to the lastrow of the table in the worksheet I connected via adodb in excel? Thanks for the help.

Leave a Reply

Your email address will not be published. Required fields are marked *