After Running Vb Script, Excel Drop Down List Not Listing Values S’abonner

0
Avatar
Legacy Poster

Hi,

 

I'm using below lines to open excel sheet and read the contents of excel sheet and close it. I've created a drop down list using data validation feature in excel. After running the vb script, I'm unable to see the data validation (drop-down list) in the excel sheet, only the selected value of drop-down list is present in the excel and all other values along with the drop-down in the excel sheet is not shown

 

Set Excel = CreateObject("Excel.Application")
Excel.Workbooks.Open "E:\LoginSheet.xls"


msgbox Excel.Cells(2, 1)

 
'Close the Excel
Excel.ActiveWorkbook.Save
Excel.Quit
Set Excel = Nothing

 

Any help is appreciated

 

Regards,

Anil

6 commentaires

0
Avatar
Ian Cummings
Modérateur
Actions pour les commentaires Permalien

I'm not exactly sure what you are wanting here.  Do you wish to fill a data entry form control with a list of items coming from and Excel file?  If so the below example VB script should set you on the right path.

 

Functions and Subs:

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

 

 

 

OnFillList:

 

Value = GetNames()
0
Avatar
Legacy Poster
Actions pour les commentaires Permalien

This might be slightly off topic but it is related to your answer and I have not found anything similar to this.

 

We are in the process of moving over to BT and have had some of our labels created for us, the rest we have created based on those already created.  I'm completly new to Bartender and my VB is very limited (limited to borrowing others scripts)

 

When using lists to select data from Excel spreadsheets the above script was used except for the line :-

 

strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='{filenameandpath}.xlsx';Extended Properties=""Excel 12.0;HDR=YES;"""

 

We have a need to limit the selection based on the result of another list.  We are already using the database for other data and these won't link together, the number of options would be too great to update!

 

If I give you some detail it might be more helpfull.

 

We pack fruit and have to show the Grower and the Country Grown on the label.

 

What we don't want is for the employees printing our labels to put a Grower in that is not from the Country they are grown in.  IE Country Grown = France and then entering an English Grower. (This has happened a number of times)

 

It would be very useful to filter the list based on the country so I have set up a test list of fake growers with their country next to them to test this on

 

The first problem encountered was when I use the above script with a spreadsheet that has more than one colomn it errors out before I even go about putting a Where in the select statement.

 

The second problem would be to get the Where statement to work but due to the first one I haven't tried much on this one yet!

 

Thanks in advance for any help you can give.

 

 

Error Message is #6900 OnFillList(Line2): : Type mismatch: "Value"

0
Avatar
Ian Cummings
Modérateur
Actions pour les commentaires Permalien

Sydknee: This is not possible to do within BarTender as things stand due to the lack of on data change events in the data entry form.

 

The solution would be to develop a stand-alone front-end application, using something like VB.NET, to take in the data entry, complete with data table look-ups, and then control a BarTender process via automation for printing.  See the below white papers to get you started.

 

http://www.bartenderbarcodesoftware.com/label-software/whitepapers/Controlling-Barcode-Label-Software-using-C-Sharp-and-VB.NET(English).pdf

 

http://www.bartenderbarcodesoftware.com/label-software/whitepapers/Controlling-Barcode-Label-Software-using-ActiveX-Automation.pdf

0
Avatar
Legacy Poster
Actions pour les commentaires Permalien

Hello Ian ,

 

Is it possible to have a sample from you in .NET ( C# if possible ) to achieve the above ?

 

The samples installed with your SDK are not very helpful in order to work with data variables .

 

Thank you ,

Stelios

0
Avatar
Ian Cummings
Modérateur
Actions pour les commentaires Permalien

Sorry, but I don't have an example to give you.  Perhaps someone else can help.

0
Avatar
Legacy Poster
Actions pour les commentaires Permalien

First of all Sorry for not coming back to forum for some personal reason...

 

The problem what I'm facing is I've added drop-down list in the excel sheet for ex: one, two, three, four; these 4 words will show as drop-down in one cell of excel sheet.

I've selected a value from the drop down list in the excel sheet and begin qtp execution, once qtp finishes execution, when I go back to excel sheet, the drop down is missing, I just see the value which is selected is present, when I click on arrow button to see other three values from drop down, it doesn't show any values except the value which is there in the cell...

 

What could be the issue?

 

 

Sorry, but I don't have an example to give you.  Perhaps someone else can help.

 

Vous devez vous connecter pour laisser un commentaire.