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

(0) Share

Report

Posted on by kiran_g

150

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

Categories:

Power Automate Desktop

I have the same question (0)

All responses (13)

Answers (1)

Sort by

UshaJyothiKasibhotla 225 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
kiran_g 150 on at

Like
a
(0)

Report
Copy link

Link copied!

But I want to delete it by using the Headers

Was this reply helpful? Yes No
UshaJyothiKasibhotla 225 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Agnius Bartninkas Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
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.

Was this reply helpful? Yes No
kiran_g 150 on at

Like
a
(0)

Report
Copy link

Link copied!

not working

Was this reply helpful? Yes No
kiran_g 150 on at

Like
a
(0)

Report
Copy link

Link copied!
not working & i have also given
xlApp.Visible = true
but it's not opening the file

Was this reply helpful? Yes No
Agnius Bartninkas Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
kiran_g 150 on at

Like
a
(0)

Report
Copy link

Link copied!

Yes, i have tried with full path and path variable

Was this reply helpful? Yes No
Agnius Bartninkas Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
kiran_g 150 on at

Like
a
(0)

Report
Copy link

Link copied!

Was this reply helpful? Yes No