0

I'm trying to read a csv file using an excel macro, the csv file doesn't have the same number of values on each row. I'm trying to create a nested loop that goes through each value within each row one at a time to update a GUI.

I can open the csv and read in lines by hard coding which lines I want to read, but would like to be able to have the flexibility to utilize a large variety of user generated csv files.

Private Sub LoadSettings_btn_Click()
    Dim strfilename As String
    Dim fDialog As FileDialog, result As Integer
    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
    fDialog.AllowMultiSelect = False
    fDialog.Title = "Select a file"
    fDialog.InitialFileName = "H:\Personal\SideProjects"</code>

    If fDialog.Show = -1 Then
        strfilename = fDialog.SelectedItems(1)
    End If
    Dim LineItems As String


    Open strfilename For Input As #1
    row_number = 0
    Do Until EOF(1)
        Line Input #1, LineFromFile
        LineItems = Split(LineFromFile, ",")
        ActiveCell.Value = LineItems.Length()
    Loop
    Close #1
End Sub

I expect to get the length of the array, in the case of my test file 1 for the first row, and 3 for the second row. However I'm told I have an invalid qualifier.

Damian
  • 5,152
  • 1
  • 10
  • 21
JoshGivens
  • 128
  • 8
  • 2
    `LBound(Array)` will return you the first index of the array and `UBound(Array)` the last index. Note that arrays built from Split start on index 0 so maybe you should add 1 to the `Ubound`. – Damian May 22 '19 at 15:45
  • I think you'll get a Type Mismatch error if you `Dim LineItems as String` and then try `LineItems = Split...`. – BigBen May 22 '19 at 15:48
  • @Damian thanks, I've been struggling with that for the past few hours. Your solution works perfectly. – JoshGivens May 22 '19 at 15:53

1 Answers1

1

However an array is created, the number of items in the array is:

UBound(arr) - LBound(arr) + 1

for example:

Sub qwerty()
    arr = Split("abc,def,ghi", ",")
    MsgBox UBound(arr) - LBound(arr) + 1
End Sub

NOTE:

This is true even if you manually create an array like:

Dim poiuy( 13 to 27) As String
Gary's Student
  • 95,722
  • 10
  • 59
  • 99