Ekspor Txt ke Excel

Hallo-hallo, sudah lama ngga ngisi blog ini, semoga teman semua tidak pada bosen dan semoga tambah pinter pemrograman VBnya. Untuk posting kali ini saya mencoba memenuhi permintaan salah satu pengunjung mengenai peng-Eksporan data dari format .txt diekspor ke format excel.

Langsung saja yang dibutuhkan dalam pembuatan aplikasi ini adalah ListView untuk menampung data dari file data.txt, 1 Commandbutton untuk melihat dan sekaligus menyimpan file dalam format xls ataupun txt, dan combo box untuk menampung pilihan format yang ingin dilihat yaitu .txt atau .xls, Agar lebih jelas lagi lihat gambar di atas. Tanpa basa-basi silahkan dipelajari code-code dibawah ini.

Masukan code dibawah ini pada form

Option Explicit
Public Enum DataSiswa
Nama = 1
Kelas
JenisKelamin
NIS
Alamat
Tempatlahir
TanggalLahir
End Enum

Private Const SE_ERR_NOASSOC = 31
Private Declare Function timeGetTime Lib "winmm.dll" () As Long
Private Declare Function GetDesktopWindow Lib "user32" () As Long
Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hWnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
Private Declare Function GetSystemDirectory Lib "kernel32" Alias "GetSystemDirectoryA" (ByVal lpBuffer As String, ByVal nSize As Long) As Long


Private Sub LoadHeader()
On Error GoTo Salah

'mengeset columnheaders
With lvwDataSiswa

.ColumnHeaders.Add , "Nama", "Nama"
.ColumnHeaders.Add , "Kelas", "Kelas"
.ColumnHeaders.Add , "JenisKelamin", "JK"
.ColumnHeaders.Add , "NIS", "NIS"
.ColumnHeaders.Add , "Alamat", "Alamat"
.ColumnHeaders.Add , "Tempatlahir", "Lahir"
.ColumnHeaders.Add , "TanggalLahir", "Tanggal Lahir"


'Nama
.ColumnHeaders.Item(DataSiswa.Nama).Width = 2500
.ColumnHeaders.Item(DataSiswa.Nama).Alignment = lvwColumnLeft
'Kelas
.ColumnHeaders.Item(DataSiswa.Kelas).Width = 700
.ColumnHeaders.Item(DataSiswa.Kelas).Alignment = lvwColumnLeft
'JenisKelamin
.ColumnHeaders.Item(DataSiswa.JenisKelamin).Width = 500
.ColumnHeaders.Item(DataSiswa.JenisKelamin).Alignment = lvwColumnLeft
'NIS
.ColumnHeaders.Item(DataSiswa.NIS).Width = 700
.ColumnHeaders.Item(DataSiswa.NIS).Alignment = lvwColumnLeft
'Alamat
.ColumnHeaders.Item(DataSiswa.Alamat).Width = 2500
.ColumnHeaders.Item(DataSiswa.Alamat).Alignment = lvwColumnLeft
'Tempatlahir
.ColumnHeaders.Item(DataSiswa.Tempatlahir).Width = 1000
.ColumnHeaders.Item(DataSiswa.Tempatlahir).Alignment = lvwColumnLeft
'TanggalLahir
.ColumnHeaders.Item(DataSiswa.TanggalLahir).Width = 1200
.ColumnHeaders.Item(DataSiswa.TanggalLahir).Alignment = lvwColumnLeft
End With


Exit Sub
Salah:
MsgBox Err.Number & vbCrLf & Err.Description
End Sub

Private Sub CmdView_Click()

ShowItemList lvwDataSiswa, 100, "Data Siswa", , True, cboExt.Text

End Sub

Private Sub Form_Load()
LoadHeader
PopulateLvw
cboExt.ListIndex = 0
End Sub

Private Sub PopulateLvw()
On Error GoTo Salah
Dim Item As ListItem
Dim sData As String
Dim saryData() As String
Dim lCount As Long
Dim saryColData() As String
Dim lColPos As Long

sData = GetFileData(App.Path & "\Data.txt")

saryData() = Split(sData, vbCrLf)

'menghilangkan Header Name yang pertama pada data.txt
For lCount = LBound(saryData, 1) + 1 To UBound(saryData, 1)
If saryData(lCount) = vbNullString Then
Exit For
End If
saryColData() = Split(saryData(lCount), vbTab)

