Get Data From Excel Db
Hello
How can I get data from my excel db into my text object txt3, if txt1 = b and txt2 = 2?
My excel:
a 1 100
a 2 101
a 3 102
b 1 103
b 2 104
b 3 105
As you can see I want txt3 to show 104.
txt1 and txt2 get its value from Promptdesign.
How can I get data from my excel db into my text object txt3, if txt1 = b and txt2 = 2?
My excel:
a 1 100
a 2 101
a 3 102
b 1 103
b 2 104
b 3 105
As you can see I want txt3 to show 104.
txt1 and txt2 get its value from Promptdesign.
0
-
The solution would be to use a post prompt event based VB script to look-up in the table the matching value for the txt3 label object. If the look-up is quite short and unlikely to change then a Select...Case statement would be easier to use. Otherwise the VB script would need to make a connection to the Excel worksheet itself to make the look-up.
Does the look-up need to check data in an Excel file?0 -
Yes, so I guess I need to make a connection to the excel in vb-script.
How will the select string looks like, when its Excel?
SELECT [txt3] FROM [Sheet1$] WHERE [txt1] = subtxt1 AND [txt2] = subtxt2
subtxt1 and subtxt2 is subnames for the text object..
Is this correct?0 -
Here is an example I made in the past to open an Excel worksheet, find a record and then mark it as having been printed. With a little alteration you can switch it for use in your own specific requirements.
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.
I'm not permitted to upload the .xls file, but this is an example of what it looked like:
Product Printed
Apple
Pear
Orange
Banana YES
Melon
Kiwifruit
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
As you are not using Bartender's database connectivity, but rather user prompts, using the OnPostPrompt event is probably more suitable. Make sure you give your prompted items a Share Name in the Data Source tab which you can then reference in the VB script.
If you have any questions, please feel free to ask.0 -
Thank you. I will try working with this. 0 -
When I use your example, how do I change the code, so I don't have to pick a value, but it takes the value from a text object/sharename? 0 -
First you need to set a share name for an objects Data Source sub-string. For example you give the name: btPartNo
Then in your VB script you can use the assistant to select "Shared Sub-Strings" under the "Category" list and then double click the appropriate share name in the "Name" list to insert the correct expression into your code.
With a share name in my example, the code expression would be: Format.NamedSubStrings("btPartNo").Value0 -
I added a new column in the db (PartNo) and gave it a number. Added a share name to the text object that have the btPartNo and then I added this code in OnPostPrompt:
[code]
'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='I:\Database\Test2003.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$] WHERE [PartNo] = Format.NamedSubStrings("btPartNo").Value", objConn, adOpenStatic, adLockOptimistic, adCmdText
[/code]
This gives me a fail in the rs.open line: Expected end of statement0 -
Ahhh, this is the joy of piecing together such statements that contain both literal and variable information.
If you look at the rs.Find statement in the code I first pasted you will see the addition use of the single quote mark. I've pasted this below putting an extra space around the single quotes for clarity.
[font="Courier New"]rs.Find "Product = ' " & Field("Sheet1$.Product") & " ' "[/font]
The difficulty is that the Select statement is a string argument in the Open() method. As it is, your reference to the sub-string is translated literally, which won't work. Therefore try the following:
[font="Courier New"]rs.open "SELECT * FROM [Sheet1$] WHERE [PartNo] = '" & Format.NamedSubStrings("btPartNo").Value & "'", objConn, adOpenStatic, adLockOptimistic, adCmdText[/font]
*Note: I haven't tested this so there is a chance I've made an error. At the least it will set you on the right track. If you search in Google you'll probably find plenty of working code examples that will do the trick.0 -
This helped me out! Thank you for your patience. I'm gonna love this software 
0
Iniciar sesión para dejar un comentario.
Comentarios
9 comentarios