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" Else qry = "Insert into [WorkSheet1$]([FirstHeader]) Values (TextBox.Value)" '... or I have this query wrong End If Application.ScreenUpdating = True rst.Close cnn.Close 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.