Set Item = lvwDataSiswa.ListItems.Add(, , saryColData(DataSiswa.Nama - 1))
'Kelas
Item.SubItems(DataSiswa.Kelas - 1) = saryColData(DataSiswa.Kelas - 1)
'JenisKelamin
Item.SubItems(DataSiswa.JenisKelamin - 1) = saryColData(DataSiswa.JenisKelamin - 1)
'NIS
Item.SubItems(DataSiswa.NIS - 1) = saryColData(DataSiswa.NIS - 1)
'Alamat
Item.SubItems(DataSiswa.Alamat - 1) = saryColData(DataSiswa.Alamat - 1)
'Tempatlahir
Item.SubItems(DataSiswa.Tempatlahir - 1) = saryColData(DataSiswa.Tempatlahir - 1)
'TanggalLahir
Item.SubItems(DataSiswa.TanggalLahir - 1) = saryColData(DataSiswa.TanggalLahir - 1)
Item.Selected = False
Next

Exit Sub
Salah:
MsgBox Err.Number & vbCrLf & Err.Description
End Sub

Private Sub ShowItemList(poLstView As Object, _
Optional plMaxColLen As Long = 100, _
Optional psOutPutName As String = vbNullString, _
Optional psOutPutPath As String = vbNullString, _
Optional pbUseTempPrefix As Boolean = False, _
Optional psExt As String)
On Error GoTo Salah
'Error
Dim lRet As Long
Dim lErrNum As Long
Dim sErrDesc As String
'File names
Dim sFileName As String
Dim sFullPathName As String
Dim sTempDir As String
Dim sExt As String
Dim bValidExt As Boolean
Dim bDelAppApthFile As Boolean
'Objects
Dim Item As ListItem
Dim oLstView As ListView
'Build Print Data
Dim lColPos As Long
Dim lFillLen As Long
Dim aryColMaxLen() As Long
Dim sHeader As String
Dim sData As String
Dim sTemp As String


'Set nama file menggunakan ekstensi .txt atau .xls
'hanya Support .txt dan .xls
If psExt = vbNullString Then
psExt = ".txt"
Else
sExt = psExt
End If

'mengecek validnya ekstensi
If StrComp(sExt, ".txt", vbTextCompare) = 0 Then
bValidExt = True
End If

If StrComp(sExt, ".xls", vbTextCompare) = 0 Then
bValidExt = True
End If

If Not bValidExt Then
Exit Sub
End If

'mengeset List View Object
Set oLstView = poLstView

If psOutPutName = vbNullString Then
sFileName = "Daftar Item" & sExt
Else
If pbUseTempPrefix Then
sFileName = psOutPutName & sExt
Else
sFileName = psOutPutName & sExt
End If
End If

'mengeset Output path
If psOutPutPath = vbNullString Then
sTempDir = App.Path & "\"
Else
sTempDir = psOutPutPath
End If

sFullPathName = sTempDir & sFileName

If Not utFileExists(sTempDir, True) Then
bDelAppApthFile = True
sTempDir = App.Path & "\"
End If

'menyusun Data
Screen.MousePointer = VBRUN.MousePointerConstants.vbHourglass

'1. menyusun Header
ReDim aryColMaxLen(1 To oLstView.ColumnHeaders.Count)
For lColPos = 1 To oLstView.ColumnHeaders.Count
If oLstView.ColumnHeaders(lColPos).Width > 0 Then
If StrComp(sExt, ".txt", vbTextCompare) = 0 Then
aryColMaxLen(lColPos) = GetMaxLenthForCol(oLstView, lColPos)
End If
sTemp = oLstView.ColumnHeaders(lColPos).Text
sTemp = "[" & sTemp & "]" 'wrap the col name
If StrComp(sExt, ".txt", vbTextCompare) = 0 Then
If aryColMaxLen(lColPos) < Len(sTemp) Then aryColMaxLen(lColPos) = Len(sTemp) End If lFillLen = aryColMaxLen(lColPos) lFillLen = (lFillLen - Len(sTemp)) If lFillLen > 0 Then
sTemp = sTemp & String(lFillLen, Chr(32))
End If
End If
'tambahkan ke header
sHeader = sHeader & sTemp & vbTab
End If
Next
If sHeader <> vbNullString Then
'menambahkan spasi pada header
sHeader = sHeader & vbCrLf
End If

