Populating multiple Excel cells in a row

  • Thread starter Thread starter Guest
  • Start date Start date
G

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()
 
¤ 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)
 
"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"
 
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)
 
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()
Click to expand...

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
 
Back
Top