Populating multiple Excel cells in a row

[Guest]

I am creating a report from a supplied comma seperated quote delimited text file. The problem I am having is populating a row with this information and getting each seperated value in the respective column. The code below shows what I assumed would work.

Is there a way to take a delimited string and have the information populate the repective cell without looping through each element and progromatically populate each cell?

Dim intLineNum As Integer = 11
Dim strRange As String
Dim strDelimiterChar As String = ","
Dim strDelimiter As Char() = strDelimiterChar.ToCharArray()
Dim strSplit As String() = Nothing

Do
strLine = sr.ReadLine()
If Not (strLine = Nothing) Then
strSplit = strLine.Split(strDelimiter)
strRange = "A" + intLineNum.ToString() + ":" + "H" + intLineNum.ToString()
xlWKSheet.Range(strRange.ToString()).Value = strSplit.ToString()
intLineNum = intLineNum + 1
End If
Loop Until strLine Is Nothing
sr.Close()

 

[Paul Clement]

¤ I am creating a report from a supplied comma seperated quote delimited text file. The problem I am having is populating a row with this information and getting each seperated value in the respective column. The code below shows what I assumed would work.
¤
¤ Is there a way to take a delimited string and have the information populate the repective cell without looping through each element and progromatically populate each cell?
¤
¤ Dim intLineNum As Integer = 11
¤ Dim strRange As String
¤ Dim strDelimiterChar As String = ","
¤ Dim strDelimiter As Char() = strDelimiterChar.ToCharArray()
¤ Dim strSplit As String() = Nothing
¤
¤ Do
¤ strLine = sr.ReadLine()
¤ If Not (strLine = Nothing) Then
¤ strSplit = strLine.Split(strDelimiter)
¤ strRange = "A" + intLineNum.ToString() + ":" + "H" + intLineNum.ToString()
¤ xlWKSheet.Range(strRange.ToString()).Value = strSplit.ToString()
¤ intLineNum = intLineNum + 1
¤ End If
¤ Loop Until strLine Is Nothing
¤ sr.Close()

Could you post a few sample lines from your text file?


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[Guest]