'Set Header ke Data
sData = sHeader

'2. menyusun isi
For Each Item In oLstView.ListItems
For lColPos = 1 To oLstView.ColumnHeaders.Count
If oLstView.ColumnHeaders(lColPos).Width > 0 Then
If lColPos = 1 Then
sTemp = Item.Text
Else
sTemp = Item.ListSubItems(lColPos - 1).Text
End If
'dibutuhkan untuk membersihkan banyaknya enter pada data
'Replace with 2 spaces
sTemp = Replace(sTemp, vbCrLf, String(2, Chr(32)))
'tidak memiliki banyak extra tab,
sTemp = Replace(sTemp, vbTab, " ")
'tambah 3 account untuk "..."
If Len(sTemp) > (plMaxColLen + 3) Then
sTemp = Left(sTemp, plMaxColLen) & "..."
End If
'Hanya dibutuhkan untuk mendapatkan banyaknya Len pada format .txt
If StrComp(sExt, ".txt", vbTextCompare) = 0 Then
lFillLen = aryColMaxLen(lColPos)
lFillLen = lFillLen - Len(sTemp)
If lFillLen > 0 Then
sTemp = sTemp & String(lFillLen, Chr(32))
End If
End If
sData = sData & sTemp & vbTab
End If
Next
sData = sData & vbCrLf
Next


'Simpan ke temp directory
SaveFileData sFullPathName, sData

If utFileExists(sFullPathName) Then
lRet = utShellExecute(GetDesktopWindow, "OPEN", sFullPathName, vbNullString, App.Path, vbNormalFocus, False, False, True)
End If

Screen.MousePointer = VBRUN.MousePointerConstants.vbDefault

Set oLstView = Nothing
Set Item = Nothing
Exit Sub
Salah:
lErrNum = Err.Number
sErrDesc = Err.Description
Screen.MousePointer = VBRUN.MousePointerConstants.vbDefault
Err.Raise lErrNum, , sErrDesc & vbCrLf & "Private Sub ShowItemList"
End Sub

Private Function GetMaxLenthForCol(poLstView As Object, _
lColPos As Long, _
Optional plMaxColLen As Long = 100) As Long
On Error GoTo Salah
Dim lErrNum As Long
Dim sErrDesc As String
Dim Item As ListItem
Dim oLstView As ListView
Dim sTemp As String
Dim lThisLen As Long
Dim lLen As Long

Set oLstView = poLstView

For Each Item In oLstView.ListItems
If lColPos = 1 Then
sTemp = Item.Text
Else
sTemp = Item.ListSubItems(lColPos - 1).Text
End If
lThisLen = Len(sTemp)
If lThisLen > lLen Then
lLen = lThisLen
End If
Next

If lLen > plMaxColLen Then
' Tambahkan maksimal 3 Length untuk account "..."
lLen = plMaxColLen + 3
End If

GetMaxLenthForCol = lLen

Set Item = Nothing
Set oLstView = Nothing

Exit Function
Salah:
lErrNum = Err.Number
sErrDesc = Err.Description
Screen.MousePointer = VBRUN.MousePointerConstants.vbDefault
MsgBox lErrNum & vbCrLf & sErrDesc
End Function


Public Function utFileExists(strFile As String, Optional pbDirOnly As Boolean) As Boolean
On Error GoTo Salah
Dim FSO As Scripting.FileSystemObject

Set FSO = New Scripting.FileSystemObject


If strFile <> vbNullString Then
If Not pbDirOnly Then
utFileExists = FSO.FileExists(strFile)
Else
utFileExists = FSO.FolderExists(strFile)
End If
End If

Set FSO = Nothing

Exit Function
Salah:
Set FSO = Nothing
utFileExists = False
End Function

Public Sub SaveFileData(psFilePath As String, psFileData As String, Optional psDelimeter As String, Optional pbLock As Boolean = False, Optional piFFile As Integer)
On Error GoTo Salah
Dim lMyFileLen As Long
Dim iFFile As Integer
Dim lErrNum As Long
Dim sErrDesc As String


iFFile = FreeFile
piFFile = iFFile
Open psFilePath For Binary Access Write As #iFFile
Put #iFFile, 1, psFileData & psDelimeter
If Not pbLock Then
Close #iFFile
End If
Exit Sub
Salah:
lErrNum = Err.Number
sErrDesc = Err.Description
Close #iFFile
Err.Raise lErrNum, , App.EXEName & vbCrLf & "Public Sub SaveFileData" & vbCrLf & "Error # " & lErrNum & vbCrLf & sErrDesc & vbCrLf
End Sub

