This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Hi - i've almost got this working, in fact I think it did work, but its not now
I'm using the Event tool to run the below VB code before my workflow. The VB code should open up a saved excel file and run a VBA macro saved within.
I tihnk my problem is with the below code. I borrowed it from another post and it seems to have too many arguments. I want to run the macro ChangeSheetName, which itself will open many files in a folder, so I dont see why I have GetFolder in the VB code.
Can anyone help with the code to change it to what I need - open the excel - run the macro - when the macro is complete (or say 10sec later) close the Excel (doesnt need to save).
dim objExcel set objExcel = CreateObject("Excel.Application") objExcel.Visible = TRUE objExcel.DisplayAlerts = FALSE
Set objWorkbook = objExcel.Workbooks.Open(Path1, 0, True)
Dim fso, path, file, recentDate, recentFile Set fso = CreateObject("Scripting.FileSystemObject") Set recentFile = Nothing For Each file in fso.GetFolder("\\uug.vcm.cc\Domestic_Retail\CollectionsAnalyticsTeam\DCA\Invoices\Input\").Files If (recentFile is Nothing) Then Set recentFile = file ElseIf (file.DateLastModified > recentFile.DateLastModified) Then Set recentFile = file End If Next
If recentFile is Nothing Then WScript.Echo "no recent files" Else objExcel.Workbooks.Open recentFile End If
thanks for the suggestions but it still wont work. Tried about a dozen different ideas found on the internet, none of those work, its driving me mad. Just run the macro, which works fine when I run it in excel, but no matter what vb code i use it will not work.
sod it i'll just run the macro manually each time. The infuriating thing is, I thought I had it working.
This is the solution to running VBScript from Event. The below VBScript will open the named excel and execute the named macro.
Thanks to John_W MrExcel VIP for writing this - he is ace.
Dim objExcel Set objExcel = CreateObject("Excel.Application") With objExcel .Workbooks.Open "\\uug.vcm.cc\Domestic_Retail\CollectionsAnalyticsTeam\StephenAllan\Alteryx\VBAMacros\AlteryxMacros.xlsm" .Visible = True .Run "Rename" .ActiveWorkbook.Close True .Quit End With
So as above, call this from the Event tab, having saved it using Notepad as a .vbs file.