I am trying to import a CSV text file using the below code. I essentially have 2 issues which I need assistant in:
1. I am trying to read the text file in question and only import certain rows based on whether column 2 of the CSV file has a certain text included or not
2. When I import the file, the text qualifier (i.e. Apostrophes ... " ) also get imported - how can I avoid that.
My macros code is as below
1. I am trying to read the text file in question and only import certain rows based on whether column 2 of the CSV file has a certain text included or not
2. When I import the file, the text qualifier (i.e. Apostrophes ... " ) also get imported - how can I avoid that.
My macros code is as below
VBA Code:
Sub Import_H10_Ranking()
Dim R As Integer
Dim C As Integer
Dim sDelim As String
Dim sRaw As String
Dim ReadArray() As String
sDelim = ","
Worksheets.Add
vFileName = Application.GetOpenFilename("Comma Seperated Fiiles(*.csv),*.csv")
Open vFileName For Input As #1
Do While Not EOF(1)
Line Input #1, sRaw
ReadArray() = Split(sRaw, sDelim, 35, vbTextCompare)
If ReadArray(1) <> "($)*" Then ' Column 2 of the CSV file must not include ($). This will always be the first three characters of the text string within column 2
Cells(R + 1, 1).Value = ReadArray(0) 'First column of CSV File
Cells(R + 1, 2).Value = R ' Row number
Cells(R + 1, 3).Value ="Text 1"
Cells(R + 1, 4).Value = ReadArray(1) ' Column 2 of CSV
Cells(R + 1, 5).Value = ReadArray(7) ' Column 8 of CSV
Cells(R + 1, 6).Formula = "=G" & R + 1 & "/ 30" 'Formula
Cells(R + 1, 7).Value = ReadArray(9) ' Column 10 of CSV
Cells(R + 1, 8).Value = ReadArray(13) ' Column 14 of CSV
Cells(R + 1, 9).Value = ReadArray(12) ' Column 13 of CSV
Cells(R + 1, 10).Value = ReadArray(11) ' Column 12 of CSV
R = R + 1
End If
Loop
Close #1
End Sub