how to select text strings and export them to a existing excel xls

[Shaun29]

I need to fix this code== so when I open my filedialog and select a text file, then it shows up in my text box, I then chase the file path open the text file, read the text find certin varibles or letter group in the rows of text that are like tags, the extract the whole row into an excell sheet on my desktop. can anyone tell me were i am going wrong?

Sample of row in a text file:

MK QTY ITEM DESCRIPTION MATERIAL WIEGHT
2 34 145 FB METAL STEEL 100PDS
2 50 146 S METAL STEEL 100PDS
2 36 147 DR METAL STEEL 100PDS



So When I read from this Text File that was open from openfiledialog it will then saerch for FB and DR and send the entire row in its contents to seprate excell sheets.


here is my code:



==========================================================





Public Class Form1



Dim ofd As OpenFileDialog
Dim WithEvents txtFile As TextBox
Dim WithEvents btnOpen As Button
Dim WithEvents btnWriteFile As Button
Const LogFile As String = ""





Private Sub Form_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
ofd = New OpenFileDialog
ofd.Filter = "Text Files|*.txt|Excel|*.xls"
txtFile = New TextBox
txtFile.Left = 12
txtFile.Top = 24
txtFile.Width = 160
txtFile.ReadOnly = True
Me.Controls.Add(txtFile)
btnOpen = New Button
btnOpen.Text = "..."
btnOpen.Width = 24
btnOpen.Height = Me.txtFile.Height
btnOpen.Left = Me.txtFile.Left + Me.txtFile.Width + 4
btnOpen.Top = Me.txtFile.Top
Me.Controls.Add(btnOpen)
AddHandler btnOpen.Click, AddressOf OpenFileButtonClick
btnWriteFile = New Button
btnWriteFile.Width = 60
btnWriteFile.Text = "Write File"
btnWriteFile.Top = Me.txtFile.Top + Me.txtFile.Height + 6
btnWriteFile.Left = 12
Me.Controls.Add(btnWriteFile)
AddHandler btnWriteFile.Click, AddressOf WriteFileButtonClick
End Sub

Private Sub OpenFileButtonClick(ByVal sender As System.Object, ByVal e As System.EventArgs)
Me.txtFile.Text = ""
ofd.ShowDialog()
Me.txtFile.Text = ofd.FileName
End Sub

Private Sub WriteFileButtonClick(ByVal sender As System.Object, ByVal e As System.EventArgs)
If Not Me.txtFile.Text.Trim = "" Then
'Read the existing file
Dim FileText As String = ""
FileText = System.IO.File.ReadAllText(Me.txtFile.Text)
'Write to the log file
System.IO.File.AppendAllText(LogFile, FileText)
MessageBox.Show("Text Transfered.")
Else
MessageBox.Show("Please select a file.")
End If
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

' do I need to Dim the excel file "C:\ test.xls"?
'and how do i set up this system.IO to write text to the excel
'sheet after it reads for the specified text like FB or DR???
'is there a way to do it using sytem IO?
'and if so were should the code go in this form???????

ofd.ShowDialog()
If Not Me.txtFile.Text.Trim = "" Then
'Read the existing file
Dim FileText As String = ""
FileText = System.IO.File.ReadAllText(Me.txtFile.Text)
'Write to the log file
System.IO.File.AppendAllText(LogFile, FileText)
MessageBox.Show("Text Transfered.")
Else
MessageBox.Show("Please select a file.")
End If
End Sub

End Class

 

[jebenson]

Try something like this:


Dim oExcel As Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet

Dim RangeVal As String

Dim SheetName As String



'Start a new workbook in Excel.
oExcel = New Excel.Application
oBook = oExcel.Workbooks.Add

oSheet = oBook.worksheets.add

oSheet.name = "Some name for the worksheet"

oSheet.range("A1").select()

'To build an input string for excel, place a tab (vbTab) between each value, and a CR/LF (vbCrLf) at the end of each line

'put in header row
RangeVal = "MK" & vbTab & "QTY" & vbTab & "ITEM" & vbTab & "DESCRIPTION" & vbTab & "MATERIAL" & vbTab & "WIEGHT" & vbCrLf


'when you find a row you like, add it to RangeVal:
RangeVal &= "2" & vbTab & "36" & vbTab & "147" & vbTab & "DR" & vbTab & "METAL" & vbTab & "STEEL" & vbTab & "100PDS"


'finally, copy RangeVal to the worksheet:
System.Windows.Forms.Clipboard.SetDataObject(RangeVal)

oBook.Worksheets(SheetName).Paste()

System.Windows.Forms.Clipboard.SetDataObject("")

RangeVal = Nothing


'clean up when done:
oBook.SaveAs("Put a path and filename here")
oSheet = Nothing
oBook = Nothing
oExcel.Quit()
oExcel = Nothing


Hope this helps.


I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[Andrzejek]

Are you saying to Copy to Clipboard and Paste to Excel?

'finally, copy RangeVal to the worksheet:
System.Windows.Forms.[red]Clipboard[/red].SetDataObject(RangeVal)

oBook.Worksheets(SheetName).[red]Paste[/red]()

Is that your approach?

Have fun.

---- Andy

 

[Shaun29]

extract into

 

[Shaun29]

I need to basically use my open text file find the rows in which i need and send them to a template xls sheet i made

 

[jebenson]

Are you saying to Copy to Clipboard and Paste to Excel?

'finally, copy RangeVal to the worksheet:
System.Windows.Forms.Clipboard.SetDataObject(RangeVal)

oBook.Worksheets(SheetName).Paste()

Is that your approach?

Have fun.

Well considering I posted that, I'd say it's rather obvious that yes, this is my approach. An approach which I have been using in live applications for years, with no problems whatsoever.

Is this all you have to say about it? If you have a problem with this approach, please state what your problem is and what you think is a better solution. If all you're going to do is post a snarky reply like this with nothing constructive, I'd like to suggest that you keep it to yourself in the future.



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!