Find Next Empty Row In Excel And Populate With Data

Legacy Poster

• September 26, 2013 14:04

I've been learning VB as I go along for this application but the scenario is this:
 

I currently have the script running at the time a user prints a label (onIdentidcalCopies), it opens up a spreadsheet, updates information, saves then closes the spreadhseet with the code below:

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("\\dt01.local\files$\UserFolders\samc\My Documents\Booker Card http://www.vbforums.com/showthread.php?735975-Find-Next-Empty-Row-in-Excel-and-Populate#\Printed Docs.xlsx")
objExcel.Application.Visible = True
objWorkbook.WorkSheets(1).Activate
objWorkbook.WorkSheets(1).Cells(1, 1).Value = "Test value2"

objExcel.ActiveWorkbook.Save
objExcel.ActiveWorkbook.Close


objExcel.Application.Quit


I could only find the code which would update a specified cell with the information given (1,1 for example). What I really need is for the code to move to the subsequent blank row below each time a label is printed to avoid overwriting information but I am stumped as to how to write this.

At this stage only one item is needed to be written to the spreadsheet so it will be restricted to column A, this will be variable data instead of fixed text as the script shows but I am fairly certain that's an http://www.vbforums.com/showthread.php?735975-Find-Next-Empty-Row-in-Excel-and-Populate# change I can make, I'd like to get the data sequencing right first.

Any help would be appreciated

0

• 

  Canguita

  ★ BarTender Hero ★

  • September 26, 2013 16:11

  Hello Sam,

   

  Thank you very much for your post. In order to don't overwrite your records in Excel we have some VBscript events as OnNewRecord() or OnPostPrompt() where you will be able to edit your VBscript

   

   

  I've attached you a sample. In the BarTender label format I have made a connection to the Excel file, which assumes it will be found in the "C:\Seagull" folder.  It uses a query to only find and therefore possibly print those products (records) that do not have the word "YES" in the "Printed" column.  The select record at print time option is turned on allowing the user to select which of the records that have not been printed, are to be printed.

   

  During the print job the VB script contained in the object in red placed off the label, runs a section of code that connects to the Excel file, finds the record being printed and sets the "Printed" field to the value "YES" so that next time the user prints it will not be available in the select at print time dialog to choose.  The VB script is as follows.

   

  OnNewRecord event

   

  'Define the object names.

  dim objConn

  dim strConn

  dim rs

   

  'Define the database connection parameter constants.

  Const adOpenStatic = 3

  Const adLockOptimistic = 3

  Const adCmdText = &H0001

   

  'Connect to the Excel file.

  Set objConn = CreateObject("ADODB.Connection")

  objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\Seagull\Fruits.xls';Extended Properties=""Excel 8.0;HDR=YES;"""

   

  'Create a record set of one record which is the current record being printed.

  Set rs = CreateObject("ADODB.Recordset")

  rs.open "SELECT * FROM [Sheet1$]", objConn, adOpenStatic, adLockOptimistic, adCmdText

  rs.Find "Product = '" & Field("Sheet1$.Product") & "'"

  'Set the "Printed" field of the record to be "YES" thus flagging it as having been printed.

  rs.Fields("Printed") = "YES"

  rs.Update

   

  Please let me know if this help you.

  0
• 

  Legacy Poster

  ★ BarTender Hero ★

  • September 26, 2013 16:27

  Carlos thanks for your reply. Our customer unfortunately wants to operate in a different mannor. They have issued us with a CSV file in MSDOS format containing the information to be printed on the label, hence is not a spreadsheet as such. We are then to update a seperate (new) spreadsheet with certain fields taken from Bartender which have been printed on the label. A method of tracking which records have been printed.

   

  The part where my knowledge fell down was to prompt excel to look at column A and input the data in the next available empty row with the VB script in Bartender.

  0
• 

  Canguita

  ★ BarTender Hero ★

  • October 03, 2013 14:35

  Hello Sam,

   

  Thank you very much for your feedback.

   

  Can you let me know why exactly do you need to write in your database?

   

  The problem here is that in VBscript when you access to a database you will work in a record, in this record you will be able to write on every database fields but you will not be able to write on a different record.

   

  Maybe with a very tedious workaround could be possible to write on a new record, however another problem is that if your code is writing in the next record on blank and your database connection is crossing the database until find empty records you will entry in a loop.

   

  Please let us know what your customer is requesting exactly and we will check if there is an easier alternative.

  0

Please sign in to leave a comment.