vba - Remove substring from each Array of Strings element

Question

I am storing filenames from a folder into an array. I am then attempting to remove the ".xlsx" part of the string from the filenames, and print them to a spreadsheet. I am having difficulties removing the ".xlsx" substring from each array element. I am led to believe that the Replace function is the best for this, but haven't been successfull yet. The area of confusion is indicated by the 'HERE comment

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

mielk · Accepted Answer · 2015-07-28 12:05:50Z

5

The reason is that you are trying to pass variable arNames which is array of Strings as a first parameter of the function Replace that should be String (note the difference between array of strings and string).

You need to replace those lines of code:

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

with those one:

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

answered Jul 28, 2015 at 12:05

mielk

3,94014 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alex K. · Accepted Answer · 2015-07-28 12:14:21Z

0

Your Next i is in the wrong place?; I assume you want the text horizontally from A1, replace everything under 'Print array details to sheet. with:

'// create an instance once;
Set o = CreateObject("excel.application")
o.Visible = True
o.Workbooks.Add

'// get the startcell
Dim startCell As Range
Set startCell = o.Sheets("sheet1").Range("A1")

'// loop removing the extension and writing
For i = LBound(arNames) To UBound(arNames)
    startCell.Offset(0, i - 1).Value = Mid$(arNames(i), 1, InStrRev(arNames(i), "."))
Next

answered Jul 28, 2015 at 12:14

Alex K.

177k32 gold badges276 silver badges299 bronze badges

Collectives™ on Stack Overflow

vba - Remove substring from each Array of Strings element

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related