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
cmp119IT ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

FarWestCommented:
to solve keeping script issue, you can save your module in PERSONAL.XLSB file which will be accessible for all opened workbooks, (I think you have to clear the read-only flag for the file before you add the module to it)
for vbs executing outside excel; you can do it using cscript  or wscript commands, but you have to use CreateObject for the office program first in your code , and use its reference when referring to objects
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cmp119IT ManagerAuthor Commented:
If I open a new blank excel document, I can see the personal.xlsb macro.  If I open existing excel spreadsheets this macro is not available.  I have to import the module within visual basic editor to make it available.
0
FarWestCommented:
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
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28372675.html
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

cmp119IT ManagerAuthor Commented:
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.

Excel Macro Not Listed

This still does not make sense why this is the case.
0
FarWestCommented:
try to save file as macro enabled with ext.   .xlsm
0
cmp119IT ManagerAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.