Imports excel = Microsoft.Office.Interop.Excel
Public Class Form1
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Me.Tab1TableAdapter.Fill(Me.FTMESDataSet1.tab1)
Me.WIPLOTTableAdapter.Fill(Me.FTMESDataSet.WIPLOT)
End Sub
Private Sub import_Click(sender As Object, e As EventArgs) Handles import.Click
Dim ds As New SqlClient.SqlConnection
Dim dm As New SqlClient.SqlCommand
ds.ConnectionString = "Data Source=;Initial Catalog=;User ID=;Password=;"
dm.Connection = ds
dm.CommandText = "select * from WIPLOT where custproduct = '" & custpro.Text & "'"
Dim dataAdapter As New SqlClient.SqlDataAdapter
dataAdapter.SelectCommand = dm
Dim mydataset As New DataSet()
ds.Open()
dataAdapter.SelectCommand.ExecuteNonQuery()
dataAdapter.Fill(mydataset, "wiplot")
Dim appXL As excel.Application
Dim wbXl As excel.Workbook
Dim shXL As excel.Worksheet
Dim raXL As excel.Range
appXL = CreateObject("Excel.Application")
appXL.Visible = True
wbXl = appXL.Workbooks.Add
shXL = wbXl.ActiveSheet
shXL.Cells(1, 1).Value = "Device"
shXL.Cells(1, 2).Value = "Cust Lot No"
shXL.Cells(1, 3).Value = "WO NO"
shXL.Cells(1, 4).Value = "Vendor Lot NO."
shXL.Cells(1, 5).Value = "PKG Type"
shXL.Cells(1, 6).Value = "REC_DATE"
shXL.Cells(1, 7).Value = "Issue Date"
shXL.Cells(1, 8).Value = "Issue Qty"
shXL.Cells(1, 9).Value = "Date code"
shXL.Cells(1, 10).Value = "RCV"
shXL.Cells(1, 11).Value = "FT1"
shXL.Cells(1, 12).Value = "FT2"
shXL.Cells(1, 13).Value = "ReelCode-Trail"
shXL.Cells(1, 14).Value = "LOSS"
With shXL.Range("A1", "N1")
.Font.Bold = True
.VerticalAlignment = excel.XlVAlign.xlVAlignCenter
End With
Dim n As Integer = 0
For i = 2 To mydataset.Tables("wiplot").Rows.Count + 1
shXL.Cells(i, 1).value = mydataset.Tables("wiplot").Rows(n).Item("custproduct").ToString
n += 1
Next
n = 0
For i = 2 To mydataset.Tables("wiplot").Rows.Count + 1
shXL.Cells(i, 2).value = mydataset.Tables("wiplot").Rows(n).Item("custlotno").ToString
n += 1
Next
n = 0
For i = 2 To mydataset.Tables("wiplot").Rows.Count + 1
shXL.Cells(i, 3).value = mydataset.Tables("wiplot").Rows(n).Item("custorder").ToString
n += 1
Next
n = 0
For i = 2 To mydataset.Tables("wiplot").Rows.Count + 1
shXL.Cells(i, 4).value = mydataset.Tables("wiplot").Rows(n).Item("itestlotno").ToString
n += 1
Next
n = 0
For i = 2 To mydataset.Tables("wiplot").Rows.Count + 1
shXL.Cells(i, 5).value = mydataset.Tables("wiplot").Rows(n).Item("packageForm").ToString
n += 1
Next
n = 0
For i = 2 To mydataset.Tables("wiplot").Rows.Count + 1
shXL.Cells(i, 6).value = mydataset.Tables("wiplot").Rows(n).Item("ReceivingDate").ToString
n += 1
Next
n = 0
For i = 2 To mydataset.Tables("wiplot").Rows.Count + 1
shXL.Cells(i, 8).value = mydataset.Tables("wiplot").Rows(n).Item("incomenum").ToString
n += 1
Next
n = 0
For i = 2 To mydataset.Tables("wiplot").Rows.Count + 1
shXL.Cells(i, 9).value = mydataset.Tables("wiplot").Rows(n).Item("code").ToString
n += 1
Next
n = 0
For i = 2 To mydataset.Tables("wiplot").Rows.Count + 1
shXL.Cells(i, 13).value = mydataset.Tables("wiplot").Rows(n).Item("reelcode").ToString
n += 1
Next
For i = 0 To mydataset.Tables("wiplot").Rows.Count - 1
If mydataset.Tables("wiplot").Rows(i).Item("status").ToString = "CHECKIN" Then
shXL.Range(shXL.Cells(i + 2, 1), shXL.Cells(i + 2, 14)).Interior.ColorIndex = 4
End If
Next
shXL.Cells.HorizontalAlignment = 3
shXL.Cells.EntireColumn.AutoFit()
shXL.Cells.EntireRow.AutoFit()
appXL.Visible = True
appXL.UserControl = True
raXL = Nothing
shXL = Nothing
wbXl = Nothing
appXL.Quit()
appXL = Nothing
Exit Sub
Err_Handler:
MsgBox(Err.Description, vbCritical, "Error: " & Err.Number)
End Sub
End Class