Hi i have a dropdown box with a datasource from an excel file.I fill my dropdown box with that code
System.Windows.Forms.Application.EnableVisualStyles()
InitializeComponent()
Dim a As New OleDbConnectionStringBuilder With
{.Provider = "Microsoft.ACE.OLEDB.12.0",
.DataSource = IO.Path.Combine(My.Settings.CargoData, "CargoData.xlsx")
}
a.Add("Extended Properties", "Excel 12.0; HDR=Yes;")
Dim SheetName_2 As String = "CargoData"
Dim dt_2 As New DataTable
Using cn_2 As New OleDbConnection With
{
.ConnectionString = a.ConnectionString
}
cn_2.Open()
Dim cmd_2 As OleDbCommand = New OleDbCommand(
<Text>
SELECT
Type
FROM [<%= SheetName_2 %>$] order by Type
</Text>.Value,
cn_2
)
Cargodt.Load(cmd_2.ExecuteReader)
End Using
that code is in another form from my dropdown box.It's in frmMain. In dropdown form frmCargo i have this code
Private Sub frmCargo_Load(sender As Object, e As EventArgs) Handles Me.Load
drpCargoType.DisplayMember = "Type"
drpCargoType.DataSource = Cargodt
End Sub
Private Sub txtCargoType_KeyDown(sender As Object, e As KeyEventArgs) Handles txtCargoType.KeyDown
If e.KeyCode = Keys.Enter Then
Dim objExcel As New Excel.Application
Dim objWorkbook As Excel.Workbook
Dim objWorksheet As Excel.Worksheet
objWorkbook = objExcel.Workbooks.Add
objWorkbook = objExcel.Workbooks.Open("C:\Position Reports SBLK\Cargo Data\CargoData.xlsx")
objWorksheet = CType(objWorkbook.Worksheets.Item("CargoData"), Excel.Worksheet)
Dim lastRow As Long
lastRow = objWorksheet.Range("A" & objExcel.Rows.CountLarge).End(Excel.XlDirection.xlUp).Row + 1
With objWorksheet
.Range("A" & lastRow).Value = txtCargoType.Text
End With
objWorkbook.Save()
objWorkbook.Close(False)
objExcel.Quit()
txtCargoType.Hide()
drpCargoType.Show()
ElseIf e.KeyCode = Keys.Escape Then
txtCargoType.Hide()
drpCargoType.Show()
End If
End Sub
The above code shows a textbox to add value in my worksheet,that works great,but i want at the same time to update the dropbox with the new added value.Please help.Also i have a code with a button that calls that textbox but i think its irrelevant