Menu utama

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim kosong As New Persiapan
kosong.Show()
End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim proses As New Form1
proses.Show()
End Sub

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
Dim export As New export
export.Show()
End Sub

Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
Dim importf As New import
importf.Show()
End Sub

Private Sub ToolBar1_ButtonClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.ToolBarButtonClickEventArgs) Handles ToolBar1.ButtonClick
If e.Button Is ToolBarButton1 Then
Dim cur As New Persiapan
cur.Show()
ElseIf e.Button Is ToolBarButton2 Then
Dim import As New import
import.Show()
ElseIf e.Button Is ToolBarButton3 Then
Dim prose As New Form1
prose.Show()
ElseIf e.Button Is ToolBarButton4 Then
Dim expot As New export
expot.Show()
Else
End
End If
End Sub

Private Sub MenuItem2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem2.Click
Dim kosong As New Persiapan
kosong.Show()
End Sub

Private Sub MenuItem3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem3.Click
Dim importf As New import
importf.Show()
End Sub

Private Sub MenuItem5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem5.Click
Dim proses As New Form1
proses.Show()
End Sub

Private Sub MenuItem7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem7.Click
Dim export As New export
export.Show()
End Sub

Private Sub MenuItem8_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem8.Click
End
End Sub

Private Sub Mainmenu_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim tgl As Date = System.DateTime.Now
StatusBar1.Panels(1).Text = CDate(tgl).ToShortDateString
StatusBar1.Panels(2).Text = CDate(tgl).ToLongTimeString
StatusBar1.Panels(0).Text = "CopyRifht ©Juli2008"
End Sub

Private Sub Label2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label2.Click
Dim kosong As New Persiapan
kosong.Show()
End Sub

Private Sub Label5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label5.Click
Dim importf As New import
importf.Show()
End Sub

Private Sub Label3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label3.Click
Dim proses As New Form1
proses.Show()
End Sub

Private Sub Label4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label4.Click
Dim export As New export
export.Show()
End Sub
End Class

Sebelum codeng tambahkan reference excel 11. dan office 11. object library ke project

Imports System.Data
Imports System.Data.OleDb
Imports System.IO

deklaresi-----------------------

Inherits System.Windows.Forms.Form
Dim mykonek As New acces.datacon

Menampilkan yang akan di export-------------------

#Region "tampil"
Sub tampil()
Try
objdatatable.Clear()
Dim strsql As String
strsql = "Select * from [export] order by [urut]"
objcom = New OleDbCommand(strsql)
objcom.Connection = mykonek.open
objdataadapter = New OleDbDataAdapter(objcom)
objdataadapter.Fill(objdatset, "export")
objdatatable = objdatset.Tables("export")
DataGrid1.DataSource = objdatatable
mykonek.close()
Catch ex As Exception
MsgBox("kenapa ya tidak Konek" & ex.Message & vbCrLf)
End Try
End Sub
#End Region

Export ke excel------------------------------------

