Tutorial ini akan mendemonstrasikan cara membaca konten dari file teks dan menempelkannya ke lembar kerja dengan VBA
Baca Konten File Teks ke dalam Lembar Kerja
Cara paling sederhana untuk membaca konten file teks adalah dengan menyalinnya ke dalam sel lembar kerja
Sub FSOPasteTextFileContent() Dim FSO As New FileSystemObject Set FSO = CreateObject("Scripting.FileSystemObject") Set FileToRead = FSO.OpenTextFile("C:\Test\TestFile.txt", ForReading) 'add here the path of your text file TextString = FileToRead.ReadAll FileToRead.Close ThisWorkbook.Sheets(1).Range("A1").Value = TextString 'you can specify the worksheet and cell where to paste the text file’s content End SubKode di atas menggunakan FileSystemObject. Untuk menggunakannya, Anda perlu menyetel referensi ke pustaka run-time skrip VB. Lihat di sini untuk informasi lebih lanjut
Tanpa menggunakan FileSystemObject Anda dapat menempelkan konten file teks Anda dengan kode di bawah ini. Jika file teks Anda berisi pemisah baris, baris demi baris akan ditempelkan
Sub PasteTextFileContent () Dim wbExcel As Workbook, wbText As Workbook Dim wsExcel As Worksheet Set wbExcel = ThisWorkbook 'specify here which Excel file the text file’s content is to be pasted into Set wsExcel = wbExcel.Sheets(1) 'specify here which worksheet to use Set wbText = Workbooks.Open("C:\Test\TestFile.txt") 'add here the path of your text file wbText.Sheets(1).Cells.Copy wsExcel.Cells wbText.Close SaveChanges:=False End Sub _Baca Konten File Teks Baris demi Baris, Kolom demi Kolom
File teks Anda mungkin memiliki beberapa baris dan beberapa elemen yang tercantum dalam baris yang dipisahkan oleh koma, titik koma, tab, spasi, dll. Untuk membaca dan menempelkan konten file teks dengan benar, Anda mungkin memerlukan kode di bawah ini
Sub PasteTextFileContentWithSeparators() Dim StrLine As String Dim FSO As New FileSystemObject Dim TSO as Object Dim StrLineElements As Variant Dim Index As Long Dim i As Long Dim Delimiter as String Set FSO = CreateObject("Scripting.FileSystemObject") Set TSO = FSO.OpenTextFile("C:\Test\TestFile.txt") Delimiter=", " 'the delimiter that is used in your text file Index = 1 Do While TSO.AtEndOfStream = False StrLine = TSO.ReadLine StrLineElements = Split(StrLine, Delimiter) For i = LBound(StrLineElements) To UBound(StrLineElements) Cells(Index, i + 1).Value = StrLineElements(i) 'this code will start pasting the text file’s content from the active worksheet’s A1 (Cell(1,1)) cell Next i Index = Index + 1 Loop TSO.Close End SubPemisah yang digunakan dalam file teks Anda dapat berupa koma (“,”), koma dengan spasi (“, “), titik koma (“;”), titik koma dengan spasi (“; “), spasi (“ “), tab
Baca File Teks ke dalam Array
Jika Anda perlu membaca konten file teks Anda ke dalam array dan menempelkan baris demi baris, kolom demi kolom ke dalam lembar kerja Anda, Anda memerlukan kode ini di bawah ini
Sub ReadDelimitedTextFileIntoArray() Dim Delimiter As String Dim TextFile As Integer Dim FilePath As String Dim FileContent As String Dim LineArray() As String Dim DataArray() As String Dim TempArray() As String Dim rw As Long, col As Long Delimiter = vbTab 'the delimiter that is used in your text file FilePath = "C:\Test\TestFileTab.txt" rw = 1 TextFile = FreeFile Open FilePath For Input As TextFile FileContent = Input(LOF(TextFile), TextFile) Close TextFile LineArray() = Split(FileContent, vbNewLine) 'change vbNewLine to vbCrLf or vbLf depending on the line separator that is used in your text file For x = LBound(LineArray) To UBound(LineArray) If Len(Trim(LineArray(x))) <> 0 Then TempArray = Split(LineArray(x), Delimiter) col = UBound(TempArray) ReDim Preserve DataArray(col, rw) For y = LBound(TempArray) To UBound(TempArray) DataArray(y, rw) = TempArray(y) Cells(x + 1, y + 1).Value = DataArray(y, rw) 'this code will start pasting the text file’s content from the active worksheet’s A1 (Cell(1,1)) cell Next y End If rw = rw + 1 Next x End SubPemisah baris dalam file teks Anda dapat berupa kombinasi carriage return dan linefeed (Chr(13)+Chr(10)) atau linefeed (Chr(10)). Gunakan vbCrLf atau vbLf, sesuai. Jika Anda tidak yakin, gunakan vbNewLine untuk menunjukkan pemisah baris
Artikel ini mengilustrasikan cara mengimpor file teks ke Excel menggunakan kode VBA dengan 3 contoh yang sesuai. Kami akan menggunakan fungsi Split, InStr, dan Replace bawaan Excel untuk mengonfigurasi kode kami. Mari selami contoh untuk mengeksplorasi teknik yang dapat menyelesaikan pekerjaan Anda dengan cepat dan mudah
Daftar isi
Download Buku Latihan
Unduh buku latihan latihan ini untuk berolahraga saat Anda membaca artikel ini
Impor File Teks ke Excel. xlsm
3 Cara Mudah Mengimpor Data File Teks ke Lembar Kerja Excel Menggunakan VBA
Katakanlah kita memiliki kumpulan data dalam file teks yang dipisahkan koma. Datasetnya terlihat seperti ini-
Dataset mewakili detail penjualan untuk sebuah toko. Di sini, setiap data dipisahkan dengan koma. Kami ingin mengimpor data ini dari file teks ke spreadsheet Excel
Pengantar VBA Split dan Fungsi InStr
Kami akan menggunakan fungsi VBA Split dan InStr untuk mengonfigurasi kode VBA untuk mengimpor file teks ke lembar kerja Excel. Mari kita perkenalkan fungsinya terlebih dahulu
Fungsi Pemisahan VBA. Fungsi Split di Excel VBA digunakan untuk membagi string menjadi substring. Fungsi mengembalikan array satu dimensi berbasis nol. Setiap elemen array adalah substring yang dipisahkan oleh pembatas yang telah ditentukan sebelumnya. Sintaks fungsi VBA adalah-
Pisahkan(ekspresi, [pembatas, [batas, [bandingkan]]])
Di Sini,
ekspresi– Parameter wajib ini mewakili string teks yang berisi substring dan pembatas. Jika string kosong, fungsi juga akan mengembalikan array kosong
delimiter– Karakter string yang digunakan untuk membagi string menjadi substring. Jika dihilangkan, fungsi akan menggunakan karakter spasi sebagai pembatas. Dan jika itu adalah string kosong, itu akan mengembalikan string asli sebagai output
limit– Ini mewakili jumlah substring untuk dikembalikan dalam output. Jika dihilangkan, fungsi akan mengembalikan semua substring
bandingkan– Ini memiliki beberapa. Kita dapat menggunakan vbBinaryCompare untuk pembatas peka huruf besar kecil dan vbTextCompare untuk pembatas tidak peka huruf besar kecil dalam fungsi Split
Fungsi InStr VBA. Kami menggunakan fungsi InStr di Excel VBA untuk mencari string tertentu dalam string tertentu dari posisi yang telah ditentukan. Sintaksnya adalah-
InStr([mulai], string 1, string 2, [bandingkan])
Di Sini,
[mulai] - Posisi dari mana ia mulai mencari. Standarnya adalah 1 jika dihilangkan
string 1- String yang diberikan dari mana fungsi mencari string yang diinginkan
string 2- String spesifik yang dicari fungsi di dalam string yang diberikan
[bandingkan] - Jenis perbandingan. Standarnya adalah Perbandingan Biner
Tulis Kode di Editor Visual Basic
Untuk mengimpor file teks ke Excel, kita perlu membuka dan menulis kode VBA di editor visual basic. Ikuti langkah-langkah untuk membuka editor visual basic dan menulis beberapa kode di sana
- Buka tab Pengembang dari Pita Excel
- Klik opsi Visual Basic
- Di jendela Visual Basic For Applications, klik dropdown Insert untuk memilih Modul Baru
Sekarang letakkan kode Anda di dalam editor kode visual dan tekan F5 untuk menjalankannya
1. Kembangkan Kode VBA untuk Mengimpor File Teks ke Excel dengan Lokasi File Tertentu
Tugas. Impor data file teks ke lembar kerja aktif menggunakan kode VBA. Untuk ini, kita perlu menggunakan lokasi file teks tertentu dalam kode kita.
Solusi . Untuk mendapatkan lokasi file teks yang tepat, kita dapat melakukan ini-
- Pergi ke lokasi file teks
- Tekan tombol Shift dan klik kanan pada file teks
- Rekatkan lokasi di variabel textFileLocation dalam kode
Kode . Masukkan kode berikut di editor visual basic dan tekan F5 untuk menjalankannya.
Sub ImportTextFileToExcel() Dim textFileNum, rowNum, colNum As Integer Dim textFileLocation, textDelimiter, textData As String Dim tArray() As String Dim sArray() As String textFileLocation = "D:\Exceldemy\SampleData.txt" textDelimiter = "," textFileNum = FreeFile Open textFileLocation For Input As textFileNum textData = Input(LOF(textFileNum), textFileNum) Close textFileNum tArray() = Split(textData, vbLf) For rowNum = LBound(tArray) To UBound(tArray) - 1 If Len(Trim(tArray(rowNum))) <> 0 Then sArray = Split(tArray(rowNum), textDelimiter) For colNum = LBound(sArray) To UBound(sArray) ActiveSheet.Cells(rowNum + 1, colNum + 1) = sArray(colNum) Next colNum End If Next rowNum MsgBox "Data Imported Successfully", vbInformation End Sub_Kumpulan Data yang Diimpor
Baca Selengkapnya. Cara Mengimpor File Teks ke Excel Secara Otomatis (2 Cara yang Cocok)
Bacaan Serupa
- Excel VBA untuk Mengimpor File CSV tanpa Membuka (3 Contoh yang Cocok)
- Cara Mengimpor CSV ke Lembaran yang Ada di Excel (5 Metode)
- Excel VBA untuk Mengonversi File CSV ke XLSX (2 Contoh Mudah)
- Cara Membaca File CSV di Excel (4 Cara Tercepat)
- Buka File CSV di Excel Dengan Kolom Secara Otomatis (3 Metode)
2. Impor File Teks ke Excel dengan Opsi Pemilihan File Menggunakan VBA
Tugas . Impor data file teks ke lembar kerja aktif dengan opsi pemilihan file menggunakan kode VBA.
Solusi . Kita perlu menggunakan Aplikasi. Metode GetOpenFilename untuk membuka kotak dialog untuk memilih file teks yang kita inginkan. Itu akan mendapatkan nama file dan lokasi tanpa membukanya terlebih dahulu.
Kode . Masukkan kode berikut di editor visual basic dan tekan F5 untuk menjalankannya.
Sub ImportTextFileToExcel() Dim textFileNum, rowNum, colNum As Integer Dim textFileLocation, textDelimiter, textData As String Dim tArray() As String Dim sArray() As String textFileLocation = Application.GetOpenFilename() textDelimiter = "," textFileNum = FreeFile Open textFileLocation For Input As textFileNum textData = Input(LOF(textFileNum), textFileNum) Close textFileNum tArray() = Split(textData, vbLf) For rowNum = LBound(tArray) To UBound(tArray) - 1 If Len(Trim(tArray(rowNum))) <> 0 Then sArray = Split(tArray(rowNum), textDelimiter) For colNum = LBound(sArray) To UBound(sArray) ActiveSheet.Cells(rowNum + 1, colNum + 1) = sArray(colNum) Next colNum End If Next rowNum MsgBox "Data Imported Successfully", vbInformation End SubMenjalankan kode di atas akan membuka file explorer untuk memilih file teks
Kumpulan Data yang Diimpor
Baca Selengkapnya. VBA Excel. Membaca File Teks menjadi String (4 Kasus Efektif)
3. Impor Data File Teks dengan Banyak Pembatas ke Lembar Kerja Excel
Tugas . Impor file teks yang memiliki banyak pembatas ke lembar kerja Excel. Berikut adalah kumpulan data dengan koma dan titik koma sebagai pembatas di dalamnya.
Solusi . Dalam kode berikut, kami akan mengganti titik koma dari file teks dengan koma menggunakan fungsi Ganti VBA.
Kode . Masukkan kode berikut di editor visual basic dan tekan F5 untuk menjalankannya.
Sub ImportTextFileDatatoExcel() Dim fileLocation As String, textData As String Dim rowNum As Long folderLocation = "D:\Exceldemy" Set FSO = CreateObject("Scripting.FileSystemObject") Set folder = FSO.GetFolder(folderLocation) rowNum = 1 Close #1 For Each textFile In folder.Files fileLocation = folder & "\" & textFile.Name Open fileLocation For Input As #1 Do While Not EOF(1) Line Input #1, textData textData = Replace(textData, ";", ",") If InStr(textData, ",") = 0 Then Cells(rowNum, 1) = textData Else tArray = Split(textData, ",") nColumn = 1 For Each element In tArray Cells(rowNum, nColumn) = element nColumn = nColumn + 1 Next element End If rowNum = rowNum + 1 Loop Close #1 Next textFile End Sub_Kumpulan Data yang Diimpor
Baca Selengkapnya. VBA Excel. Impor File Teks yang Dipisahkan Koma (2 Kasus)
Hal-hal untuk diingat
- Kode yang kami gunakan dalam contoh ketiga dapat mengimpor banyak file teks ke lembar kerja Excel dari folder yang ditentukan
- Jika pembatas yang ditentukan tidak ada di string sumber, fungsi Split akan mengembalikan string apa adanya
- Jika argumen pembanding dari fungsi Split dihilangkan, nilai defaultnya adalah
Kesimpulan
Sekarang, kita tahu cara mengimpor file teks ke lembar kerja Excel menggunakan VBA di Excel dengan bantuan contoh yang sesuai. Mudah-mudahan, ini akan membantu Anda menggunakan fungsionalitas dengan lebih percaya diri. Setiap pertanyaan atau saran jangan lupa untuk memasukkannya ke dalam kotak komentar di bawah ini