时间戳

首页 > 解决方案 > 在添加数组之前检查文件扩展名是否为 .xlsx

excel - 在添加数组之前检查文件扩展名是否为 .xlsx

问题描述

我为我创建了一个循环来获取特定文件夹中的文件，但我想知道如何确保将添加到 myArray 中的文件名仅包含 .xlsx 或 .xls？

Function listFiles(ByVal get_Path As String)

Dim myArray As Variant
Dim i As Integer
Dim counter As Integer
Dim xFile As Object
Dim FileServ As Object
Dim the_Folder As Object
Dim the_Files As Object

Set FileServ = CreateObject("Scripting.FileSystemObject")
Set the_Folder = FileServ.GetFolder(get_Path)
Set the_Files = the_Folder.Files

    'Check if folder is empty
    If the_Files.Count = 0 Then
        Exit Function
    End If

    'Declare the size of Array based on the number of Files inside the folder
    ReDim myArray(1 To the_Files.Count)

    'Loop through each file and assign each filename in Array
    i = 1
    For Each xFile In the_Files
        myArray(i) = xFile.Name
        i = i + 1
    Next


    listFiles = myArray

End Function

标签： excelvba

解决方案

你可以这样做：

i = 0
For Each xFile In the_Files
    If xFile.Name Like "*.xls" or xFile.Name Like "*.xlsx" Then
        i = i + 1
        myArray(i) = xFile.Name
    End If
Next

Redim Preserve myArray(1 to i)

推荐阅读