#Region "export"
Sub export()
Dim objcom2 As New OleDbCommand("select * from [export] order by [urut]")
objcom2.Connection = mykonek.open
objdatreader = objcom2.ExecuteReader
objdatreader.Read()
sfd.ShowDialog()
Dim nama As String = sfd.FileName
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim tNode As TreeNode
Dim x, Add, z As Integer
Try
xlApp = CType(CreateObject("Excel.Application"), Excel.Application)
xlBook = CType(xlApp.Workbooks.Add, Excel.Workbook)
xlSheet = CType(xlBook.Worksheets(1), Excel.Worksheet)
xlSheet.Cells(1, 1) = CStr(objdatreader.GetName(0).ToString)
xlSheet.Cells(1, 2) = CStr(objdatreader.GetName(1).ToString)
xlSheet.Cells(1, 3) = CStr(objdatreader.GetName(2).ToString)
xlSheet.Cells(1, 4) = CStr(objdatreader.GetName(3).ToString)
xlSheet.Cells(1, 5) = CStr(objdatreader.GetName(5).ToString)
xlSheet.Cells(1, 6) = CStr(objdatreader.GetName(6).ToString)
xlSheet.Cells(1, 7) = CStr(objdatreader.GetName(7).ToString)
xlSheet.Cells(1, 8) = CStr(objdatreader.GetName(8).ToString)
xlSheet.Cells(1, 9) = CStr(objdatreader.GetName(10).ToString)
xlSheet.Cells(1, 10) = CStr(objdatreader.GetName(11).ToString)
xlSheet.Cells(1, 11) = CStr(objdatreader.GetName(12).ToString)
xlSheet.Cells(1, 12) = CStr(objdatreader.GetName(13).ToString)
xlSheet.Cells(1, 13) = CStr(objdatreader.GetName(14).ToString)
xlSheet.Cells(1, 14) = CStr(objdatreader.GetName(15).ToString)
'xlSheet.Cells(1, 15) = CStr(objdatreader.GetName(15).ToString)
'xlSheet.Cells(1, 16) = CStr(objdatreader.GetName(15).ToString)
mykonek.close()
'conversi()
objdatatable.Clear()
tampil()
For x = 0 To objdatatable.Rows.Count - 1
xlSheet.Cells(x + 2, 1) = CStr(objdatatable.Rows(x).Item(0))
xlSheet.Cells(x + 2, 2) = CStr(objdatatable.Rows(x).Item(1))
xlSheet.Cells(x + 2, 3) = CStr(objdatatable.Rows(x).Item(2))
xlSheet.Cells(x + 2, 4) = CStr(objdatatable.Rows(x).Item(3))
xlSheet.Cells(x + 2, 5) = CStr(objdatatable.Rows(x).Item(5))
xlSheet.Cells(x + 2, 6) = CStr(objdatatable.Rows(x).Item(6))
xlSheet.Cells(x + 2, 7) = CStr(objdatatable.Rows(x).Item(7))
xlSheet.Cells(x + 2, 8) = CStr(objdatatable.Rows(x).Item(8))
xlSheet.Cells(x + 2, 9) = CStr(objdatatable.Rows(x).Item(10))
xlSheet.Cells(x + 2, 10) = CStr(objdatatable.Rows(x).Item(11))
xlSheet.Cells(x + 2, 11) = CStr(objdatatable.Rows(x).Item(12))
xlSheet.Cells(x + 2, 12) = CStr(objdatatable.Rows(x).Item(13))
xlSheet.Cells(x + 2, 13) = CStr(objdatatable.Rows(x).Item(14))
xlSheet.Cells(x + 2, 14) = CStr(objdatatable.Rows(x).Item(15))
'xlSheet.Cells(x + 2, 15) = CStr(objdatatable.Rows(x).Item(14))
'xlSheet.Cells(x + 2, 16) = CStr(objdatatable.Rows(x).Item(15))
Next x
xlSheet.Application.Visible = True
xlSheet.SaveAs(nama)
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
#End Region

Button export diklik--------------------------------

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
export()
End Sub

Button view diklik---------------------------------

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
tampil()
End Sub

Button close-----------------------------------

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
Me.Close()
End Sub

Codeng untuk form proses :

Sebelumnya tambahkan reference office dan excelnya yang versi 11

Imports System.IO
Imports System.Data
Imports System.Data.OleDb

#Region "declare"
Dim br As DataRow
Dim strsql As String
Dim mykonek As New acces.datacon
Dim insert As New acces.datainsrt
Dim urut, no, lev, master, itemmaster, desc, Qty, uom, bkolom, tkolom, purchase, labor, machine, over, extra, total As String
#End Region

memecah kemudian menyimpan ke acces--------------

