Hi All,
Stuck on this scenario for ages. I'm creating a "Run VBscript" action to loop through various rows of images present in an Excel worksheet, then name them and save them to a folder. Hoping to use the variables I've got in my PAD flow
ImageRows represent the rows of images, each row of images is separated by empty cells.
Any help would be appreciated, as this current script keeps giving me wierd syntax errors like:
\AppData\Local\Temp\Robin\vvcrshifp1j.tmp(17, 11) Microsoft VBScript compilation error: Expected identifier
Here are the PAD variables:
%ImagesPath% = string = C:\Users\Automation\Images
%FilePath% = string = C:\Users\Automation\File1.xlsx
%ImageRowCount% = integer = 8
%ImageRowList% = list = ["ImageRow1", "ImageRow2"]
%ImageNameList% = list = ["image1", "image2, "image3"]
Here's my script:
Option Explicit
Dim xlApp, xlBook, sh, pic, i, path, ImageRowCount, row, startRow, endRow
Dim rng, intersectRange, excelFilePath, ImageRowList, imageNames
' Get the path where you want to save the images from the Power Automate variable
path = "%ImagesPath%"
' Get the path to the Excel file from the Power Automate variable
excelFilePath = "%FilePath%"
' Get the names of the image rows from the Power Automate variable
ImageRowList = %ImageRowList%
' Get the names of the images from the Power Automate variable
ImageNameList = %ImageNameList%
i = 0 ' Initialize the counter
' Create a new instance of Excel
Set xlApp = CreateObject("Excel.Application")
' Open the workbook
Set xlBook = xlApp.Workbooks.Open(excelFilePath)
' Set the worksheet that contains the images
Set sh = xlBook.Sheets("Sheet1")
' Loop through each image row
For imagerow = 1 To %ImageRowCount%
' Calculate the start and end rows for the current imagerow
startRow = 7 + (imagerow - 1) * 38
endRow = startRow + 30
' Loop through each picture in the worksheet
For Each pic In sh.Pictures
' Check if the picture is within the specified range
Set rng = sh.Range("C" & startRow & ":HA" & endRow)
Set intersectRange = xlApp.Intersect(pic.TopLeftCell, rng)
If Not intersectRange Is Nothing Then
' Save the picture as a .png file
pic.Select
pic.CopyPicture
With xlApp.Charts.Add
.Paste
.Export path & ImageRowList(Row - 1) & "_" & ImageNameList(i Mod 13) & ".png"
.Delete
End With
i = i + 1 ' Increment the counter
End If
Next
Next imagerow
