1

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.

M--
  • 25,431
  • 8
  • 61
  • 93
AudioTroubler
  • 69
  • 1
  • 1
  • 7

1 Answers1

2

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
M--
  • 25,431
  • 8
  • 61
  • 93
  • My VBA scripting skills are pretty light. So within the workbook, I looked at the macro itself And it has 4 modules (in pic below) http://i.imgur.com/Lpz1UPb.png How would I go about that? – AudioTroubler May 23 '17 at 13:46
  • 1
    @AudioTroubler Just find where it takes the input, and change them to be exactly what you want. `Today() + 1` gives you the tomorrow's date. You can use something like `format(Today() + 1, "mm-dd-yyyy")` to format it as you wish. This is for the name of the text file. Also the path. From What I see, there's an User-Form. You need to take a look at those. In the code it will be open you can enter path and name and then they will be unloaded. Find the part of the code that contains that and change it to explicitly defined path and name. I cannot provide any further help without seeing the code. – M-- May 23 '17 at 13:51
  • This is the code for 4 of the modules - https://pastebin.com/tgS1BpAc And this is the files itself - https://drive.google.com/open?id=0Bx1g2zYEjTEpREVrd3g0MnNFZVk Can you help me locate the needed parts, please? – AudioTroubler May 23 '17 at 13:56
  • 1
    From what I see, You have a main sub that only has `OpenForm.Show 1`. You can see there's a folder called `Forms` in your VBA. Open that and migrate (not only copy/paste, apply proper changes) them to that sub and get rid of `Form.Show`. It gets the path, name, etc. form the form; define them explicitly. – M-- May 23 '17 at 14:08
  • So I didn't really understand what to migrate to that folder, did you mean the modules? Also could you tell me where do I define the path, inside "OpenForm" or inside one of the modules? Thank you in advance – AudioTroubler May 24 '17 at 04:00
  • 1
    You should take everything out of the Form and out it in your main module. Because you want to automate the process and don't want a userform to pops up after running the script. You should consider posting another question if you don't know how to change a userform code to be in a module. Again, just to clarify, you need to take the codes in folder called "Form" and put it in the folder called "Module", instead of the line says `OpenForm.Show` you'll have lines of code brought from there. – M-- May 24 '17 at 13:20
  • 1
    @AudioTroubler I don't think I can be any more help. You need to ask other questions with details and specify to one problem to find your way. Your form was kind of busy and also in other language so it wasn't that easy for me to just change it for you. Beside the fact that SO is not a code writing community. – M-- May 24 '17 at 13:22