I attached an excel 2010 source file. I was able to automate the scraping of data from a website public calendar system to this excel file. I used a program by winautomation.com to automate the scraping of data from a website. That part works great, and it generates an excel file with the website's raw data.
After creating the raw excel file, I was able to massage the data a bit using the macro recorder feature in excel, and doing so it did a fairly good job. I still have not figured out how to split column H into two separate columns using all succeeding text beginning with "filed by" to split into a second column. So further refinements are in progress.
I discovered that when the winautomation program creates the raw excel file, the excel macro I recorded is not available. It's not listed as an available macro to run. I exported the recorded macro to a Module2.bas file earlier for safe keeping. So the only way I can get the macro to run is by importing it first. If I open a new excel document I can see the macro, but when the program creates the raw data excel file its not available.
I was thinking if I can run the macro outside of excel on the current raw data that is open that would be an alternative. winautomation offers a way of running VBScripts, but I am having problems figuring out how to run the excel script as module2.vbs that's run outside excel. The only way I could get the macro to run was importing the .bas module and then running it. I am trying to automate this as much as possible, and manually importing the macro each time the file is created will not work.
The last thing I am needing to complete is have the excel macro call in a word template to complete a mail merge and print out all records and actually save the file to a network drive. I attached the word template as well.