Skip to main content

Search

Search

Exporting Data To Excel

Comments

9 comments

  • Avatar
    Legacy Poster
    Sharon,

    You could use one of BarTender's pre-built logging solutions to write to the system database or create a text file log. This can be set under [b]Administer[/b] > [b]Log Setup[/b]

    Alternatively, I have used the code below to program my own export script when a label is printed. This script is in my OnIdenticalCopies VB event at the document level (found by going to [b]View[/b] > [b]View Options[/b] > [b]VB Scripting Tab[/b]

    [code]'Place script below at to
    'If Format.IsPrinting = False then Exit sub

    Dim objCon
    Dim strSQL

    Set objCon = CreateObject("ADODB.Connection")
    'Create connection (in the case of xls file in the same folder as btw file)
    objCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='DataCollection.xls';Extended Properties=""Excel 8.0;HDR=YES;"""
    objCon.Open

    'Create statement
    strSQL = "INSERT INTO [Sheet1$] (Data1) values ('" & BarcodeData & "')"


    'Run SQL
    objCon.Execute strSQL

    'Close connection
    objCon.Close
    Set objCon = Nothing[/code]

    You could take this code and expand it to include more columns. The 'Data1' Just refers to my first column, and 'BarcodeData' refers to my data source shared name. So you can take that line and copy it many times changing the column name and data source name to write more things. Obviously you will have to change the other text a bit to account for your file name and location.
    0
  • Avatar
    Legacy Poster

    I have tried to use this with a modification to fill more than one field at once , a multiple from one sql statement as below

     

    'Create statement
    strSQL = "INSERT INTO [Sheet1$] (Power,LotNo,ExpiryDate,Qty,BoxNumber) values ('" & Power1&LotNo1&ExpDate1&Qty1&BoxNumber & "')"
    'Run SQL
    objCon.Execute strSQL

     

    and it brings up the error number of query values and destination fields are not the same, I think I have tried every variation on this without success and had a few other people look at this with me. If anyone knows where I am going wrong let me know thanks. (I think the values are wrong because the & is joining them into one long number rather than seperating them .

    0
  • Avatar
    Legacy Poster

    I have tried to use this with a modification to fill more than one field at once , a multiple from one sql statement as below

     

    'Create statement
    strSQL = "INSERT INTO [Sheet1$] (Power,LotNo,ExpiryDate,Qty,BoxNumber) values ('" & Power1&LotNo1&ExpDate1&Qty1&BoxNumber & "')"
    'Run SQL
    objCon.Execute strSQL

     

    and it brings up the error number of query values and destination fields are not the same, I think I have tried every variation on this without success and had a few other people look at this with me. If anyone knows where I am going wrong let me know thanks. (I think the values are wrong because the & is joining them into one long number rather than seperating them .

    I also tried this

    strSQL = INSERT INTO [Sheet1$] (Power,LotNo,ExpiryDate,Qty) VALUES('" & Power1 & "', '" & LotNo1 &"', '" & ExpDate1 &"', '" & Qty1 &"')"
    and

    strSQL = INSERT INTO [Sheet1$] (Power,LotNo,ExpiryDate,Qty) VALUES('" & Power1 & "', '" & LotNo1 &"', '" & ExpDate1 &"', '" & Qty1 &"');"

     

    nothing seems to work .

    0
  • Avatar
    Legacy Poster

    Sorry to resurrect an old thread but I have a similar situation that I don't know how to solve.

     

    I have an excel database in which all my Serial Numbers and Test related data is stored. I have managed to set queries to query one serial number at a time (via a barcode scanner) and print only data that contains a "Passed" remark. Works great but I also want to export whatever I printed plus the data that was retrieved to an excel sheet so that I can have a record of what was printed and queried.

     

    So my question is: How can I export data that was queried and printed into another excel sheet? I am a VB newbie let alone VB newbie to Bartender so please walk me through it.

     

    Thank you in advance..

    0
  • Avatar
    Legacy Poster

    anyone?

    0
  • Avatar
    Legacy Poster

    Does anyone have a working example of this?

    0
  • Avatar
    Shotaro Ito

    BarTender's database connection is read only and you cannot write back the value to database.

    In Automation edition or above, you can log desired field to CSV file via Administer > Log setup > Text file log > Log print job information, which might helps.

    0
  • Avatar
    MUHAMMAD ABBAS

    One silly question, the above VBA code where will I add path of my excel file?

    0
  • Avatar
    gurkaN GURBUZ

    This is working 

    i found it too many years later  :D

     

    strSQL = "INSERT INTO [Sheet1$] (item, serialnumber) values ('" & Format.Objects("Serial").value & "','" & Format.Objects("item").value & "')"

    0

Please sign in to leave a comment.