Vb Script & Dropdown List & Excel
Hello
I have a problem
Here is my excel file - file.xls
a b
1 barcode product
2 12345 table
3 78901 chear
In bartender I connected to the data source (my excel file).
So, I try to create a new control - DropDown List who could let user to choose the name of product (from column B in my Excel file)before printing and of course print selected name of product.
I created this control and now I`m trying to create a script (on VBS), that could get a list of product for this control from my excel file.
So, this script is a main problem. I have not enough skills to make it.
Also, it will be very nice if after choosing the name of the product it`s barcode also printed (accordingly the choise of product).
Please, could you help me?
I have a problem
Here is my excel file - file.xls
a b
1 barcode product
2 12345 table
3 78901 chear
In bartender I connected to the data source (my excel file).
So, I try to create a new control - DropDown List who could let user to choose the name of product (from column B in my Excel file)before printing and of course print selected name of product.
I created this control and now I`m trying to create a script (on VBS), that could get a list of product for this control from my excel file.
So, this script is a main problem. I have not enough skills to make it.
Also, it will be very nice if after choosing the name of the product it`s barcode also printed (accordingly the choise of product).
Please, could you help me?
0
-
Here is an example of the VB script that you'll need to use. Obviously you'll need to make modifications for the path/name of the Excel file, the name of the worksheet and column name.
Functions and Subs
[code]
dim objConn
dim strConn
dim rs
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\items and item numbers.xls';Extended Properties=""Excel 8.0;HDR=YES;"""
Public Function GetNames()
Dim strNames
Set objConn = CreateObject("ADODB.Connection")
objConn.Open strConn
Set rs = CreateObject("ADODB.Recordset")
Set rs = objConn.Execute("SELECT * FROM [Sheet1$]")
strNames = ""
rs.MoveFirst()
do while NOT rs.EOF
strNames = strNames + rs.fields("Item") + vbCrLf
rs.MoveNext()
loop
GetNames = strNames
End Function
[/code]
OnFillList
[code]
Value = GetNames()
[/code]0 -
Great!!It works!!
Thank you so much.
But there`s still one more question.
After choosing the item it`s barcode (from column A in my Excel file) should also printed (accordingly the choise of product).
Now if I select one item in preview I see a label with same item but with different barcode.
The task is:
- there`s a label with 2 objects - text and barcode
- before preview I can select an item
- in preview will be 1 label with my selected item and it barcode (from my Excel file)
Would not it be rude and insolent to ask for help again ?0 -
The main problem here is that the user prompt form currently lacks an on data change event that could be used to update other prompted items based on the selection of another. However, if we were to assume that only the one item is prompted you could then use an event based VB script in the data source of the other object to connect to the same Excel data, find the record using the field value of the prompted object, and then set the value of this second object from another field in the same record.
Of course much of the above can be achieved with just a regular database query using a query prompt. Any reason why you are not using that? Is it because query prompts are only supported by a text input form control?0 -
Thank you for your quick answer.
Now I have one TextBox with data from my dropdown list (I used your example - thank you once more) on my label.
Then,I added a barcode on my label. It gets data also from dropdown list. And I get zeros in preview.
So, I have to use script for barcode object that will get correct data from my excel file - record that has my selected item.
And how can I use data from another object ?
1) find record
use rs.Find ? how can I find a whole record,even I know a part of it ?
2) find column
use rs.Fields ? I have to find a first column in the found record ?
Thank you in advance.0 -
Yes the rs.Find with find record/s based on the criteria you feed it. The rs.Fields item allows you to extract the value from a field. Note that when you use the "value = " expression you'll be setting the value of the current sub-string with whatever is on the other side of the expression.
Example:
value = rs.fields("AnotherItem")
To share sub-string values between other objects you should first give your data source a "Share Name", once named you can then reference it from your scripts as they are treated like global variables for the label format.
Example:
Having named a sub-string "btTest" in the data source of another object, you can then reference it with the below expression.
Format.NamedSubStrings("btTest").Value0 -
Hi Ian,
I also have used the same VBScript to connect to a second Excel File.
My format requires 2 independent Excel files to be used - Products and Customers. The customers file has 2 columns, NAME and ADDRESS
The Products table is linked using the Add Database Wizard, and Customers is done using the VBScript.
I have some questions about this:
1. Duplicate values - I get an error in the vbscript if the customers table has duplicate values. How do I correct this?
2. I currently can show the Customer Name from the Pull Down list and print it on my format. How do I get other data from the Excel File to print, for example the customers address.
thanks.0 -
Off the top of my head I'm not sure on 1, try a google search as normally there are lots of code examples out there. On 2, you could run the script in one shared object to pull multiple concatenated fields out of the table and then from other objects refer to this shared name and get the field that you want. 0 -
[quote name='Ian C - Seagull Support' timestamp='1328794257' post='1690']
Here is an example of the VB script that you'll need to use. Obviously you'll need to make modifications for the path/name of the Excel file, the name of the worksheet and column name.
Functions and Subs
[code]
dim objConn
dim strConn
dim rs
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\items and item numbers.xls';Extended Properties=""Excel 8.0;HDR=YES;"""
Public Function GetNames()
Dim strNames
Set objConn = CreateObject("ADODB.Connection")
objConn.Open strConn
Set rs = CreateObject("ADODB.Recordset")
Set rs = objConn.Execute("SELECT * FROM [Sheet1$]")
strNames = ""
rs.MoveFirst()
do while NOT rs.EOF
strNames = strNames + rs.fields("Item") + vbCrLf
rs.MoveNext()
loop
GetNames = strNames
End Function
[/code]
OnFillList
[code]
Value = GetNames()
[/code]
[/quote]
I'm using the code exactly as it is posted here (with some changes for file naming and excel version). For some reason I get
[quote]
OnFillList(Line 6): : Type mismatch: 'Value'
[/quote]
when I finish.
My excel file is laid out as
1 Title Title Title
2 Data Data Data
3 Data Data Data
4 Data Data Data
and so on.
I'll admit I'm very new to VB, but what am I missing?0 -
I trust that you are putting the correct code into the two different events (Functions and Subs & OnFillList) as titled?
As a test change the code in "Functions and Subs" to the following:
[code]
Public Function GetNames()
GetNames = "Hello World!" + vbCr + "This is a test."
End Function
[/code]
Does this then result in a two item list? If yes then you need to look at the code used to connect to your Excel data file. If no, then you've made a more fundamental mistake somewhere along the line.0 -
[quote name='Ian C - Seagull Support' timestamp='1344620876' post='3054']
I trust that you are putting the correct code into the two different events (Functions and Subs & OnFillList) as titled?
As a test change the code in "Functions and Subs" to the following:
[code]
Public Function GetNames()
GetNames = "Hello World!" + vbCr + "This is a test."
End Function
[/code]
Does this then result in a two item list? If yes then you need to look at the code used to connect to your Excel data file. If no, then you've made a more fundamental mistake somewhere along the line.
[/quote]
The test code worked. I'm using Excel 2010 on 32-bit Windows 7. This is the line I'm using to connect to the Excel data file
[code]
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='Z:\Folder\Bartender\Label Info File.xlsx';Extended Properties=""Excel 12.0;HDR=YES;"""[/code]
Can you see anything that is wrong with it?0 -
This website seems like a useful resource for connection strings:
http://www.connectionstrings.com/excel0
Vous devez vous connecter pour laisser un commentaire.
Commentaires
11 commentaires