Hi,
hier mal eine Demo, wie man das machen könnte:
Imports System.ComponentModel
Imports Microsoft.Office.Interop
Public Class Form1
Private WithEvents btn1 As New Button With {.Text = "Excel Datei öffnen", .Dock = DockStyle.Top}
Private WithEvents btn2 As New Button With {.Text = "3 beliebige Zellen", .Dock = DockStyle.Top}
Private WithEvents btn3 As New Button With {.Text = "3 Werte schreiben", .Dock = DockStyle.Top}
Private WithEvents btn4 As New Button With {.Text = "schließen und beenden", .Dock = DockStyle.Top}
Private tb1 As New TextBox With {.Dock = DockStyle.Top}
Private tb2 As New TextBox With {.Dock = DockStyle.Top}
Private tb3 As New TextBox With {.Dock = DockStyle.Top}
Private inpFile As String = "c:\temp\neue_daten.xlsx"
Private outFile As String = "c:\temp\test.xlsx"
Private xlApp1 As Excel.Application
Private xlApp2 As Excel.Application
Private xlWb1 As Excel.Workbook
Private xlWb2 As Excel.Workbook
Private rnd As New Random
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Me.Controls.AddRange(New Control() {tb3, tb2, tb1, btn4, btn3, btn2, btn1})
End Sub
Private Sub btn1_Click(sender As Object, e As EventArgs) Handles btn1.Click
Try
If xlApp1 Is Nothing Then xlApp1 = New Excel.Application
xlWb1 = xlApp1.Workbooks.Open(inpFile)
tb1.Text = GetCellValue(xlWb1)
tb2.Text = GetCellValue(xlWb1)
tb3.Text = GetCellValue(xlWb1)
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
Private Sub btn2_Click(sender As Object, e As EventArgs) Handles btn2.Click
If xlApp2 Is Nothing Then xlApp2 = New Excel.Application
If xlWb1 Is Nothing Then Exit Sub
xlWb2 = xlApp2.Workbooks.Add
SetCellValue(xlWb2, GetCellValue(xlWb1))
SetCellValue(xlWb2, GetCellValue(xlWb1))
SetCellValue(xlWb2, GetCellValue(xlWb1))
End Sub
Private Sub btn3_Click(sender As Object, e As EventArgs) Handles btn3.Click
If xlApp2 Is Nothing Then Exit Sub
If xlWb2 Is Nothing Then Exit Sub
SetCellValue(xlWb2, tb1.Text)
SetCellValue(xlWb2, tb2.Text)
SetCellValue(xlWb2, tb3.Text)
End Sub
Private Sub btn4_Click(sender As Object, e As EventArgs) Handles btn4.Click
SaveAndClose()
End Sub
Private Sub Form1_Closing(sender As Object, e As CancelEventArgs) Handles Me.Closing
SaveAndClose()
End Sub
Private Sub SaveAndClose()
If xlWb1 IsNot Nothing Then xlWb1.Close()
If xlApp1 IsNot Nothing Then xlApp1.Quit()
If xlWb2 IsNot Nothing Then
xlWb2.SaveAs(outFile)
xlWb2.Saved = True
xlWb2.Close()
End If
If xlApp2 IsNot Nothing Then xlApp2.Quit()
End Sub
Private Function GetCellValue(wb As Excel.Workbook) As String
Dim xlSheet As Excel.Worksheet = CType(wb.Worksheets(1), Excel.Worksheet)
Dim item As Excel.Range = CType(xlSheet.Cells.Item(rnd.Next(1, 10), rnd.Next(1, 10)), Excel.Range)
Return item.Value.ToString
End Function
Private Sub SetCellValue(wb As Excel.Workbook, value As String)
Dim xlSheet As Excel.Worksheet = CType(wb.Worksheets(1), Excel.Worksheet)
Dim item As Excel.Range = CType(xlSheet.Cells.Item(rnd.Next(1, 10), rnd.Next(1, 10)), Excel.Range)
item.Value = value
End Sub
End Class
--
Viele Grüsse
Peter Fleischer (ehem. MVP)
Meine Homepage mit Tipps und Tricks
