Hi I need to run different vba scripts on different worksheets in the same workbook. Basically, each worksheet has it's own vba script which triggers an ODBC connection then updates the worksheet from a database. I've been able to get one vba script to run on one sheet and save as… no problem, but can't get anymore than one to run. here's the code I'm using
$excel = new-object -comobject excel.application
$excelFiles = Get-ChildItem -Path C:\test\Daily_update.xlsm
$Date = (Get-Date -Format dd-MM-yy)
Foreach($file in $excelFiles)
{
$workbook = $excel.workbooks.open($file.fullname)
$worksheet = $workbook.worksheets.item(2)
$excel.Run("Test_Refresh")
$workbook.saveAs("C:\test\Daily_update_$Date.xlsm")
$workbook.close()
}
$excel.quit()
When I try to add other worksheets and vba scripts it doesn't work at all.
Best Answer
OK after walking away from the problem for a little while, a little more coffee and applying some logic. I got the thing to work. So just in case you need a script to do what I was after and run a specific macro on a specific worksheet in the same workbook, here it is.
Just to add a little more to this. After some trial and error I found that the following code was far more efficient at running than the above. Also I noticed that if there were only 3 worksheets it was fine but any more started to raise errors, especially when calling sheets. But this problem disappeared when the workbook was opened and visible.