Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Want to use Excel to parse a text file | Excel Programming | |||
load/parse large text file | Excel Programming | |||
Lookup on a sheet so that every time when file open it copes data from another file sheet | Excel Programming | |||
How to parse data in text file | Excel Programming | |||
Parse Text File | Excel Programming |