"01/02/03","63032-021-15","LUXIQ FOAM 0.12% 15G","A2D016",48,"KOFF, AMY B.","02660","MA","01/01/03","12/31/03"
"01/02/03","63032-021-15","LUXIQ FOAM 0.12% 15G","A2D015",24,"MARCINCIN, PAUL G.","18017","PA","01/01/03","12/31/03"
"01/02/03","63032-021-15","LUXIQ FOAM 0.12% 15G","A2D015",18,"HELMOLD, MARIE E.","18018","PA","01/01/03","12/31/03"
"01/02/03","63032-021-15","LUXIQ FOAM 0.12% 15G","A2D015",18,"THOMPSON, ROBERT J.","18103","PA","01/01/03","12/31/03"
"01/02/03","63032-021-15","LUXIQ FOAM 0.12% 15G","A2D015",18,"WALL, JAMES R.","18104","PA","01/01/03","12/31/03"
"01/02/03","63032-021-15","LUXIQ FOAM 0.12% 15G","A2D016",18,"STERLING, GLENN B.","33326","FL","01/01/03","12/31/03"
"01/02/03","63032-031-15","OLUX FOAM 0.05% 15GR","A2C013",36,"KASTELER, J S.","40202","KY","01/01/03","12/31/03"
"01/02/03","63032-031-15","OLUX FOAM 0.05% 15GR","A2C013",36,"KIRCIK, LEON H.","40217","KY","01/01/03","12/31/03"
"01/02/03","63032-021-15","LUXIQ FOAM 0.12% 15G","A2D016",6,"SIVIK, MARY T.","44131","OH","01/01/03","12/31/03"
"01/02/03","63032-021-15","LUXIQ FOAM 0.12% 15G","A2D016",6,"HIRSCH, ANA L.","44131","OH","01/01/03","12/31/03"
"01/02/03","63032-021-15","LUXIQ FOAM 0.12% 15G","A2D017",12,"KOPRINCE, JANET L.","48067","MI","01/01/03","12/31/03"
"01/02/03","63032-031-50","OLUX FOAM 0.05% 50GR","1H964",4,"KOPRINCE, JANET L.","48067","MI","01/01/03","12/31/03"
"01/02/03","63032-021-15","LUXIQ FOAM 0.12% 15G","A2D017",12,"HARRIS, JOEL J.","48071","MI","01/01/03","12/31/03"
"01/02/03","63032-031-50","OLUX FOAM 0.05% 50GR","1H964",2,"HARRIS, JOEL J.","48071","MI","01/01/03","12/31/03"
"01/02/03","63032-021-15","LUXIQ FOAM 0.12% 15G","A2D017",12,"SUSSMAN, KATHRYN H.","48093","MI","01/01/03","12/31/03"
"01/02/03","63032-031-50","OLUX FOAM 0.05% 50GR","1H964",3,"SUSSMAN, KATHRYN H.","48093","MI","01/01/03","12/31/03"
"01/02/03","63032-021-15","LUXIQ FOAM 0.12% 15G","A2D016",12,"SWANSON-VAZALES, S","49770","MI","01/01/03","12/31/03"
"01/02/03","63032-021-15","LUXIQ FOAM 0.12% 15G","A2D016",12,"POTTER, ROGER A.","49770","MI","01/01/03","12/31/03"
"01/02/03","63032-021-15","LUXIQ FOAM 0.12% 15G","A2D015",30,"FOMAN, NEAL A.","55454","MN","01/01/03","12/31/03"
"01/02/03","63032-021-15","LUXIQ FOAM 0.12% 15G","A2D016",24,"FORSHA, DOUGLASS W.","84088","UT","01/01/03","12/31/03"
"01/02/03","63032-031-15","OLUX FOAM 0.05% 15GR","A2C014",24,"FORSHA, DOUGLASS W.","84088","UT","01/01/03","12/31/03"
"01/02/03","63032-021-15","LUXIQ FOAM 0.12% 15G","A2D016",18,"SOUTHWICK, EDWARD G.","84120","UT","01/01/03","12/31/03"
"01/02/03","63032-021-15","LUXIQ FOAM 0.12% 15G","A2D017",6,"RAYHAN, SYRUS","92647","CA","01/01/03","12/31/03"

 

[Paul Clement]

The following uses ADO.NET to import into an existing Excel Worksheet named ReportSheet. The source
text file is ReportFile.txt.

Function ImportTextToExcel() As Boolean

Dim ExcelConnection As New
System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\My Documents\Book20.xls;Extended Properties=""Excel 8.0;HDR=NO;""")

ExcelConnection.Open()

Dim ImportCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [ReportSheet] (F1, F2,
F3, F4, F5, F6, F7, F8, F9, F10) SELECT * FROM [Text;HDR=NO;DATABASE=E:\My
Documents\TextFiles].[ReportFile.txt]", ExcelConnection)

ImportCommand.ExecuteNonQuery()
ExcelConnection.Close()

End Function


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[Tibby]

I am creating a report from a supplied comma seperated quote delimited text file. The problem I am having is populating a row with this information and getting each seperated value in the respective column. The code below shows what I assumed would work.

Is there a way to take a delimited string and have the information populate the repective cell without looping through each element and progromatically populate each cell?

Dim intLineNum As Integer = 11
Dim strRange As String
Dim strDelimiterChar As String = ","
Dim strDelimiter As Char() = strDelimiterChar.ToCharArray()
Dim strSplit As String() = Nothing

Do
strLine = sr.ReadLine()
If Not (strLine = Nothing) Then
strSplit = strLine.Split(strDelimiter)
strRange = "A" + intLineNum.ToString() + ":" + "H" + intLineNum.ToString()
xlWKSheet.Range(strRange.ToString()).Value = strSplit.ToString()
intLineNum = intLineNum + 1
End If
Loop Until strLine Is Nothing
sr.Close()

Ran into a similar problem, and then had a brainstorm. I made the
workbook as I wanted it without any data, and saved it as XML, using
that as a template. I then took that, and created a system that goes
row by row and inserts the appropriate info, and when you open it in
Excel, no data problems or any of the crap. I've tested it with
Office XP/2003.
I personally hate the Excel objects.... DLL Hell...

Tibby