?
Solved

Open run Macro and Close Excel file plus delete another file

Posted on 2014-04-10
6
Medium Priority
?
664 Views
Last Modified: 2014-04-12
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?
0
Comment
Question by:phoenix55
  • 3
  • 3
6 Comments
 
LVL 65

Accepted Solution

by:
RobSampson earned 2000 total points
ID: 39993286
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
 
LVL 65

Expert Comment

by:RobSampson
ID: 39993289
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
 

Author Comment

by:phoenix55
ID: 39993333
Okay, thanks for the prompt reply Rob, will give it a try today.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:phoenix55
ID: 39996498
Hi Rob,

Gave the code a try out but it needs a line inserted to enable the Macro's, could you advise please?
0
 

Author Closing Comment

by:phoenix55
ID: 39996639
Right, sorted enabled macro's.  Code works brilliantly Rob, much appreciated, thank you.
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 39996754
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question