Answered

Using VBS to delete specific Columns

(0) Share

Report

Posted on by SCIGUY

So I have an excel file that is generated every day. The issue I am running into when I attempt to upload to SQL server, it needs specific columns deleted. These columns are generated at random, so trimming by their specific column is out. I have VB code I used to delete said columns, and it works fine inside the excel instance, however running the exact script from Power automate does not work. There are about twenty or so columns I must trim.
Excel script example:

Screenshot 2022-03-23 145229 (2).png

Power Automate Example

Screenshot 2022-03-23 145824_LI.jpg

Screenshot 2022-03-23 150021.png

How would I change the power automate VBS script to target that particular excel instance?

Categories:

Power Automate Desktop

I have the same question (0)

All responses (6)

Answers (1)

Sort by

VJR 7,635 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @SCIGUY

For VBScript to work you will need to create an Object with the Excel Application and the Excel Workbook.

VBA has the Excel Object Model integrated within itself and therefore does not need this.

I have added a vbscript code here that interacts with the Excel application which you can make use of.

Having said that, may I know why are you not using the "Delete column from Excel worksheet" in-built action of PAD?

Was this reply helpful? Yes No
SCIGUY 9 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you for the assist. I believe I have it. Will post more and the finished product once I confirm it.

Was this reply helpful? Yes No
SCIGUY 9 on at

Like
a
(0)

Report
Copy link

Link copied!

@VJR

So I got the excel portion working, but I am trying to run the visual basic code that works currently in the excel document itself. However, I receive and error when attempting in PAD:

What would do you think the error could be?

Was this reply helpful? Yes No
Verified answer

VJR 7,635 on at

Like
a
(1)

Report
Copy link

Link copied!

Where did you get the column method from 🙂

The sheet object does not have a method called as "Column".

You can test anything in a VBA editor and go accordingly

You can use the below line to get the cell value of the 1st row and the ith column.

objWorkbook.Sheets("%SheetName%").cells(1,i).value

Was this reply helpful? Yes No
SCIGUY 9 on at

Like
a
(0)

Report
Copy link

Link copied!

@VJR

Thank you for the assist. I eventually found the Workbook.Sheet(%Sheetname%).Column.cells(1,i) last night and spent the rest of that time getting the code together. It's ugly, as there are 30+ If statements, but it works. For some reason, it did not like the %SheetName% variable for some reason, so I used sheets(1). I'll eventually come back around to this and tighten up the code. For now, it works like a charm! Thanks for the help.

Was this reply helpful? Yes No
VJR 7,635 on at

Like
a
(0)

Report
Copy link

Link copied!

okay great. do post back if you need anything, or close the thread if there is nothing further.

Was this reply helpful? Yes No