CODEWITHSUNDEEP
excelvbapowershellvbscriptautomation
AudioTroubler
AudioTroubler

Reputation: 69

Autorun Excel with VBA script, and auto insert a file based on a date mask

So I have an excel file, which is basically a table. I have another excel file with a VBA script which is used as a converter for that Excel file to search for specific entries and output them in text.

I've been trying to automate it, but I can't come up with a solution.

This is what the original Excel looks like: enter image description here

This is how the "converter" looks like enter image description here

It works like: We open it, click the button, windows pops up, we select the file which has always the same path, and the name of the file is always tomorrows date. It then places the "converted" file into path that has been saved before.

This is what the "converted" version looks like: enter image description here

So as I mentioned before, I'm looking to automate this step, as this is one of the many mundane tasks that is needed to be performed on daily basis.

I was wondering what my options are.

Maybe it is possible to make the input file get selected automatically? Or maybe there is a way in which I can extract the VBA script from excel and automate it using Powershell, in which I can set the filenames to be tommorows dates.

Any help is greatly appreciated. We tried contacting the contractor who made the converter scripts, but we can't reach him anymore.

Upvotes: 1

Views: 610

Answers (1)

M--
M--

Reputation: 28826

You can make a vbscript (*.vbs) and run it through opening it or Task Scheduling;

In the script below (which you can simply copy/paste into note pad and save as "Converter.vbs"), you need to change the path to "Converter" workbook, change the name of the macro that does the job.

I would apply a change to your macro too; Instead of an input for name and path, use explicit coding that path is hard-coded in it and get the name based on the system's date. This way there's no need for user interaction with the script.

Option Explicit

'On Error Resume Next ''Comment-out for debugging

ExcelMacro

Sub ExcelMacro() 

 Dim xlApp 
 Dim xlBook 

 Set xlApp = CreateObject("Excel.Application") 
 Set xlBook = xlApp.Workbooks.Open("\path\to\Converter.xlsm", 0, True) 

 xlApp.Run "Convert" 'This line runs your macro (called Convert)

 xlBook.Close
 xlApp.Quit 

 Set xlBook = Nothing 
 Set xlApp = Nothing 

End Sub

Upvotes: 2

Related Questions