Modification on a vb script

Help Activities
,
1

Hello All,
Here I have a VB script to create the drop down value in excel
Dim ws As Microsoft.Office.Interop.Excel.Worksheet
Dim rng As Microsoft.Office.Interop.Excel.Range

excel = New Microsoft.Office.Interop.Excel.ApplicationClass
wb = excel.Workbooks.Open(Filename)
excel.Visible=False

ws=CType(wb.Sheets(TargetSheet),Microsoft.Office.Interop.Excel.Worksheet)
rng=CType(ws.Range(PlaceRange),Microsoft.Office.Interop.Excel.Range)

With rng
.Validation.Add(Microsoft.Office.Interop.Excel.XlDVType.xlValidateList,
Microsoft.Office.Interop.Excel.XlDVAlertStyle.xlValidAlertStop,
Microsoft.Office.Interop.Excel.XlFormatConditionOperator.xlBetween,
ValidationFormula)
.Validation.IgnoreBlank = True
.Validation.InCellDropdown = True

End With

wb.Save
wb.Close

Here instead of formula I want to directly give the values as string that should be shown in excel

2

Hi @Demo_User

Could you try with the below code.

Dim ws As Microsoft.Office.Interop.Excel.Worksheet
Dim rng As Microsoft.Office.Interop.Excel.Range

excel = New Microsoft.Office.Interop.Excel.ApplicationClass
wb = excel.Workbooks.Open(Filename)
excel.Visible = False

ws = CType(wb.Sheets(TargetSheet), Microsoft.Office.Interop.Excel.Worksheet)
rng = CType(ws.Range(PlaceRange), Microsoft.Office.Interop.Excel.Range)

Dim dropDownValues As String = "Option 1, Option 2, Option 3, Option 4" ' Replace this with your desired drop-down values

With rng
    .Validation.Add(Microsoft.Office.Interop.Excel.XlDVType.xlValidateList,
                    Microsoft.Office.Interop.Excel.XlDVAlertStyle.xlValidAlertStop,
                    Microsoft.Office.Interop.Excel.XlFormatConditionOperator.xlBetween,
                    dropDownValues)
    .Validation.IgnoreBlank = True
    .Validation.InCellDropdown = True
End With

wb.Save
wb.Close

Hope it helps!!

3

Hi
How to iterate through each row of column
If column value matches certain value
then do certain steps
If not do some other steps
Using vb script
Could u please help

4

Hi @Demo_User

Find the below vb code

Imports System.Data

Public Sub IterateThroughRowsWithVBScript()
    ' Assuming you have a DataTable named dtSource with the desired data

    ' Iterate through each row in the DataTable
    For Each row As DataRow In dtSource.Rows
        ' Replace "YourColumnName" with the actual name of the column you want to check
        Dim columnValue As String = row("YourColumnName").ToString()

        ' Check if the column value matches a certain value
        If columnValue = "CertainValue" Then
            ' Perform certain steps if the condition is true
            ' For example, log a message, update values, or call another workflow
            LogMessage("Column value matches CertainValue: " & columnValue)
            ' Add your actions here
        Else
            ' Perform other steps if the condition is false
            ' For example, log a different message or take alternative actions
            LogMessage("Column value does not match CertainValue: " & columnValue)
            ' Add your actions here
        End If
    Next
End Sub

Public Sub LogMessage(message As String)
    ' Custom method to log a message (replace with your own logging mechanism)
    ' For example, you can use the WriteLine activity in UiPath to log the message to the Output panel
    Console.WriteLine(message)
End Sub

Hope it helps!!

5

Not a data table I should directly work with excel

6

Then you have to use the excel activities to iterate the rows use the For each row activity.
Use If condition to check the conditions.

Hope it helps!!

7

Dim excel As Microsoft.Office.Interop.Excel.Application
Dim wb As Microsoft.Office.Interop.Excel.Workbook
Dim ws As Microsoft.Office.Interop.Excel.Worksheet
Dim rng As Microsoft.Office.Interop.Excel.Range
Dim DropDownValue As String = DropDownValues
Dim DropDownPastDue As String = DropDownPastDues
Dim rw As Range
Dim rowcount As Integer

