Cara import multiple text file ke excel menggunakan vba

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 Sub

Kode 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 Sub

Pemisah 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 Sub

Pemisah 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 Sub

Menjalankan 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

Bisakah Anda mengimpor banyak file teks ke Excel sekaligus?

Bagaimana Anda mengimpor banyak file di Excel VBA?

Bagaimana cara mengimpor banyak file teks ke dalam satu lembar kerja di Excel?

Bagaimana cara mengimpor banyak file teks ke dalam beberapa lembar?