Excel VB Script
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.
Mar12-2015RawData.xlsx
Module2.vbs
TempCourtCalMailMergeTemplate.docx
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.
Mar12-2015RawData.xlsx
Module2.vbs
TempCourtCalMailMergeTemplate.docx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
check this thread
for me I can find it even if existing file
try open new blank document and open the existing one without closing the new one
https://www.experts-exchange.com/questions/28372675/reattach-personal-xlsb.html
for me I can find it even if existing file
try open new blank document and open the existing one without closing the new one
https://www.experts-exchange.com/questions/28372675/reattach-personal-xlsb.html
ASKER
I tried deleting the personal file and have it recreate a new one. All new documents show the macro as a selection, but existing excel documents do not display it unless I import the module first.
I decided to create a shortcut on the Quick Access Toolbar, and then had winautomation create the excel file with raw data in it, and I was able to launch the macro by selecting toolbar shortcut. See below.
This still does not make sense why this is the case.
I decided to create a shortcut on the Quick Access Toolbar, and then had winautomation create the excel file with raw data in it, and I was able to launch the macro by selecting toolbar shortcut. See below.
This still does not make sense why this is the case.
try to save file as macro enabled with ext. .xlsm
ASKER
I was able to save the macro via the personal.xlsb. I was then able to copy this file to the appropriate XLSTART folder on other computers to share this macro. Thanks.
ASKER