excel = New Microsoft.Office.Interop.Excel.ApplicationClass
wb = excel.Workbooks.Open(Filename)
excel.Visible=False
ws=CType(wb.Sheets(TargetSheet),Microsoft.Office.Interop.Excel.Worksheet)
rng=CType(ws.Range(PlaceRange),Microsoft.Office.Interop.Excel.Range)
For Each rw In ws
If ws.Range(“S”+rowcount).Value = “Sucse” Then
With rng
.Validation.Delete
.Validation.Add(Microsoft.Office.Interop.Excel.XlDVType.xlValidateList,Microsoft.Office.Interop.Excel.XlDVAlertStyle.xlValidAlertStop,Microsoft.Office.Interop.Excel.XlFormatConditionOperator.xlBetween,)
.Validation.IgnoreBlank = True
.Validation.InCellDropdown = True
End With
Else
With rng
.Validation.Delete
.Validation.Add(Microsoft.Office.Interop.Excel.XlDVType.xlValidateList,Microsoft.Office.Interop.Excel.XlDVAlertStyle.xlValidAlertStop,Microsoft.Office.Interop.Excel.XlFormatConditionOperator.xlBetween,DropDownPastDues)
.Validation.IgnoreBlank = True
.Validation.InCellDropdown = True
End With
rowcount = rowcount+1

wb.Save
wb.Close

Here I need some modifications
For the for each

8

Check the below one.

Imports System.Data

Public Sub ApplyDropdownValidation()
    Dim excel As Microsoft.Office.Interop.Excel.Application
    Dim wb As Microsoft.Office.Interop.Excel.Workbook
    Dim ws As Microsoft.Office.Interop.Excel.Worksheet
    Dim rng As Microsoft.Office.Interop.Excel.Range
    Dim DropDownValue As String = DropDownValues
    Dim DropDownPastDue As String = DropDownPastDues

    Try
        ' Initialize Excel objects
        excel = New Microsoft.Office.Interop.Excel.Application
        wb = excel.Workbooks.Open(Filename)
        excel.Visible = False
        ws = CType(wb.Sheets(TargetSheet), Microsoft.Office.Interop.Excel.Worksheet)
        rng = CType(ws.Range(PlaceRange), Microsoft.Office.Interop.Excel.Range)

        ' Get the last used row in the Excel sheet (assuming the "S" column has values in each row)
        Dim lastRow As Integer = ws.Cells(ws.Rows.Count, "S").End(Microsoft.Office.Interop.Excel.XlDirection.xlUp).Row

        ' Loop through each row in the Excel sheet
        For rowcount As Integer = 1 To lastRow
            If ws.Range("S" & rowcount).Value = "Sucse" Then
                ' Apply dropdown validation for "Sucse" condition
                With rng.Validation
                    .Delete()
                    .Add(Microsoft.Office.Interop.Excel.XlDVType.xlValidateList, _
                         Microsoft.Office.Interop.Excel.XlDVAlertStyle.xlValidAlertStop, _
                         Microsoft.Office.Interop.Excel.XlFormatConditionOperator.xlBetween, _
                         DropDownValue)
                    .IgnoreBlank = True
                    .InCellDropdown = True
                End With
            Else
                ' Apply dropdown validation for other conditions (DropDownPastDues)
                With rng.Validation
                    .Delete()
                    .Add(Microsoft.Office.Interop.Excel.XlDVType.xlValidateList, _
                         Microsoft.Office.Interop.Excel.XlDVAlertStyle.xlValidAlertStop, _
                         Microsoft.Office.Interop.Excel.XlFormatConditionOperator.xlBetween, _
                         DropDownPastDue)
                    .IgnoreBlank = True
                    .InCellDropdown = True
                End With
            End If
        Next

        ' Save and close the workbook
        wb.Save()
        wb.Close()

    Catch ex As Exception
        ' Handle any exceptions that may occur during the execution
        ' You can log the error, show a message box, or take other appropriate actions.
        ' For example: MessageBox.Show("An error occurred: " & ex.Message)
    Finally
        ' Clean up Excel objects
        If wb IsNot Nothing Then
            System.Runtime.InteropServices.Marshal.ReleaseComObject(wb)
        End If

        If excel IsNot Nothing Then
            excel.Quit()
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excel)
        End If
    End Try
End Sub

Hope it helps!!

9

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.