Running VB code in an Event to execute VBA code in a macro

[SteAllan]

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

Path1="\\uug.vcm.cc\Domestic_Retail\CollectionsAnalyticsTeam\StephenAllan\Alteryx\AlteryxMacros.xlsm"

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

objExcel.Run("AlteryxMacros.xlsm!ChangeSheetName")

objWorkbook.Saved=False

objExcel.quit

So the above is saved on my drive as "RunAlteryxMacroChangeSheetName.vbs"

vba macro code within the excel

Sub ChangeSheetName()
Dim strPathh As String, strFile As String, counter As Long

strPath = "\\uug.vcm.cc\Domestic_Retail\CollectionsAnalyticsTeam\DCA\Invoices\Input\"
strFile = Dir(strPath & "*.xl*")

Application.ScreenUpdating = False
Do While strFile <> ""
With Workbooks.Open(strPath & strFile)
.Sheets(1).Name = "Sheet1"
.Close SaveChanges:=True
End With
counter = counter + 1
strFile = Dir
Loop
Application.ScreenUpdating = True

MsgBox counter & " files altered. ", vbInformation, "Process Complete"

End Sub

The macro code does what I want it when it runs from the excel, I just cant get it to run via the event calling the vb code

Any help would be much appreciated.

Thanks

Ste

[danilang]

Hi @SteAllan 

Try removing the extra looping part from the first script.  It's been a while, but I think you may need to change macro path to 

modulename.ChangeSheetName as well

dim objExcel
set objExcel = CreateObject("Excel.Application")
objExcel.Visible = TRUE
objExcel.DisplayAlerts = FALSE

Path1="\\uug.vcm.cc\Domestic_Retail\CollectionsAnalyticsTeam\StephenAllan\Alteryx\AlteryxMacros.xlsm"

Set objWorkbook = objExcel.Workbooks.Open(Path1, 0, True)

objExcel.Run("AlteryxMacros.xlsm!ChangeSheetName") <-- change to modulename.ChangeSheetName

objWorkbook.Saved=False

objExcel.quit

 Dan

[SteAllan]

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.

[SteAllan]

Ok so i tried to approach this from a different direction, while having Events run VB to run a VBA macro in an excel is ultimately very useful for a number of reasons, I still cant get it to work.

For this particular problem, just in case this helps anyone, I solved it very easily using the Crew Macros addons.

Turns out there's a XLSX reader maco in there which replaced my Batch Macro. The Crew Macro will be a Batch Macro itself I assume, just a more powerful one than which I was using.

The Crew Macro "Wildcard XLSX Input" imports all sheets of all excels in a specified folder, regardless of sheet names, file names, or schemas.

It just requires the data you want to have the same Column Names. It solved my problem, for now at least.

Getting an Event to run VB code to correctly open my Macro containing Excel file and run a specified macro, remains a challenge to overcome on another day. Stupid bloody VB code.....

[SteAllan · Accepted solution]

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. 

[AK90]

hey, I am stuck at the same point. I tried utilizing your code but while running it in Alteryx it is giving error at line 5 where I am providing the path of the macro workbook. Below is the code. Can you suggest what has gone wrong

dim objExcel
set objExcel = CreateObject("Excel.Application")
With objExcel
.Workbooks.Open
"C:\Users\Ankit.Kukreja1\Downloads\VBA_Workflow\General Formatting Template_v1.xlsm"
.Visible = True
.Run "General_Formatting"
.ActiveWorkbook.Close True
.Quit
End With

[SteAllan]

Hi - its been a long time since i looked at this, all i can think of is your path name needs to be changed to the UNC version.

If you copy the path into alteryx on say a input tool, then go to Options - Advanced Options - Workflow dependancies, it will then show all the paths in the flow.

The one you've copied into the input tool will be there.

Click on UNC

Does that re write the path? If so, copy that address out and use it in the vb code.

Also i did read in someone elses post about this stuff sometimes spaces in the address can be an issue. First rename your Alteryx file without any spaces in the title. Probbaly not the issue but worth changing just in case.

Good luck with it.

[AK90]

hey, I figured it out later (with no give up attitude). Did some tweak in the vbscript lines not sure though why prior one which was mentioned in your comment did not work out. Anyway thanks!

[gsarcona]

@AK90 how did you tweak the VB code? I am getting an error at set objExcel = CreateObject("Excel.Application"). Did doing something with this part fix it for you?