#Region "proses2"
Sub proses2()
Try
objdatatable.Clear()
Dim strsql As String
strsql = "SELECT urut,Left([item],4) AS lev, Mid([item],9,14) AS itemmaster, Mid([item],33,17) AS [desc],Mid([item],19,4) AS Qty,Mid([item],24,3) AS UOM,Mid([item],50,1) AS bkolom,Mid([item],52,1) AS tkolom,Mid([item],54,12) AS purchae,Mid([item],66,13) AS labour,Mid([item],79,13) AS machine,Mid([item],92,17) AS over,Mid([item],105,13) AS extra,Mid([item],118,14) AS total from [cost]"
' 0 1 2 3 4 5 6 7 8 9 10 11 12 13
'backup
'strsql = "SELECT Left([item],4) AS lev, Mid([item],9,14) AS itemmaster, Mid([item],33,17) AS [desc],Mid([item],21,23) AS Qty,Mid([item],24,32) AS UOM,Mid([item],51,53) AS bkolom,Mid([item],53,55) AS tkolom,Mid([item],56,68) AS purchae,Mid([item],69,81) AS labour,Mid([item],82,94) AS machine,Mid([item],95,107) AS over,Mid([item],108,120) AS extra,Mid([item],123,133) AS total from [cost]"
objcom = New OleDbCommand(strsql)
objcom.Connection = mykonek.open
objdataadapter = New OleDbDataAdapter(objcom)
objdataadapter.Fill(objdatset, "cost")
objdatatable = objdatset.Tables("cost")
'DataGrid1.DataSource = objdatatable
mykonek.close()
Catch ex As Exception
MsgBox("kenapa ya tidak Konek" & ex.Message & vbCrLf)
End Try
Try
Dim lev, itemmaster, desc, Qty, uom, bkolom, tkolom, purchase, labor, machine, over, extra, total As String
Dim i, jum As Integer
jum = objdatatable.Rows.Count - 1
For i = 0 To objdatatable.Rows.Count - 1
With objdatatable.Rows(i)
urut = Convert.ToString(.Item(0).ToString.Trim())
lev = .Item(1).ToString.Trim
itemmaster = .Item(2).ToString
desc = .Item(3).ToString()
bkolom = .Item(6).ToString()
tkolom = .Item(7).ToString()
purchase = .Item(8).ToString()
labor = .Item(9).ToString()
machine = .Item(10).ToString()
over = .Item(11).ToString()
extra = .Item(12).ToString()
total = .Item(13).ToString()
End With
'i = i + 1
With objdatatable.Rows(i + 1)
Qty = .Item(4).ToString()
uom = .Item(5).ToString()
End With
'insert.proses2(urut, lev, itemmaster, desc, bkolom, Qty, uom, tkolom, purchase, labor, machine, over, extra, total)
insert.proses2(urut, lev, itemmaster, desc, bkolom, Qty, uom, tkolom, purchase, labor, machine, over, extra, total)
Label5.Text = "Langkah 1 sukses"
ProgressBar1.Maximum = objdatatable.Rows.Count - 1
If i <= ProgressBar1.Maximum Then
ProgressBar1.Value = i
ElseIf ProgressBar1.Maximum >= objdatatable.Rows.Count - 1 Then
ProgressBar1.Dispose()
End If
Next i
Catch ex As Exception
MsgBox("gagal konek ke database" & ex.Message & vbCrLf)
End Try
End Sub
#End Region 'langkah kedua

hapus yang tidak perlu--------------------------

#Region "hapus"
Sub hapus()
Try
'---- 0 ** 3044 901 Acco Batc Cost Leve Mult Requ S I
Dim hapus1, hapus2, hapus3, hapus4, hapus5, hapus6, hapus7, hapus8, hapus9, hapus10, hapus11 As String
hapus1 = "----"
hapus2 = "0 **"
hapus3 = "3044"
hapus4 = "901"
hapus5 = "Acco"
hapus6 = "Batc"
hapus7 = "Cost"
hapus8 = "Leve"
hapus9 = "Mult"
hapus10 = "Requ"
hapus11 = "S I"
strsql = "delete from [backcoast] where ([level])='" & hapus1 & "' or ([level])= '" & hapus2 & "' or ([level])= '" & hapus3 & "' or ([level])= '" & hapus4 & "' or ([level])= '" & hapus5 & "' or ([level])= '" & hapus6 & "' or ([level])= '" & hapus7 & "' or ([level])= '" & hapus8 & "' or ([level])= '" & hapus9 & "' or ([level])= '" & hapus10 & "' or ([level])= '" & hapus11 & "' "
'strsql = "delete from [backcoast] where ([level])='" & hapus1 & "' or ([level])= '" & hapus2 & "' or ([level])= '" & hapus7 & "' "
objcom = New OleDbCommand(strsql)
objcom.Connection = mykonek.open
objcom.ExecuteNonQuery()
'DataGrid1.DataSource = objdatatable
mykonek.close()
Label5.Text = "langkah 2 sukses"
Catch ex As Exception
MsgBox("tidak dapat menghapus" & ex.Message & vbCrLf)
End Try
End Sub
#End Region 'langkah ketiga

