
Saving Excel file to Excel file
Here's a long one, a lot of code.
I'm having one hell of a time trying to figure this one
out. Here's the basics:
I'm getting a csv file (.txt) from a barcode scanner, this
is intern saved as an Excel file (twice) and then the 2nd
Excel file is linked to a table in an Access db that is
used by a query to update the Access db.
txt file: (barcode.txt) (PalletID, LocationID, ItemID,
Date)
7,700,120,021206
8,701,L258,021206
----
When saving this file to Excel, the ItemID = L258 is saved
to barcode2.xls, but not barcode3.xls (occurs in the
databinding for some reason), it leaves the field as
(null).
--
Below is code to read the text file and save to Excel
(barcode2.xls)
--
Dim objStreamWriter As StreamWriter
Dim x As Long
Dim oneChar As Char
FileOpen(1, "C:\Apex\barcode.txt",
OpenMode.Input) ' Open file.
System.IO.File.Delete("C:\Apex\barcode2.xls")
objStreamWriter = New StreamWriter
("C:\Apex\barcode2.xls", True, Encoding.Default)
Dim a, b, c, d As String
a = "PalletID"
b = "LocationID"
c = "ItemID"
d = "Date"
objStreamWriter.Write(a)
objStreamWriter.Write(vbTab)
objStreamWriter.Write(b)
objStreamWriter.Write(vbTab)
objStreamWriter.Write(c)
objStreamWriter.Write(vbTab)
objStreamWriter.Write(d)
objStreamWriter.Write(vbCrLf)
While Not EOF(1) ' Loop until end of file.
oneChar = (InputString(1, 1))
If oneChar = Chr(44) Then
objStreamWriter.Write(vbTab)
ElseIf oneChar = " " Then
objStreamWriter.Write(vbCrLf)
Else
objStreamWriter.Write(oneChar)
End If
End While
FileClose(1)
objStreamWriter.Close()
MsgBox("Done")
--
The above doesn't save the file in a recognizable Excel
format for binding (the code below does)
--
Dim oExcel As Object
oExcel = CreateObject("Excel.Application")
'Open the excel file and save it in the Excel
workbook format.
oExcel.Workbooks.OpenText("C:\Apex\barcode2.xls", _
, , , -4142, , True)
oExcel.DisplayAlerts = False
oExcel.ActiveWorkbook.SaveAs
("C:\Apex\barcode2.xls", _
-4143)
oExcel.Workbooks.Open("C:\Apex\barcode2.xls")
'Quit Excel.
oExcel.Quit()
oExcel = Nothing
GC.Collect()
MsgBox("Barcode2.xls Created")
--
Binding Sub (Where the problem occurs somehow) L258
doesn't come with the Excel file ???
--
Dim cnString As String
= "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Apex\barcode2.xls;Extended Properties=Excel
8.0" 'HDR=YES
Dim objConn As New
System.Data.OleDb.OleDbConnection(cnString)
Dim da As New OleDb.OleDbDataAdapter("Select *
From [barcode2$]", cnString) 'barcode2
objConn.Open()
da.Fill(ds, "barcode")
dv = New DataView(ds.Tables("barcode"))
DataGrid1.DataSource = ds.Tables
("barcode").DefaultView
dv.Sort = "PalletID"
objCurrencyManager = CType(Me.BindingContext(dv),
CurrencyManager)
TextBox1.DataBindings.Clear()
TextBox1.DataBindings.Add("Text", dv, "PalletID")
TextBox2.DataBindings.Clear()
TextBox2.DataBindings.Add("Text", dv, "LocationID")
TextBox3.DataBindings.Clear()
TextBox3.DataBindings.Add("Text", dv, "ItemID")
TextBox4.DataBindings.Clear()
TextBox4.DataBindings.Add("Text", dv, "Date")
TIA. I know it's a long message.