hello guys i asked this question before but i just don't seem to find an answer can someone please tell me what i'm doing wrong in my code because even while i tried every methode on the internet no change is ever made on my excel file this my code for changing the color of a cell on an existing excel file
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim ws1 As Microsoft.Office.Interop.Excel.Worksheet
Dim xlApp As New Microsoft.Office.Interop.Excel.Application
ws1 = xlApp.Workbooks.Open(TextBox1.Text).Worksheets(ComboBox1.SelectedItem)
Dim rangeToFormat As Excel.Range
rangeToFormat = ws1.Cells(6, 5)
rangeToFormat.Interior.Color = ColorTranslator.ToOle(Color.Red)
End Sub
Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
Dim xlworkbook As Excel.Workbook
Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
Dim xlApp As New Microsoft.Office.Interop.Excel.Application
OpenFileDialog1.ShowDialog()
TextBox1.Text = OpenFileDialog1.FileName
xlworkbook = xlApp.Workbooks.Open(TextBox1.Text, 0, True)
For Each xlWorkSheet In xlApp.Sheets
ComboBox1.Items.Add(xlWorkSheet.Name)
Next
End Sub
my code asks the user to choose an excel file, choose the worksheet that wants to work on and then click on a button to change the color of the cell(6,5) and other cells that i didn't add here to make my code easier to undrestand. thank you sooo much guys on advanced.
CodePudding user response:
Try this:
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim ws1 As Microsoft.Office.Interop.Excel.Worksheet
Dim xlApp As New Microsoft.Office.Interop.Excel.Application
ws1 = xlApp.Workbooks.Open(TextBox1.Text).Worksheets(ComboBox1.SelectedItem)
Dim rangeToFormat As Excel.Range
rangeToFormat = ws1.Cells(6, 5)
rangeToFormat.Font.Color = RGB(255, 20, 20)
End Sub
Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
Dim xlworkbook As Excel.Workbook
Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
Dim xlApp As New Microsoft.Office.Interop.Excel.Application
OpenFileDialog1.ShowDialog()
TextBox1.Text = OpenFileDialog1.FileName
xlworkbook = xlApp.Workbooks.Open(TextBox1.Text, 0, True)
For Each xlWorkSheet In xlApp.Sheets
ComboBox1.Items.Add(xlWorkSheet.Name)
Next
End Sub
CodePudding user response:
for the one that are still looking for the answer i actually did find it you should just add after the color command:
xlWb.Save()
xlApp.Quit()
xlApp = Nothing