Insert kolom baru dengan menggunakan formula------------------------

#Region "Proses3"
Sub masterdanno()
Try
objdatatable.Clear()
Dim strsql, nos, nos1, rmaster As String
Dim i, nom As Integer
strsql = "SELECT * from [backcoast]"
objcom = New OleDbCommand(strsql)
objcom.Connection = mykonek.open
objdataadapter = New OleDbDataAdapter(objcom)
objdataadapter.Fill(objdatset, "backcoast")
objdatatable = objdatset.Tables("backcoast")
For i = 0 To objdatatable.Rows.Count - 1
With objdatatable.Rows(i)
urut = .Item(0).ToString
nos = .Item(1).ToString.Trim
nos1 = CInt(.Item(1).Replace(".", ""))
no = (IIf(nos1 = 0, nos1, no + 1))
rmaster = .Item(2).ToString()
nom = no.ToString
lev = .Item(1).Replace(".", "")
If nos1 = 1 Then
master = .Item(2).ToString()
ElseIf nos1 = 2 Then
master = rmaster.Insert(0, "_")
ElseIf nos1 = 3 Then
master = rmaster.Insert(0, "__")
ElseIf nos1 = 4 Then
master = rmaster.Insert(0, "___")
ElseIf nos1 = 5 Then
master = rmaster.Insert(0, "____")
Else
master = .Item(2).ToString()
End If
itemmaster = .Item(2).ToString
desc = .Item(3).ToString()
Qty = .Item(5).ToString()
uom = .Item(6).ToString()
bkolom = .Item(4).ToString()
tkolom = .Item(7).ToString()
purchase = .Item(8).ToString()
labor = .Item(9).ToString()
machine = .Item(10).ToString()
over = .Item(11).ToString()
extra = .Item(12).ToString()
total = .Item(13).ToString()
End With
insert.proses3(urut, nom, lev, master, itemmaster, desc, bkolom, Qty, uom, tkolom, purchase, labor, machine, over, extra, total)
Label5.Text = "Total record #" & objdatatable.Rows.Count - 1
ProgressBar1.Maximum = objdatatable.Rows.Count - 1
If i <= ProgressBar1.Maximum Then
ProgressBar1.Value = i
ElseIf ProgressBar1.Maximum >= objdatatable.Rows.Count - 1 Then
ProgressBar1.Dispose()
End If
Next i
mykonek.close()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
#End Region 'langkah keempat

menampilkan hasil----------------------------

#Region "viewexport"
Sub viewexport()
objdatatable.Clear()
strsql = "SELECT * from [export] ORDER BY [urut]"
objcom = New OleDbCommand(strsql)
objcom.Connection = mykonek.open
objdataadapter = New OleDbDataAdapter(objcom)
objdataadapter.Fill(objdatset, "export")
objdatatable = objdatset.Tables("export")
DataGrid1.DataSource = objdatatable
mykonek.close()
End Sub
#End Region 'langkah kelima

Button proses diklik------------------------------

Private Sub btnproses_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnproses.Click
proses2()
hapus()
masterdanno()
viewexport()
End Sub

Button close klik------------------------------

Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Me.Close()
End Sub

Codeng pada form import

Public Class import
Inherits System.Windows.Forms.Form
Dim mykonek As New acces.datacon
Dim insert As New acces.datainsrt

