MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

How to use Workbooks.opentext method in VB.NET

  Asked By: Jason    Date: Nov 08    Category: MS Office    Views: 8082

I am using following method in VB.NET for opening text files in to excel. It
works fine.

xlObject.Workbooks.OpenText(FileName:=FileNm & ".txt", Origin:=437, StartRow:=1,
DataType:=xlTextParsing.xlFixedWidth, TrailingMinusNumbers:=True)

But as in VB for FieldInfo v have to enter following code

FieldInfo:=Array(Array(0, 1), Array(45, 1), Array(55, 1), Array(65, 1),
Array(75, 1), Array(85, 1), Array(95, 1), Array( 105, 1), Array(116, 1),
Array(125, 1) )

I want to use same code for FieldInfo in VB.NET. But Array doesn't work in

So how to use FieldInfo in VB.NET in workbooks.opentext method?



1 Answer Found

Answer #1    Answered By: James Currier     Answered On: Dec 30

Not the cleanest code in the world but I think you will get the idea...

Imports Excel = Microsoft.Office.Interop.Excel
Imports System.IO
Imports Microsoft.VisualBasic
Imports Microsoft.Office.Interop.Excel
Imports Microsoft.Office.Interop.Excel.XlColumnDataType

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim xlApp As Excel.Application
Dim xlWorkbook As Excel.Workbook
Dim xlWorksheet As Excel.Worksheet
Dim myStr As String
Dim FileReader As StreamReader
Dim FileWriter As StreamWriter
Dim results As DialogResult
Dim openFileDialog1 As New OpenFileDialog()
Dim mycol(,) As Object = New Object(,) {{1, 2}, {2, 2}, {3, 5}, {4, 1}, {5, 2}, {6, 1}}

openFileDialog1.InitialDirectory = "c:\"
openFileDialog1.Filter = "txt files (*.txt)|*.txt|All files (*.*)|*.*"
openFileDialog1.FilterIndex = 2
openFileDialog1.RestoreDirectory = True
results = openFileDialog1.ShowDialog

If results = DialogResult.OK Then

FileReader = New StreamReader(openFileDialog1.FileName)

myStr = FileReader.ReadToEnd()
'myStr = Replace(myStr, "'", "")

FileWriter = New StreamWriter("c:\Temp\invoices.txt")



xlApp = CreateObject("Excel.Application")

xlApp.Visible = False

xlApp.Workbooks.OpenText("C:\Temp\invoices.txt", , , , XlTextQualifier.xlTextQualifierSingleQuote, True, FieldInfo:=mycol)
xlWorkbook = xlApp.ActiveWorkbook
xlWorksheet = xlWorkbook.Worksheets("invoices")

MsgBox(xlWorksheet.Cells(2, 1).value)
xlWorkbook.SaveAs("c:\Temp\invoices.xlsx", FileFormat:=51)
End If

End Sub

Didn't find what you were looking for? Find more on How to use Workbooks.opentext method in VB.NET Or get search suggestion and latest updates.