vba - 如何打开文件夹中的所有文件(xls 和 csv 格式文件) - VBA

Question

我编写了将数据从多个工作簿合并到一个工作簿的代码，并且代码仅打开 xls 格式文件，但某些文件在文件夹中具有 csv 格式。如何打开文件夹中的csv和xls文件？任何建议将不胜感激

Option Explicit

Sub ImportGroups()
Dim fPATH As String, fNAME As String
Dim LR As Long, LastRow As Long
Dim wb2, wb1 As Workbook, ofs As Worksheet

Set ofs = ThisWorkbook.Sheets("Sheet3")


fPATH = "C:\Users\ashokkumar.d\Desktop\MFI\"       

fNAME = Dir(fPATH & "*.xls")        'get the first filename in fpath

Do While Len(fNAME) > 0
    Set wb1 = Workbooks.Open(fPATH & fNAME)   'open the file

    LastRow = ofs.Range("B" & Rows.Count).End(xlUp).Row
    ofs.Range("B" & LastRow).Offset(1, -1).Value = fNAME

    Sheets("Input").Range("C8:J12").Copy
    ofs.Range("B" & LastRow).Offset(1, 0).PasteSpecial xlPasteValues



    wb1.Close False   'close data workbook
        fNAME = Dir         'get the next filename
Loop


 LR = ofs.Range("C" & Rows.Count).End(xlUp).Row
 ofs.Range("E2:I" & LR).Select
 Selection.NumberFormat = "0.00%"
 Application.ScreenUpdating = True
 ofs.Range("A1:Z" & LR).Select
 With Selection
    WrapText = True
    End With

End Sub

Answer 1

像这样:

fNAME = Dir(fPATH & "*")        'get the first filename in fpath
Do While Len(fNAME) > 0
    dim ext as string, p as integer
    p = inStrRev(fName, ".")
    ext = ucase(mid(fName, p+1))
    if ext = "CSV" or ext = "XLS" or ext = "XLSX" or ext = "XLST" then
        Set wb1 = Workbooks.Open(fPATH & fNAME)   'open the file
        ...
    end if