Open a .csv file, parse text and display on more than one sheet
 

How would you open a .csv file (comma seperated values),
parse (using line returns and so forth),
and put in different worksheets in Excel programatically.

I usually use C#, but I guess any language would do.

Thanks in Advance! (-:
--
Joseph
a/k/a joseph_doggie

Open a .csv file, parse text and display on more than one sheet
 

I think this will do what you want:
Public Sub DoTheImport()
Dim FName As Variant
Dim Sep As String

FName = Application.GetOpenFilename _
(filefilter:="Text Files(*.txt),*.txt,All Files (*.*),*.*")
If FName = False Then
MsgBox "You didn't select a file"
Exit Sub
End If

Sep = InputBox("Enter a single delimiter character.", _
"Import Text File")
ImportTextFile CStr(FName), Sep

End Sub



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

Dim RowNdx As Long
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer

Application.ScreenUpdating = False
'On Error GoTo EndMacro:

SaveColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row

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
ColNdx = SaveColNdx
Pos = 1
NextPos = InStr(Pos, WholeLine, Sep)
While NextPos = 1
TempVal = Mid(WholeLine, Pos, NextPos - Pos)
Cells(RowNdx, ColNdx).Value = TempVal
Pos = NextPos + 1
ColNdx = ColNdx + 1
NextPos = InStr(Pos, WholeLine, Sep)
Wend
RowNdx = RowNdx + 1
Wend

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

End Sub




Regards,
Ryan---

--
RyGuy


"joseph_doggie" wrote:

How would you open a .csv file (comma seperated values),
parse (using line returns and so forth),
and put in different worksheets in Excel programatically.

I usually use C#, but I guess any language would do.

Thanks in Advance! (-:
--
Joseph
a/k/a joseph_doggie

Open a .csv file, parse text and display on more than one shee
 

Thanks very much.

I'm somewhat new to VB.

When I try to compile this code,
I get errors like:
30451: Name 'Application' is not declared.
C:\Work\Everest\OfficeIntegration\ConsoleApplicati on2\ConsoleApplication2\Module1.vb(38) : error BC30451: Name 'ActiveCell' is not declared.
C:\Work\Everest\OfficeIntegration\ConsoleApplicati on2\ConsoleApplication2\Module1.vb(39) : error BC30451: Name 'ActiveCell' is not declared.

I am working in VS 2008, and I realize it may not be worth your while to
correct these.

However, if anyone knows a solution, please let me know.

But I sincerely do appreciate you taking the time to answer my question; it
was very thoguhtful of you.


--
Joseph
a/k/a joseph_doggie


"ryguy7272" wrote:

I think this will do what you want:
Public Sub DoTheImport()
Dim FName As Variant
Dim Sep As String

FName = Application.GetOpenFilename _
(filefilter:="Text Files(*.txt),*.txt,All Files (*.*),*.*")
If FName = False Then
MsgBox "You didn't select a file"
Exit Sub
End If

Sep = InputBox("Enter a single delimiter character.", _
"Import Text File")
ImportTextFile CStr(FName), Sep

End Sub



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

Dim RowNdx As Long
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer

Application.ScreenUpdating = False
'On Error GoTo EndMacro:

SaveColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row

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
ColNdx = SaveColNdx
Pos = 1
NextPos = InStr(Pos, WholeLine, Sep)
While NextPos = 1
TempVal = Mid(WholeLine, Pos, NextPos - Pos)
Cells(RowNdx, ColNdx).Value = TempVal
Pos = NextPos + 1
ColNdx = ColNdx + 1
NextPos = InStr(Pos, WholeLine, Sep)
Wend
RowNdx = RowNdx + 1
Wend

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

End Sub




Regards,
Ryan---

--
RyGuy


"joseph_doggie" wrote:

How would you open a .csv file (comma seperated values),
parse (using line returns and so forth),
and put in different worksheets in Excel programatically.

I usually use C#, but I guess any language would do.

Thanks in Advance! (-:
--
Joseph
a/k/a joseph_doggie

Open a .csv file, parse text and display on more than one shee
 

This is VBA for Excel. You need to run the code that I gave you through Excel.

Regards,
Ryan---

--
RyGuy


"joseph_doggie" wrote:

Thanks very much.

I'm somewhat new to VB.

When I try to compile this code,
I get errors like:
30451: Name 'Application' is not declared.
C:\Work\Everest\OfficeIntegration\ConsoleApplicati on2\ConsoleApplication2\Module1.vb(38) : error BC30451: Name 'ActiveCell' is not declared.
C:\Work\Everest\OfficeIntegration\ConsoleApplicati on2\ConsoleApplication2\Module1.vb(39) : error BC30451: Name 'ActiveCell' is not declared.

I am working in VS 2008, and I realize it may not be worth your while to
correct these.

However, if anyone knows a solution, please let me know.

But I sincerely do appreciate you taking the time to answer my question; it
was very thoguhtful of you.


--
Joseph
a/k/a joseph_doggie


"ryguy7272" wrote:

I think this will do what you want:
Public Sub DoTheImport()
Dim FName As Variant
Dim Sep As String

FName = Application.GetOpenFilename _
(filefilter:="Text Files(*.txt),*.txt,All Files (*.*),*.*")
If FName = False Then
MsgBox "You didn't select a file"
Exit Sub
End If

Sep = InputBox("Enter a single delimiter character.", _
"Import Text File")
ImportTextFile CStr(FName), Sep

End Sub



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

Dim RowNdx As Long
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer

Application.ScreenUpdating = False
'On Error GoTo EndMacro:

SaveColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row

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
ColNdx = SaveColNdx
Pos = 1
NextPos = InStr(Pos, WholeLine, Sep)
While NextPos = 1
TempVal = Mid(WholeLine, Pos, NextPos - Pos)
Cells(RowNdx, ColNdx).Value = TempVal
Pos = NextPos + 1
ColNdx = ColNdx + 1
NextPos = InStr(Pos, WholeLine, Sep)
Wend
RowNdx = RowNdx + 1
Wend

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

End Sub




Regards,
Ryan---

--
RyGuy


"joseph_doggie" wrote:

How would you open a .csv file (comma seperated values),
parse (using line returns and so forth),
and put in different worksheets in Excel programatically.

I usually use C#, but I guess any language would do.

Thanks in Advance! (-:
--
Joseph
a/k/a joseph_doggie