Solved

Open run Macro and Close Excel file plus delete another file

Posted on 2014-04-10
6
647 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 500 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
excel pivot question 4 41
InStr Function not working properly in macro 3 19
Google Sheets - Artificial Intelligence 2 21
excel help 4 20
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

862 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now