cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SCIGUY
Frequent Visitor

Using VBS to delete specific Columns

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?

1 ACCEPTED SOLUTION

Accepted Solutions

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

 

VJR_0-1648202504889.png

 

 

 

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

 

View solution in original post

6 REPLIES 6
VJR
Super User
Super User

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?

 

SCIGUY
Frequent Visitor

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

SCIGUY
Frequent Visitor

@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:

SCIGUY_1-1648150463805.png

 

SCIGUY_0-1648150437976.png

What would do you think the error could be?

 

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

 

VJR_0-1648202504889.png

 

 

 

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

 

SCIGUY
Frequent Visitor

@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.

SCIGUY_0-1648230610991.png

 

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

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Top Solution Authors
Top Kudoed Authors
Users online (1,812)