Sering kali waktu kita mengkonversi file report/Print Image ke Excel terjadi kesalahan sehingga menghasilkan GL yang tidak balance misalnya, hal ini diakibatkan kesalahan dalam memotong fixed width text report. Capek sekali kalau kita mesti mengulang lagi dari awal, oleh karena itu kita bisa gunakan VBA untuk merekam langkah2 dalam mengconversi Report Text tadi.
Kita gunakan data : gl tahun 2010_part.txt
ini tampilannya
Langkah2nya :
- Buat workbook baru
- Klik Record macro di pojok kiri bawah Excel
- Jika tombol itu tidak ada maka developer ribbon harus ditampilkan dulu, caranya baca disini
- Ini penampilan Record macro jika kita klik tombol record
- Kita isi nama dan keterangan sesuai dengan selera kita
- Kita simpan di Personal Macro workbook biar tidak gabung dengan file hasil konversi kita dan agar makro tsb tetap muncul kapan saja kita butuhkan
- Lalu kita mulai konversi
- Kita gunakan Ribbon Data --> From Text
- lalu lakukan seperti biasa, kita potong2 (ingat kalau text adalah rata kiri sedang number adalah rata kanan)
- lalu kita atur type datanya, untuk tanggal type : Date, format DMY, lalu semuanya adalah text kecuali debet, kredit, dan saldo
- ini tampilannya :
- Klik Finish lalu kita pilih New Worksheet untuk tempat data hasil konversinya
- Ini hasilnya
- Jika sudah lalu kita matikan record macronya
- Kita klik kotak diatas hingga berubah menjadi
- Kita akan lihat macro yang sudah kita rekam tadi
- Kita Klik Ribbon Developer -->Visual Basic
- atau shortcutnya ALT+F11
- Itu adalah kode2 yang diciptakan excel sebagai representasi atas langkah2 yang telah kita lakukan saat perekaman
- Untuk menjalankannya kita klik Ribbon Developer -->Macros
- Pilih nama macro kita
- lalu klik Run
- Ini hasilnya, akan dihasilkan dalam jangka waktu 2 detik di PC saya
- Bayangkan lamamya jika kita harus mengulang lagi secara manual
- Bagaimana jika terjadi kesalahan, misalnya salah potong atau salah dalam memilih type data?
- Kita ubah saja di macronya,(Klik ALT+F11, lalu pilih module1 pada Personal.xlsb untuk melihat kode), misalnya untuk mengubah lebar kolom kita lihat di baris yang berisi
- .TextFileFixedColumnWidths = Array(13, 16, 44, 19, 19)
- disini terdiri dari 5 bagian karena untuk kolom terakhir tidak perlu lagi karena merupakan sisa panjang ke kanan
- kalau kita mau ubah menjadi panjang 20 maka kita ubah menjadi
- .TextFileFixedColumnWidths = Array(13, 16, 44, 20, 20)
- sedang jika kita ingin mengubah type data maka kita ubah baris yang berisi
- .TextFileColumnDataTypes = Array(4, 2, 2, 1, 1, 1)
- disini type data adalah sbb
-
Constant Description xlGeneralFormat=1 General xlTextFormat=2 Text xlSkipColumn=3 Skip column xlDMYFormat=4 Day-Month-Year date format xlDYMFormat=5 Day-Year-Month date format xlEMDFormat=6 EMD date xlMDYFormat=7 Month-Day-Year date format xlMYDFormat=8 Month-Year-Day date format xlYDMFormat=9 Year-Day-Month date format xlYMDFormat=10 Year-Month-Day date format - Jadi kalau mau kita ubah type tanggal menjadi text kita ganti menjadi
- .TextFileColumnDataTypes = Array(2, 2, 2, 1, 1, 1)
- Kalau misalnya kita menggunakan format bahasa indonesia dimana pemisah ribuan adalah titik dan desimal adalah koma
- kita tambahkan di baris paling bawah sebelum baris End Sub
- .TextFileDecimalSeparator = ","
- .TextFileThousandsSeparator = "."
- namun dalam kasus ini format adalah bahasa inggris (default sehingga boleh tidak perlu ditambahkan, atau kita tambahkan juga boleh untuk contoh)
- Lengkapnya adalah sbb :
-
Sub GL_2010_part() ' ' GL_2010_part Macro ' Menconvert Report File GL_2010_part ke excel ' ' ActiveWorkbook.Worksheets.Add With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;E:\Module Eauditor\Module Visual Eauditor\Module Visual TABK\Data\GL 2baris\gl tahun 2010_part.txt" _ , Destination:=Range("$A$1")) .Name = "gl tahun 2010_part" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 850 .TextFileStartRow = 1 .TextFileParseType = xlFixedWidth .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(4, 2, 2, 1, 1, 1) .TextFileFixedColumnWidths = Array(13, 16, 44, 19, 19) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False 'kita tambahkan untuk format number bahasa inggris .TextFileDecimalSeparator = "." .TextFileThousandsSeparator = "," End With End Sub
Mantap Om Bayu …. top markotop
Pingback: Conversi Report file/Print Image yang memiliki field deskripsi lebih dari satu baris