Using VBA and file name excel variables

[anh.nguyen]

Hi,
I use Ass for file excel input

out_Str_input = Directory.GetFiles(in_ParrentFolder+"Input",“SSC*.xlsx”).OrderByDescending(Function(d) New FileInfo(d).CreationTime).ToList(0).ToString

And invoke VBA as below. How can I use variables instead link =‘D:\RPA\UiPath\ICS\Input[ICS.xlsx]

Sub Del_Rws()
Dim d As Object
Dim a As Variant, b As Variant, itm As Variant
Dim nc As Long, i As Long, k As Long

Sheet2.Range(“A1:A1000”).Formula = “=‘D:\RPA\UiPath\ICS\Input[ICS.xlsx]PLHQ’!D20”
Sheet2.Range(“A1001:A2000”).Formula = “=‘D:\RPA\UiPath\ICS\Input[ICS.xlsx]PLHQ’!E20”

End Sub

[arjunshenoy]

Hi @anh.nguyen

Instead of hardcoding the file path, you can try to pass the variable something like this:

out_Str_input = Directory.GetFiles(in_ParrentFolder+"Input","SSC*.xlsx").OrderByDescending(Function(d) New FileInfo(d).CreationTime).ToList(0)
Del_Rws(out_Str_input)

Sub Del_Rws(file_path as String)
    Dim d As Object
    Dim a As Variant, b As Variant, itm As Variant
    Dim nc As Long, i As Long, k As Long

    Sheet2.Range("A1:A1000").Formula = "='" & file_path & "PLHQ'!D20"
    Sheet2.Range("A1001:A2000").Formula = "='" & file_path & "PLHQ'!E20"
End Sub

Hope this helps,
Best Regards.

[Tuannna1 (Tuan Anh)]

Hi @anh.nguyen

In Sub Del_Rws() call the variables you want for example:
Sub Del_Rws(out_Str_input As String)

Externally, pass arguments to Invoke VBA.

{out_Str_input.ToString}

Regards,
Tuan

[anh.nguyen]

Sub Del_Rws(out_Str_input As String)
Dim d As Object
Dim a As Variant, b As Variant, itm As Variant
Dim nc As Long, i As Long, k As Long

Sheet2.Range(“A1:A1000”).Formula = “='” & out_Str_input & “PLHQ’!D20”
Sheet2.Range(“A1001:A2000”).Formula = “='” & out_Str_input & “PLHQ’!E20”

When run vba in excel not corret

=‘D:\RPA\UiPath\ICS\Input[ICS.xlsxPLHQ]ICS’!D20

[anh.nguyen]

@arjunshenoy

Del_Rws(out_Str_input)

Where I can input this code, put in assign not correct.

[Tuannna1 (Tuan Anh)]

Sheet2.Range("A1:A1000").Formula = "='" & out_Str_input & "PLHQ'!$D$20"
Sheet2.Range("A1001:A2000").Formula = "='" & out_Str_input & "PLHQ'!$E$20"

You must make sure the path, file name, and sheet name are correct.
Why don’t you use the [ write cell ] operation of UiPath ?
Please try again!

Regards,
Tuan

[anh.nguyen]

Yes, Before using var, vba code run correctly but now I want to change var bc the unknown input name file excel.
output correct

=‘D:\RPA\UiPath\ICS\Input[ICS.xlsx]PLHQ’!D20

Use var

=‘D:\RPA\UiPath\ICS\Input[ICS.xlsxPLHQ]ICS’!D20

[jack.chan]

@anh.nguyen

out_Str_input

whats the value of out_Str_input in UiPath studio?? @anh.nguyen

[anh.nguyen]

This is an input Excel file and I want use this var for code vba

[jack.chan]

after this assign add 1 more assign @anh.nguyen

assign
out_str_input = Path.getDirectoryName(out_str_input) + "[" + Path.getFileName(out_str_input) + "]"

and try again

[anh.nguyen]

Hi, still error

=‘D:\RPA\UiPath\ICS[Input[ICS.xlsx]PLHQ]Input[ICS.xlsx]PLHQ’!D20

[jack.chan]

whats your vba to set the formula?

[jack.chan]

delete second assign in UiPath

then replace vba with this

Sub Del_Rws(out_Str_input As String)
  Dim d As Object
  Dim a As Variant, b As Variant, itm As Variant
  Dim nc As Long, i As Long, k As Long

Dim inputWb as Workbook
Set inputWb = Workbooks.Open(out_Str_input)
Dim fso As Object
Set fso = VBA.CreateObject("Scripting.FileSystemObject")
Dim fileName As String
fileName = fso.GetFileName(out_Str_input)

Sheet2.Range("A1:A1000").Formula = "='[" & fileName & "]PLHQ'!D20"
Sheet2.Range("A1001:A2000").Formula = "='[" & fileName & "]PLHQ'!E20"

Set d = CreateObject("Scripting.Dictionary")
  a = Sheets("Sheet2").Range("A2", Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp)).Value
  For Each itm In a
    d(itm) = 1
  Next itm
  With Sheets("Sheet1")
    a = .Range("C2", .Range("C" & Rows.Count).End(xlUp)).Value
    ReDim b(1 To UBound(a), 1 To 1)
    For i = 1 To UBound(a)
      If Not d.exists(a(i, 1)) Then
        k = k + 1
        b(i, 1) = 1
      End If
    Next i
    If k > 0 Then
      Application.ScreenUpdating = False
      nc = .Cells.Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
      With .Range("A2").Resize(UBound(a), nc)
        .Columns(nc).Value = b
        .Sort Key1:=.Columns(nc), Order1:=xlAscending, Header:=xlNo
        .Resize(k).EntireRow.Delete
      End With
      Application.ScreenUpdating = True
    End If
  End With
Sheet1.Range("H2:H2").Formula = "=SUM(D2:D1063)" 'Tong khoi luong
Sheet1.Range("I2:I2").Formula = "=ROUND(SUM(G2:G1063),2)" 'Tong gia

inputWb.Close SaveChanges:=False
End Sub