CODEWITHSUNDEEP
excelvba
user7419135
user7419135

Reputation:

VBA - Store first value of each row from CSV into a variable and move to specific sheet

I'm relatively new to VBA, I've been wanting to do the following, if at all possible, in VBA

  1. Point towards CSV file
  2. Loop through each row
  3. Using the first value of each row - store the rest of the row in a specific worksheet

I've managed to do points 1 and 2, however, I'm struggling on Point 3

For example: My CSV file is something like this:

A00,5675,TGI,6897
R88,7647,35968,35864
R88,5968,34531,44566
Z11,2245,FGH,YIU

I would like to read the first value of each row, in this case, A00, R88, R88 and Z11.

Then write that specific line to a corresponding worksheet based on the value.

For example, the A00 line would be written to a worksheet called A00 and so on.

I've gotten this far for reference:

Sub ImportFile()
Dim ws As Worksheet, strFile As String

strFile = Application.GetOpenFilename("Text Files (*.csv),*.csv", , "Please select text file...") 'Select file

'If first value = A00 - write to ActiveWorkbook.Sheets("A00")
'If first value = Z99 - write to ActiveWorkbook.Sheets("Z99")

Set ws = ActiveWorkbook.Sheets("A00") 'Export to this worksheet

With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1"))
     .TextFileParseType = xlDelimited
     .TextFileCommaDelimiter = True
     .Refresh
End With
End Sub

Any help would be much appreciated.

Upvotes: 0

Views: 667

Answers (1)

Applecore
Applecore

Reputation: 4099

I've put together some VBA code that seems to work how you want. It reads the data file in line by line, splits this data into an array using the "," as a separator, sets the worksheet to the first element of the array, and then places the rest of the elements into cells on the first blank line.


Sub sGetData()
    On Error GoTo E_Handle
    Dim ws As Worksheet
    Dim lngLastRow As Long
    Dim strFile As String
    Dim intFile As Integer
    Dim strInput As String
    Dim aData() As String
    Dim lngLoop1 As Long
    intFile = FreeFile
    strFile = "J:\downloads\test3.txt"
    Open strFile For Input As intFile
    Do
        Line Input #intFile, strInput
        aData() = Split(strInput, ",")
        Set ws = Worksheets(aData(LBound(aData)))
        lngLastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
        For lngLoop1 = LBound(aData) + 1 To UBound(aData)
            ws.Cells(lngLastRow + 1, lngLoop1) = aData(lngLoop1)
        Next lngLoop1
    Loop Until EOF(intFile)
sExit:
    On Error Resume Next
    Close #intFile
    Exit Sub
E_Handle:
    MsgBox Err.Description & vbCrLf & vbCrLf & "sGetData", vbOKOnly + vbCritical, "Error: " & Err.Number
    Resume sExit
End Sub

It could probably do with a bit of "bullet-proofing", to deal with things like the worksheet not existing and so on, but it should give you an idea.

Regards,

Upvotes: 1

Related Questions