importing cvs txt into column vs. row

  • Thread starter Thread starter Craig J
  • Start date Start date
C

Craig J

I am trying to import a CSV text file into excel in a column format vic
row due to the fact that I have 1024 cells of data and in the ro
format it gets cut off due to length limitations. Anyone have an
ideas? Thank yo
 
Craig,

Try something like the code below.

HTH,
Bernie
MS Excel MVP

Public Sub TransposeImportTextFile(FName As String, Sep As String)
'Based on Chip Pearson's Code
'Modified by Bernie Deitrick June 10, 2003
Dim RowNdx As Integer
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveRowNdx As Integer

Application.ScreenUpdating = False
'On Error GoTo EndMacro:

SaveRowNdx = ActiveCell.Row
ColNdx = ActiveCell.Column

Open FName For Input Access Read As #1

While Not EOF(1)
Line Input #1, WholeLine
If Right(WholeLine, 1) <> Sep Then
WholeLine = WholeLine & Sep
End If
RowNdx = SaveRowNdx
Pos = 1
NextPos = InStr(Pos, WholeLine, Sep)
While NextPos >= 1
TempVal = Mid(WholeLine, Pos, NextPos - Pos)
Cells(RowNdx, ColNdx).Value = TempVal
Pos = NextPos + 1
RowNdx = RowNdx + 1
NextPos = InStr(Pos, WholeLine, Sep)
Wend
ColNdx = ColNdx + 1
Wend

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1

End Sub
 
Bernie, I work with craig. thatnks for the help, but we are having
prooblem with the code and are too ignorant to sort it out.

We pasted that code into a module and saved it but the Macro does no
show up in the Excel macro menu. When we try to run it from the VB
editor, it pops up the empty macro menu.

we are lost

Thanks again for responding in the first plac
 
Hammer,

Sorry. I should have been more explicit with my instructions.

The macro needs to be called from another macro, since it must be passed
parameters.

Since it is declared like this:

Public Sub TransposeImportTextFile(FName As String, Sep As String)

it muct be used like this

Sub Test()
TransposeImportTextFile "C:\Excel\CSVfile.txt", ","
End Sub

You could also rewrite the macro to not take parameters, but prompt for the
filename:
Change

Public Sub TransposeImportTextFile(FName As String, Sep As String)

to

Public Sub TransposeImportTextFile()
Dim FName As String
Dim Sep As String
FName = Application.GetOpenFilename
Sep = ","

Sorry for the confusion.

HTH,
Bernie
MS Excel MVP
 
Back
Top