community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
#SANTALYTICS

The highly anticipated Alteryx Community tradition is back! We hope you'll join us!

Learn More
We will be upgrading the Gallery to our 2019.4 release this Saturday, December 7th beginning at 9:00am MT. We expect the outage to take last approx. 2.5 hours.
SOLVED

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

Meteoroid

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"

 

SteAllan_0-1574182362759.png

 

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

Nebula
Nebula

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

Meteoroid

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.

Meteoroid

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

Meteoroid

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. 

 

 

Labels