将CSV文件与Excel VBA合并 [英] Combine CSV files with Excel VBA

问题描述

我在一个文件夹中有一些csv文件.它们都包含3个特定的列.总列数和顺序可能会有所不同.

I have some csv files in one folder. They all contain 3 specific columns. The number of total columns and the order may vary.

我想用下划线连接所有3列，并将它们写在运行代码的工作表的单列中.

I want to concatenate all 3 columns with an underscore and write them in a single column in the worksheet that is running the code.

这是我到目前为止所拥有的:

Here is what I have so far:

 Option Explicit

Sub test()

Dim i As Long
Dim LastRow As Long
Dim Columns()

Columns = Array("Column1", "Column2", "Column3")

'Find Columns by Name
For i = 0 To 2
    Columns(i) = Rows(1).Find(What:=Columns(i), LookIn:=xlValues, LookAt:=xlWhole, _
    MatchCase:=False, SearchFormat:=False).Column
Next i

'Debug.Print Columns(0)
'Debug.Print Columns(1)
'Debug.Print Columns(2)

LastRow = Cells(Rows.Count, "A").End(xlUp).Row


For i = 2 To LastRow
    Cells(i, 1) = Cells(i, Columns(0)) & "_" & Cells(i, Columns(1)) & "_" & Cells(i, Columns(2))
Next i

End Sub

如您所见，这满足了我的要求，但仅适用于活动工作表. 我实际上想循环遍历与活动工作表相同的文件夹中的所有csv文件，并将结果写入运行该代码的工作表的第一工作表，第一列(这显然不是csv本身). 我该怎么办?

As you can see, this does what I want, but only for the active sheet. I actually want to loop through all csv files in the same folder as the active sheet and write the results in the first sheet, first column of the sheet running the code (which is not a csv itself obviously). How can I do this?

谢谢！

推荐答案

这是将遍历文件夹的代码

This is a code that will loop through a folder

Sub Button1_Click()
    Dim MyFile As String, Str As String, MyDir As String, Wb As Workbook

    Set Wb = ThisWorkbook
    'change the address to suite
    MyDir = "C:\WorkBookLoop\"
    MyFile = Dir(MyDir & "*.xls")    'change file extension
    ChDir MyDir
    Application.ScreenUpdating = 0
    Application.DisplayAlerts = 0

    Do While MyFile <> ""
        Workbooks.Open (MyFile)

        'do something here




        MyFile = Dir()
    Loop

End Sub

这篇关于将CSV文件与Excel VBA合并的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！