Opening more a data file contain more than 253 column

  • Thread starter Thread starter Syed
  • Start date Start date
S

Syed

I was trying to open a matrix of 460 column by 460 raw in excel. The data was
in *.CSV format. Due to the 253 column limitation I can not open the file in
excel. I know using macro excel can do it. But I dont have the macro. Could
anyone please help me?
 
Sub GetCSVData()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0



Set fsread = CreateObject("Scripting.FileSystemObject")

'default folder
Folder = "C:\temp\test"

Newfolder = Application.GetOpenFilename("CSV (*.csv),*.csv")
If Not Newfolder = False Then
Folder = ""
Do While InStr(Newfolder, "\") > 0
Folder = Folder & Left(Newfolder, InStr(Newfolder, "\"))
Newfolder = Mid(Newfolder, InStr(Newfolder, "\") + 1)
Loop
'remove last character which is a \
Folder = Left(Folder, Len(Folder) - 1)
End If

Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
RowCount = Lastrow + 1
First = True
Do
If First = True Then
Filename = Dir(Folder & "\*.csv")
First = False
Else
Filename = Dir()
End If
If Filename <> "" Then
'open files
Set fread = fsread.GetFile(Folder & "\" & Filename)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)

Do While tsread.atendofstream = False

InputLine = tsread.Readline

'extract comma seperated data
ColumnCount = 1
Do While InputLine <> ""
CommaPosition = InStr(InputLine, ",")
If CommaPosition > 0 Then
data = Trim(Left(InputLine, CommaPosition - 1))
InputLine = Mid(InputLine, CommaPosition + 1)
Else
data = Trim(InputLine)
InputLine = ""
End If

Cells(RowCount, ColumnCount) = data
ColumnCount = ColumnCount + 1
Loop
RowCount = RowCount + 1
Loop

tsread.Close
End If
Loop While Filename <> ""
End Sub
 
Back
Top