vba - 从每个字符串数组元素中删除子字符串

Question

我将文件夹中的文件名存储到数组中。然后，我尝试从文件名中删除字符串的“.xlsx”部分，并将它们打印到电子表格中。我很难从每个数组元素中删除“.xlsx”子字符串。我相信替换功能是最好的，但还没有成功。困惑的区域由 'HERE 注释指示

Sub Example()

    Dim FName As String
    'Array to store filenames.
    Dim arNames() As String
    Dim myCount As Integer
    Dim i As Integer

    FName = Dir("G:\ExampleFolder\*.xls*")
    ' Run until there are no more filenames.
    Do Until FName = ""
        'Increment
        myCount = myCount + 1
        'Actively store filenames into an array.
        ReDim Preserve arNames(1 To myCount)
        arNames(myCount) = FName
        FName = Dir
    Loop

    'Print array details to sheet.
    For i = LBound(arNames) To UBound(arNames)
    Next i

    'Create a random excel sheet to print the file names.
    Set o = CreateObject("excel.application")
    ' Activate new excel spreadsheet.
    o.Visible = True
    o.Workbooks.Add
    'Edit string in array.

    'HERE
    Dim LResult As String
    'LResult = Replace(arNames, ".xlsx", "")

    o.sheets("sheet1").Range("A1:" & ConvertToLetter(i) & "1").Value = arNames

End Sub



    Function ConvertToLetter(iCol As Integer) As String
   Dim iAlpha As Integer
   Dim iRemainder As Integer
   iAlpha = Int(iCol / 27)
   iRemainder = iCol - (iAlpha * 26)
   If iAlpha > 0 Then
      ConvertToLetter = Chr(iAlpha + 64)
   End If
   If iRemainder > 0 Then
      ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)
   End If
End Function

Answer 1

原因是您试图传递变量arNames，它是字符串数组，作为函数Replace的第一个参数，该函数应该是String (注意字符串数组和字符串之间的区别)。

您需要替换这些代码行:

Dim LResult As String
'LResult = Replace(arNames, ".xlsx", "")

与那些人:

For i = LBound(arNames) To UBound(arNames)
    arNames(i) = Replace(arNames(i), ".xlsx", "")
Next i