How to delete the multiple columns by using VB Script in Power automate desktop

[kiran_g]

I have tried with one VB script it is working on the VB editor, when I use it in the workflow it's not working.

Sub DeleteColumnsByHeaderName()

    Dim HeaderNames() As Variant

    Dim HeaderName As Variant

    Dim LastColumn As Long

    Dim ColumnIndex As Long

    Dim Found As Boolean

set xlApp = CreateObject("Excel.Application")

set xlWbk = xlApp.Workbooks.Open("path")

set xlWksht = xlWbk.Worksheets("RPA")

    xlApp.Visible = true

HeaderNames = Array("First Name", "Last Name", "Company Name")

    ' Find the last column in the active sheet

    LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column

    ' Loop through the header names

    For Each HeaderName In HeaderNames

        Found = False

        ' Search for the header name in the first row

        For ColumnIndex = 1 To LastColumn

            If Cells(1, ColumnIndex).Value = HeaderName Then

                Found = True

                ' Delete the entire column if the header name is found

                Columns(ColumnIndex).Delete

                Exit For

            End If

        Next ColumnIndex

        ' Display a message if the header name was not found

        If Not Found Then

            MsgBox "Header '" & HeaderName & "' not found.", vbExclamation

        End If

    Next HeaderName

End Sub

[UshaJyothi20]

Set objExcel = CreateObject("Excel.Application")
Set wb = objExcel.Workbooks.Open("File Path")
objExcel.Visible = True
wb.Worksheets("Sheet1").Range("A:A").EntireColumn.Delete
wb.Worksheets("Sheet1").Range("D:D").EntireColumn.Delete
wb.Save
wb.Close

Try this

Hope this helps

Usha

[kiran_g]

But I want to delete it by using the Headers

[UshaJyothi20]

Hi @kiran_g 

Try this

' Assuming headersToDelete is an array containing headers to delete
Dim headersToDelete
headersToDelete = Array("Header1", "Header2", "Header3")

' Assuming sheet is the name of your Excel sheet
Set excelApp = GetObject("Excel.Application")
Set excelWorkbook = excelApp.Workbooks.Open("Path to Your Excel File")
Set excelSheet = excelWorkbook.Sheets("Sheet1")

' Loop through headersToDelete and delete corresponding columns
For Each header In headersToDelete
On Error Resume Next
columnIndex = Application.Match(header, excelSheet.Rows(1), 0)
On Error GoTo 0

If Not IsError(columnIndex) Then
excelSheet.Columns(columnIndex).Delete
End If
Next

' Save and Close Excel
excelWorkbook.Save
excelWorkbook.Close
excelApp.Quit

[Agnius]

You're not referencing the worksheet object next to your Cells and Columns objects. Try this:

Sub DeleteColumnsByHeaderName()
    Dim HeaderNames() As Variant
    Dim HeaderName As Variant
    Dim LastColumn As Long
    Dim ColumnIndex As Long
    Dim Found As Boolean
 
set xlApp = CreateObject("Excel.Application")
set xlWbk = xlApp.Workbooks.Open("path")
set xlWksht = xlWbk.Worksheets("RPA")
    xlApp.Visible = true
 
HeaderNames = Array("First Name", "Last Name", "Company Name")
 
    ' Find the last column in the active sheet
    LastColumn = xlWksht.Cells(1, Columns.Count).End(xlToLeft).Column
 
    ' Loop through the header names
    For Each HeaderName In HeaderNames
        Found = False
 
        ' Search for the header name in the first row
        For ColumnIndex = 1 To LastColumn
            If xlWksht.Cells(1, ColumnIndex).Value = HeaderName Then
                Found = True
                ' Delete the entire column if the header name is found
                xlWksht.Columns(ColumnIndex).Delete
                Exit For
            End If
        Next ColumnIndex
 
        ' Display a message if the header name was not found
        If Not Found Then
            MsgBox "Header '" & HeaderName & "' not found.", vbExclamation
        End If
    Next HeaderName
End Sub

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.

[kiran_g]

not working 

[kiran_g]

 not working & i have also given 

xlApp.Visible = true

but it's not opening the file 

[Agnius]

Do you actually provide the path to the file instead of "path" in the script there?

[kiran_g]

Yes, i have tried with full path and path variable

[Agnius]

Can you share the final script in there? Also, is there any error in %ScriptError% when you run it?

[kiran_g]

[Agnius]

Try putting the %f_path% variable in double quotes there as follows:

set xlWbk = xlApp.Workbooks.Open("%f_path%")

Also, please copy the actual contents of the error out, as it's not visible in the screenshot.

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.

[kiran_g]

still not working.

Error: 

C:\Users\****\AppData\Local\Temp\Robin\yx2efnaewvz.tmp(2, 23) Microsoft VBScript compilation error: Expected end of statement

[kinuasa · Accepted solution]

The code is written in VBA. Please change it to VBScript.

Call DeleteColumnsByHeaderName

Private Sub DeleteColumnsByHeaderName()
  Dim xlApp 'Excel.Application
  Dim xlWbk 'Excel.Workbook
  Dim xlWksht 'Excel.Worksheet
  Dim HeaderNames, HeaderName
  Dim LastColumn
  Dim ColumnIndex
  Dim Found
  Const xlToLeft = -4159

  Set xlApp = CreateObject("Excel.Application")
  Set xlWbk = xlApp.Workbooks.Open("%f_path%")
  Set xlWksht = xlWbk.Worksheets("RPA")
  xlApp.Visible = True
  HeaderNames = Array("First Name", "Last Name", "Company Name")

  'Find the last column in the active sheet
  LastColumn = xlWksht.Cells(1, xlWksht.Columns.Count).End(xlToLeft).Column
  
  'Loop through the header names
  For Each HeaderName In HeaderNames
    Found = False

    'Search for the header name in the first row
    For ColumnIndex = LastColumn To 1 Step -1
      If xlWksht.Cells(1, ColumnIndex).Value = HeaderName Then
        Found = True
        'Delete the entire column if the header name is found
        xlWksht.Columns(ColumnIndex).Delete
        Exit For
      End If
    Next

    'Display a message if the header name was not found
    If Not Found Then
      MsgBox "Header '" & HeaderName & "' not found.", vbExclamation
    End If
  Next
End Sub