Saving Excel file to Excel file 
Author Message
 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.



Wed, 25 May 2005 02:19:54 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. Save embedded excel spreadsheet in access to excel file

2. Saving an Excel file without using Excel

3. Saving an Excel file without using Excel

4. need help: Open Excel, run macro, save file, close Excel

5. how to consolidate excel files into one file with each old file be

6. Opening an excel file through a form/running excel macros through access

7. read excel file in access 2000 lire fichier excel dans access 2000

8. Controlling Excel from VBA after Output to Excel File

9. Excel file opened in IE3 - on one machine starts Excel, others does not

10. Accessing an Excel file without opening Excel

11. Reading Excel files without opening excel

12. Write a Excel File Without Excel App

 

 
Powered by phpBB® Forum Software