Public Sub LoadExcelSheet(ByVal sFile As String)
Dim excelApp As New Excel.Application
Dim excelBook As Excel.Workbook = excelApp.Workbooks.Open(Filename:=sFile)
Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
excelApp.Visible = False
With excelWorksheet
'Creates connections to pull data to DataSet
If txtAfdeling.Text <> "" Then
sqlSELECT = "SELECT Epapor.ordernr, dbo.Debitr.debzk, Epapor.orderregel, Epapor.artcode, Orsrg.oms45, Epapor.prod_order, Epapor.aant_ord, Epapor.aantal_grd, Orsrg.aant_gelev, Orsrg.aant_fakt, Epapor.einddat, Orsrg.afldat, dbo.Debitr.naam, dbo.Epapor.ord_status FROM Epapor FULL OUTER JOIN Artbst On Epapor.artcode = Artbst.artcode FULL OUTER JOIN Orsrg ON Epapor.prod_order = Orsrg.prod_order INNER JOIN Debitr ON Epapor.debnr = Debitr.debnr WHERE (dbo.Epapor.magcode='" + txtAfdeling.Text + "' AND dbo.Epapor.ord_status <> 'A'" + msSearchStatus + ")"
ElseIf txtKlant.Text <> "" Then
sqlSELECT = "SELECT Epapor.ordernr, dbo.Debitr.debzk, Epapor.orderregel, Epapor.artcode, Orsrg.oms45, Epapor.prod_order, Epapor.aant_ord, Epapor.aantal_grd, Orsrg.aant_gelev, Orsrg.aant_fakt, Epapor.einddat, Orsrg.afldat, dbo.Debitr.naam, dbo.Epapor.ord_status FROM Epapor FULL OUTER JOIN Artbst On Epapor.artcode = Artbst.artcode FULL OUTER JOIN Orsrg ON Epapor.prod_order = Orsrg.prod_order INNER JOIN Debitr ON Epapor.debnr = Debitr.debnr WHERE (dbo.Debitr.debzk = '" + txtKlant.Text + "' AND dbo.Epapor.ord_status <> 'A'" + msSearchStatus + ")"
ElseIf txtKlant.Text = "" And txtAfdeling.Text = "" And msStatus = "" And txtPO.Text = "" And txtartcode.Text = "" And txtordernr.Text = "" Then
sqlSELECT = "SELECT Epapor.ordernr, dbo.Debitr.debzk, Epapor.orderregel, Epapor.artcode, Orsrg.oms45, Epapor.prod_order, Epapor.aant_ord, Epapor.aantal_grd, Orsrg.aant_gelev, Orsrg.aant_fakt, Epapor.einddat, Orsrg.afldat, dbo.Debitr.naam, dbo.Epapor.ord_status FROM Epapor FULL OUTER JOIN Artbst On Epapor.artcode = Artbst.artcode FULL OUTER JOIN Orsrg ON Epapor.prod_order = Orsrg.prod_order INNER JOIN Debitr ON Epapor.debnr = Debitr.debnr WHERE (dbo.Epapor.ord_status <> 'A')"
ElseIf msStatus <> "" Then
sqlSELECT = "SELECT Epapor.ordernr, dbo.Debitr.debzk, Epapor.orderregel, Epapor.artcode, Orsrg.oms45, Epapor.prod_order, Epapor.aant_ord, Epapor.aantal_grd, Orsrg.aant_gelev, Orsrg.aant_fakt, Epapor.einddat, Orsrg.afldat, dbo.Debitr.naam, dbo.Epapor.ord_status FROM Epapor FULL OUTER JOIN Artbst On Epapor.artcode = Artbst.artcode FULL OUTER JOIN Orsrg ON Epapor.prod_order = Orsrg.prod_order INNER JOIN Debitr ON Epapor.debnr = Debitr.debnr WHERE (dbo.Epapor.ord_status = '" + msStatus + "')"
ElseIf txtPO.Text <> "" Then
sqlSELECT = "SELECT Epapor.ordernr, dbo.Debitr.debzk, Epapor.orderregel, Epapor.artcode, Orsrg.oms45, Epapor.prod_order, Epapor.aant_ord, Epapor.aantal_grd, Orsrg.aant_gelev, Orsrg.aant_fakt, Epapor.einddat, Orsrg.afldat, dbo.Debitr.naam, dbo.Epapor.ord_status FROM Epapor FULL OUTER JOIN Artbst On Epapor.artcode = Artbst.artcode FULL OUTER JOIN Orsrg ON Epapor.prod_order = Orsrg.prod_order INNER JOIN Debitr ON Epapor.debnr = Debitr.debnr WHERE (dbo.Epapor.prod_order = '" + txtPO.Text + "' AND dbo.Epapor.ord_status <> 'A'" + msSearchStatus + ")"
ElseIf txtartcode.Text <> "" Then
sqlSELECT = "SELECT Epapor.ordernr, dbo.Debitr.debzk, Epapor.orderregel, Epapor.artcode, Orsrg.oms45, Epapor.prod_order, Epapor.aant_ord, Epapor.aantal_grd, Orsrg.aant_gelev, Orsrg.aant_fakt, Epapor.einddat, Orsrg.afldat, dbo.Debitr.naam, dbo.Epapor.ord_status FROM Epapor FULL OUTER JOIN Artbst On Epapor.artcode = Artbst.artcode FULL OUTER JOIN Orsrg ON Epapor.prod_order = Orsrg.prod_order INNER JOIN Debitr ON Epapor.debnr = Debitr.debnr WHERE (dbo.Epapor.artcode = '" + txtartcode.Text + "' AND dbo.Epapor.ord_status <> 'A'" + msSearchStatus + ")"
ElseIf txtordernr.Text <> "" Then
sqlSELECT = "SELECT Epapor.ordernr, dbo.Debitr.debzk, Epapor.orderregel, Epapor.artcode, Orsrg.oms45, Epapor.prod_order, Epapor.aant_ord, Epapor.aantal_grd, Orsrg.aant_gelev, Orsrg.aant_fakt, Epapor.einddat, Orsrg.afldat, dbo.Debitr.naam, dbo.Epapor.ord_status FROM Epapor FULL OUTER JOIN Artbst On Epapor.artcode = Artbst.artcode FULL OUTER JOIN Orsrg ON Epapor.prod_order = Orsrg.prod_order INNER JOIN Debitr ON Epapor.debnr = Debitr.debnr WHERE (dbo.Epapor.ordernr = " + txtordernr.Text + " AND dbo.Epapor.ord_status <> 'A'" + msSearchStatus + ")"
End If
Dim ds As New DataSet("dtsNexans")
Dim dr As DataRow
Dim dtaNexans As New SqlClient.SqlDataAdapter
'Instellen
Dim cmdOpvragen As New SqlClient.SqlCommand(sqlSELECT, conDemival)
Dim i As Integer = 8
Try
cmdOpvragen.CommandType = CommandType.Text
dtaNexans.SelectCommand = cmdOpvragen
dtaNexans.SelectCommand.Connection = conDemival
dtaNexans.Fill(ds)
Catch ex As Exception
MsgBox(ex.Message + ex.StackTrace)
End Try
'Excel Data
.Range("B1").Value = txtAfdeling.Text
'Populate Excel spreadsheet
Try
For Each dr In ds.Tables(0).Rows
.Range("A" + i.ToString).Value = dr("ordernr")
.Range("B" + i.ToString).Value = dr("debzk")
.Range("C" + i.ToString).Value = dr("orderregel")
.Range("D" + i.ToString).Value = dr("artcode")
.Range("E" + i.ToString).Value = dr("oms45")
.Range("F" + i.ToString).Value = dr("prod_order")
.Range("G" + i.ToString).Value = dr("aant_ord")
.Range("H" + i.ToString).Value = dr("aantal_grd")
.Range("I" + i.ToString).Value = dr("aant_gelev")
.Range("J" + i.ToString).Value = dr("aant_fakt")
.Range("K" + i.ToString).Value = dr("einddat")
.Range("L" + i.ToString).Value = dr("afldat")
.Range("M" + i.ToString).Value = dr("naam")
.Range("N" + i.ToString).Value = dr("ord_status")
i += 1
Next
Catch ex As Exception
MsgBox(ex.Message + ex.StackTrace)
End Try
'Make Excel visible
excelApp.Visible = True
End With
End Sub
)