Public Function GetFileData(psFilePath As String, Optional pbLock As Boolean = False, Optional piFFile As Integer, Optional pbSkipMess As Boolean = True) As String
On Error GoTo Salah
Dim lMyFileLen As Long
Dim iFFile As Integer

iFFile = FreeFile
piFFile = iFFile
If pbLock Then
Open psFilePath For Binary Access Read Lock Read As #iFFile
Else
Open psFilePath For Binary Access Read As #iFFile
End If
lMyFileLen = FileLen(psFilePath) + 2
GetFileData = Input(lMyFileLen, #iFFile)
If Not pbLock Then
Close #iFFile
End If

Exit Function
Salah:
Close #iFFile
If Not pbSkipMess Then
If MsgBox("Tidak Dapat Membaca File... " & vbCrLf & psFilePath & vbCrLf & "(" & Err.Description & ")" & vbCrLf & vbCrLf & _
"Jaringan atau File Sedang Sibuk." & vbCrLf & "Tekan ""Yes"" untuk mencoba lagi." & vbCrLf & "Tekan ""No"" untuk menghentikan proses", vbYesNo, "File Sibuk") = vbYes Then
Resume
End If
End If

End Function

Public Function utShellExecute(Optional plHwnd As Long = -1, _
Optional pslpOperation As String = "OPEN", _
Optional pslpFile As String, _
Optional pslpParameters As String = vbNullString, _
Optional pslpDirectory As String = "App.Path", _
Optional plnShowCmd As VBA.VbAppWinStyle = vbNormalFocus, _
Optional pbUseTimeStampFileName As Boolean = False, _
Optional pbShowMessage As Boolean = False, _
Optional psTempFileCaption As String) As Boolean
On Error GoTo Salah
Dim lHwnd As Long
Dim slpOperation As String
Dim slpFile As String
Dim slpParameters As String
Dim slpDirectory As String
Dim lnShowCmd As VBA.VbAppWinStyle
Dim sErrorMess As String
Dim sTmpExt As String
Dim sTmpFile As String
Dim lRet As Long
Dim sDir As String
Dim lErrNum As Long
Dim sErrDesc As String

utShellExecute = False

'mendapatkan info dari Parameter
If plHwnd = -1 Then
lHwnd = GetDesktopWindow
End If
slpOperation = pslpOperation
If pslpFile = vbNullString Then
Exit Function
Else
slpFile = pslpFile
End If

slpParameters = pslpParameters
If pslpDirectory = "App.Path" Then
slpDirectory = App.Path
Else
slpDirectory = pslpDirectory
End If

lnShowCmd = plnShowCmd

'Jika file tdk ada kemudian keluar
If utFileExists(slpFile) Or InStr(1, slpFile, "MAPIMAIL", vbTextCompare) > 0 Then
sTmpFile = slpFile
lRet = ShellExecute(lHwnd, slpOperation, sTmpFile, slpParameters, slpDirectory, lnShowCmd)
If lRet = SE_ERR_NOASSOC Then
sDir = Space(260)
lRet = GetSystemDirectory(sDir, Len(sDir))
sDir = Left(sDir, lRet)
lRet = ShellExecute(lHwnd, vbNullString, "RUNDLL32.EXE", "shell32.dll,OpenAs_RunDLL " & sTmpFile, sDir, lnShowCmd)
End If
Else
SHOW_ERROR:
If pbShowMessage Then
If sErrorMess = vbNullString Then
sErrorMess = "File Tidak diketemukan!" & vbCrLf & psTempFileCaption & vbCrLf & slpFile
End If
MsgBox sErrorMess, vbExclamation + vbOKOnly, "File Error"
End If
End If
utShellExecute = True
Exit Function
Salah:
lErrNum = Err.Number
sErrDesc = Err.DescriptionErr.Raise lErrNum, , App.EXEName & vbCrLf & "Public Function utShellExecute" & vbCrLf & "Error # " & lErrNum & vbCrLf & sErrDesc & vbCrL

End Function

Selesai, Semoga bermanfaat.


sumber: http://vbasiccode.blogspot.com/2008/11/ekspor-txt-ke-excel.html