Open run Macro and Close Excel file plus delete another file

Hello Experts,

I have bespoke application that exports a CSV file as 'EXPORT.csv' to my desktop, this file is used to update an Excel spreadsheet called 'SPECIFICATION.xlsx' which has a Macro within called  'CSVSETUP'  that will load the 'EXPORT.csv' file to update 'SPECIFICATION.xlsx' - and my application can call a program that should enable me to run this in the background when 'EXPORT.csv' is sent to the desktop.  

What i wish to do is to simply call 'SPECIFICATION.xlsx' and open it to run the Macro, Save 'SPECIFICATION.xlsx'  and Close it when the Macro has finished running  plus Delete 'EXPORT.csv' from the Desktop when this process is completed.  

What would be the easiest method to do this task and how should any code required to do this be written?

Thank you, in advance of any reply?
phoenix55Asked:
Who is Participating?
 
RobSampsonConnect With a Mentor Commented:
Hi, this is untested, but I think it should do the job.

Regards,

Rob.

strExcelFile = Replace(WScript.ScriptFullName, WScript.ScriptName, "") & "Specification.xlsm"
strWBName = Mid(strExcelFile, InStrRev(strExcelFile, "\") + 1)
strMacroName = "MacroName"
strMacroPath = strWBName & "!" & strMacroName

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWB = objExcel.Workbooks.Open(strExcelFile, False, False)
objWB.Application.Run strMacroPath
objWB.Close False
objExcel.Quit

Set objShell = CreateObject("WScript.Shell")
Set objFSO = CreateObject("Scripting.FileSystemObject")
objFSO.DeleteFile objShell.ExpandEnvironmentStrings("%UserProfile%") & "\Desktop\EXPORT.csv", True

Open in new window

0
 
RobSampsonCommented:
That's VBScript code, by the way, so place the .VBS file in the same folder as the Specifications.xlsm file, and it should work.

Regards,

Rob.
0
 
phoenix55Author Commented:
Okay, thanks for the prompt reply Rob, will give it a try today.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
phoenix55Author Commented:
Hi Rob,

Gave the code a try out but it needs a line inserted to enable the Macro's, could you advise please?
0
 
phoenix55Author Commented:
Right, sorted enabled macro's.  Code works brilliantly Rob, much appreciated, thank you.
0
 
RobSampsonCommented:
Thanks for the grade. Typically you need to manually lower the macro security or add the file to Trusted Locations. You probably could do that with a script but it's easier to do it manually.

Rob.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.