Sub import()-------------------------
Try
Dim oFile As System.IO.File
Dim oRead As System.IO.StreamReader
Dim EntireFile, LineIn, tempfile As String
Dim pro As Integer = 0
If txtfile.Text.Trim = "" Then
MsgBox("Cari file terlebih dahulu", MsgBoxStyle.Information, "Pesan kesalahan")
Exit Sub
End If
oRead = oFile.OpenText(txtfile.Text.Trim)
While oRead.Peek <> -1
LineIn = oRead.ReadLine()
Dim level As String = LineIn.Substring(0, 4)
Dim item As String = LineIn.Trim
insert.insrt(level, item)
Label1.Text = oRead.Peek.MaxValue
pro = pro + 1
End While
oRead.Close()
MsgBox("Import data berhasil", MsgBoxStyle.Information, "Pesan keberhasilan")
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Critical)
mykonek.close()
End Try
End Sub

Button open file dialog-----------------------

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
OpenFileDialog1.Filter = "CSV (Comma delimited) (*.csv) *.csv Text Documents(*.txt) *.txt All Files (*.*) *.*"
OpenFileDialog1.FilterIndex = 0
OpenFileDialog1.FileName = ""
Dim result As DialogResult
OpenFileDialog1.ShowDialog()
txtfile.Text = OpenFileDialog1.FileName
End Sub

Button import---------------------------------

Private Sub btnimport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnimport.Click
import()
End Sub

Skenario:

1. File di ambil dari JDE berupa file dengan extensi .txt


2. mengosongkan table untuk menampung imports


3. File di import ke acces sebagai databasenya


4. File hasil import diolah


5. Hasil olahan di export ke excel

Coding langkah 2 dengan judul form pengosongan tabel

Imports System.Data
Imports System.Data.OleDb

#Region "declare"
Dim mykoneksi As New acces.datacon
Dim objcommand As OleDbCommand
Dim objdataadapter As OleDbDataAdapter
Dim objdatatable As New DataTable
Dim objdataset As New DataSet
Dim strsql As String
#End Region

#Region "import"
Sub hapusimport()
Try
objcommand = New OleDbCommand("delete from [cost]")
objcommand.Connection = mykoneksi.open
objcommand.ExecuteNonQuery()
mykoneksi.close()
'MsgBox("Hapus tabel hasil import dari JDE sukses")
Catch ex As Exception
MsgBox("koneksi ke database gagal" & ex.Message)
End Try
End Sub
#End Region

#Region "proses"
Sub hapusproses()
Try
objcommand = New OleDbCommand("delete from [backcoast]")
objcommand.Connection = mykoneksi.open
objcommand.ExecuteNonQuery()
mykoneksi.close()
'MsgBox("Hapus tabel Proses Data dari JDE sukses")
Catch ex As Exception
MsgBox("koneksi ke database gagal" & ex.Message)
End Try
End Sub
#End Region

#Region "export"
Sub hapusexport()
Try
objcommand = New OleDbCommand("delete from [export]")
objcommand.Connection = mykoneksi.open
objcommand.ExecuteNonQuery()
mykoneksi.close()
'MsgBox("Hapus tabel Export Ke Excel sukses")
Catch ex As Exception
MsgBox("koneksi ke database gagal" & ex.Message)
End Try
End Sub
#End Region

Button Kosongkan diklik

hapusimport()
hapusproses()
hapusexport()

Misal kita punya table dengan struktur

1. Kode type number
2. ket type text
3. Number type autonumber
4. hapus type text

misal kita ingin mengupdate ket dengan acuan field kode maka kita set terlebih dahulu nilai dari textbox kode menjadi integer,soalnya kalau masih bertype string maka akan terjadi error

Contoh codenya :

Diterima dari textbox dan kemudian dikirm dalam bentuk function

myinsert.update(txtkode.Text, txtket.Text, "F")

Code Function updatenya :

Public Function update(ByVal brand1 As String, ByVal ket As String, ByVal hapus As String)
Dim x As Integer = CInt(brand1)
Dim mykoneksi As New accesdata.dataconnection
Dim objcommand As New OleDbCommand("update [db_brand] set brandname='" & ket & "',hapus='" & hapus & "' where brandcode= " & x & " ")
objcommand.Connection = mykoneksi.open
objcommand.ExecuteNonQuery()
mykoneksi.close()
End Function

pada number tidak disertakan karena autonumber tidak dapat diupdate.

NB: untuk field bertipe date/time tidak perlu melakukan proses conversi terlebih dahulu,sehingga langsung dikirim