Solved

Open Excel 2003 workbook run macro save workbook

Posted on 2013-01-17
57
799 Views
Last Modified: 2013-02-01
I have over 1,000 Excel 2003 workbooks that I need to open and then run a macro that is already in a personal.xls file and then save the file with the same name. The workbooks contain multiple worksheets.
The macro in the personal.xls file is named "Database_query" this macro is huge and makes calls to numerous other macro's that all reside in the personal.xls file. Its also writes to the "thisworkbook" in the workbook being opened.

Is there a way to do this in a batch so I do not need to open each files and do it?
0
Comment
Question by:cadman56
  • 29
  • 28
57 Comments
 
LVL 12

Expert Comment

by:mlongoh
ID: 38788769
It's probable that a VBA could be written to handle the batch.  A vbscript to handle it is how I would do it, but only because I'm more comfortable with vbscript than VBA (so it would take less time and I've done similar things in the past with it).

What's you level of comfort with either vba or vbscript?
0
 

Author Comment

by:cadman56
ID: 38788785
I'm a noobie with VBA, and have never done anything with vbscript, but can usually get whatever to work
0
 
LVL 12

Expert Comment

by:mlongoh
ID: 38788802
OK, give me an example of what you would manually do to fix one of the worksheets, step by step.

Also, do you have a list of the workbook file names (you don't have to give me that, I just want to know if you have a list that can be read by the script).  If not, are they all in the same folder?
0
 

Author Comment

by:cadman56
ID: 38788886
I would open a directory with the excel files in it.
Double click a file to have it open in Excel 2003
Click on a toolbar button that is mapped to the "Database_query" macro
When maco is done running click on "ok" button that lets me know it is done
Click save

I do not have a list of the file names. They are in multiple directories.
I thought that I would make a copy of the files that I need to run the macro on and put them in a directory. I would do this to about 200 files at a time.
0
 
LVL 12

Expert Comment

by:mlongoh
ID: 38788925
That's an acceptable approach.  I think that I can provide a script (or VBA) to do this in batch for you.

Is "Database_query" the actual name of the macro?  I ask because the code won't be "clicking a button" rather it will run the macro.  So you should also be able to run the macro by click Tools, Macro, Macro, select the macro and click Run.  What does it show up as on the list (I'm betting it's the same.. Database_query).
0
 

Author Comment

by:cadman56
ID: 38788961
Yes, the actual macro name is Database_query
0
 
LVL 12

Expert Comment

by:mlongoh
ID: 38788980
OK.  I believe that I can provide you with what you need.  Can you wait a day or two for this?
0
 

Author Comment

by:cadman56
ID: 38788981
You most likely already know this but when I open the excel file a file called personal.xls is opened hidden from view. This file is where the macro is located
0
 
LVL 12

Expert Comment

by:mlongoh
ID: 38788990
Yep, I got that the macro is in there.  Do you manually open it prior to opening the workbook you want to update or does opening the workbook auto-open personal.xls?
0
 

Author Comment

by:cadman56
ID: 38789067
The personal.xls always opens up hidden whenever Excel is started.

Also just thought of something.
This macro is doing updates to the workbook, sometimes if there is a problem the macro kicks out a message saying the "file could not be converted, do not save"
0
 
LVL 12

Expert Comment

by:mlongoh
ID: 38789101
Well that makes it more of a challenge.  I'll probably have to configure it to look for a success or failure message - if I can.  Might not get to this until Monday, and there's not guarantees that it will work, but I'll try.  Can you wait that long (I don't want to commit if you don't end up benefiting)?
0
 

Author Comment

by:cadman56
ID: 38789129
Yes, I can wait that long,

Thank You
0
 

Author Comment

by:cadman56
ID: 38801450
If all it does is stop running and let me know the file it stopped on, that would suffice.

Thanks
0
 

Author Comment

by:cadman56
ID: 38807603
Hello, mlongoh

Any luck with this?
0
 
LVL 12

Expert Comment

by:mlongoh
ID: 38807614
Still working on it.
0
 
LVL 12

Expert Comment

by:mlongoh
ID: 38810638
I'm still working on it.  Work work got in the way, along with a winter storm.  I have a script written that will run the macro... I just need to test it and refine for error handling.  Hopefully sometime today or tomorrow I'll have something for you.
0
 

Author Comment

by:cadman56
ID: 38810794
It's funny how work seems to have a way of doing that:).
We've been getting hit by alot of bad weather also, but thats part of living in Michigan.

Thanks for all the work you are doing on this.
0
 
LVL 12

Expert Comment

by:mlongoh
ID: 38810920
Can you send me screen grabs of what the success and failure alerts look like.  Or better the code out of the macro that generates them?  That will allow me to work through this part of the process easier.
0
 

Author Comment

by:cadman56
ID: 38811309
This message comes up if the file is already at the correct revision level
 Terminate2:
     MsgBox "This file is already at the correct revision" _
     & vbNewLine & "" & vbNewLine & "It will not be converted"
    'End
     Application.ScreenUpdating = False
    Windows("PERSONAL.XLS").Activate
    ActiveWindow.Visible = False
    ThisWorkbook.Saved = True ' Make it think personal.xls was saved this stops it from asking to save

    Workbooks(currentWB).Activate
    Sheets(currentWS).Select  'Reset to sheet started from
     ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    Range("A3").Select ' reset to top of page
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.CutCopyMode = False ' Clear clipboard
    End

Open in new window


This message come up if the macro fails for some unknown reason

  Terminate:
     MsgBox "This file CANNOT be converted - You've had a fatal error" _
     & vbNewLine & "" & vbNewLine & "DO NOT SAVE THIS FILE !"
    
    Application.ScreenUpdating = False
    Windows("PERSONAL.XLS").Activate
    ActiveWindow.Visible = False
    ThisWorkbook.Saved = True ' Make it think personal.xls was saved this stops it from asking to save

    Workbooks(currentWB).Activate
    Sheets(currentWS).Select  'Reset to sheet started from
    Range("A3").Select ' reset to top of page
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.CutCopyMode = False ' Clear clipboard
    Application.EnableEvents = True

    End

Open in new window


This comes up if rverything is good

 MsgBox "This file has been converted" _
     & vbNewLine & "" & vbNewLine & "Please check it over" 

Open in new window

0
 
LVL 12

Expert Comment

by:mlongoh
ID: 38816105
Here's what I've got.  Drop the .xls extension from each file and rename such that the dash is the . (AutoITX3.dll, RunExcelMacro.vbs, RunExcelMacroBG.vbs).

Put them all in the folder where you will copy the batches of workbooks to be updated, and then from the command line run cscript RunExcelMacro.vbs.

It launches the other vbscript which runs in the background (thus the BG in the name).

It will then open each .XLS file that it finds in the folder, run the Macro against it, and close it.  If it's a failure or a no change, it will not save the .xls, but if it successfully updates it, then  it will save the .xls.

Each file that it processes is logged in RunExcelMacro.log, so you can see which ones failed, succeeded, or didn't change.  You can copy the contents of the log file into Excel and use the Text to Columns function to parse the results so you can sort.

The background script looks for the alert message at the end of the macro run - if it sees it, it closes the alert and writes a small status message to a file that is read by the main script and used to log status.  When the main script stops, the background script quits automatically.

Obviously you need to test this, but I think it will work.  If not, let me know the problem and I'll try to help.
AutoItX3-dll.xls
RunExcelMacroBG-vbs.xls
RunExcelMacro-vbs.xls
0
 

Author Comment

by:cadman56
ID: 38816344
I renamed file per directions
I created a directory "C:\convert", copied your files and 1 excel file into it.
Opened command window,
Changed to the convert directory
typed in cscript runexcelmacro.vbs

Script window opens, the excel file opens and then nothing happens, it just sits there.
I close the excel file, the script window stays open with just a flashing cursor.
nothing happens, There is not a log file made or anything
0
 
LVL 12

Expert Comment

by:mlongoh
ID: 38816349
Did you see Excel start?
0
 
LVL 12

Expert Comment

by:mlongoh
ID: 38816360
You can stop the script with a CTRL-C at the command window, and close the other one by clicking the X.

I'd suggest opening any other sheet (not in the folder) so that the macro is available and then try to re-run the script.
0
 

Author Comment

by:cadman56
ID: 38816407
Did what you suggested, no change, except the script window opens and closes.
The excel file in the directory opens and just sits there
0
 
LVL 12

Expert Comment

by:mlongoh
ID: 38816424
My test was done with a small a couple of small .xls files, each with the macro called Database_query, is that the right macro name?

Stand by and I'll provide an updated copy that provides some feedback regarding what it's doing.
0
 

Author Comment

by:cadman56
ID: 38816455
That is the correct macro name, could it be because it is in the personal.xls file.
The macro button I have shows
Personal.xls'!Database_query
0
 
LVL 12

Expert Comment

by:mlongoh
ID: 38816479
Copy this one into the folder (rename appropriately) and run it as I instructed previously.  The command window that you launch it from will provide feedback concerning what it's doing.  Let me know what it shows.
RunExcelMacro-vbs.xls
0
 

Author Comment

by:cadman56
ID: 38816532
This is what it shows

C:\CONVERT>cscript runexcelmacro.vbs
Microsoft (R) Windows Script Host Version 5.7
Copyright (C) Microsoft Corporation. All rights reserved.

Running background script - runexcelmacroBG.vbs in separate window.
About to scan folder for files and process .xls files
Checking to see if C:\CONVERT\22HUBQ2.xls is an .XLS file
About to open file C:\CONVERT\22HUBQ2.xls
About to run Macro Database_query
Waiting for Excel alert message to pop up and be closed by the background script
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 12

Expert Comment

by:mlongoh
ID: 38816542
The query is being run.  Can you manually run the macro after it opens the sheet and then see what happens?
0
 

Author Comment

by:cadman56
ID: 38816559
If I run the macro manually after your script opens the file it closes the file and creates this in the log file
C:\CONVERT\22HUBQ2.xls      NoChange

The file has not been change.

If I run the macro on this file purly manually it updates it as it should
0
 

Author Comment

by:cadman56
ID: 38816575
This is what the script window shows after I run your script and then run the macro manually

C:\CONVERT>cscript runexcelmacro.vbs
Microsoft (R) Windows Script Host Version 5.7
Copyright (C) Microsoft Corporation. All rights reserved.

Running background script - runexcelmacroBG.vbs in separate window.
About to scan folder for files and process .xls files
Checking to see if C:\CONVERT\22HUBQ2.xls is an .XLS file
About to open file C:\CONVERT\22HUBQ2.xls
About to run Macro Database_query
Waiting for Excel alert message to pop up and be closed by the background script
Getting status from backgroundstatus script via file runexcelmacroBG.sta
No Change
Checking to see if C:\CONVERT\AutoItX3.dll is an .XLS file
Checking to see if C:\CONVERT\orig RunExcelMacro.vbs is an .XLS file
Checking to see if C:\CONVERT\runexcelmacro.log is an .XLS file
Checking to see if C:\CONVERT\RunExcelMacro.vbs is an .XLS file
Checking to see if C:\CONVERT\RunExcelMacroBG.vbs is an .XLS file

C:\CONVERT>
0
 
LVL 12

Expert Comment

by:mlongoh
ID: 38816583
Did you happen to see the alert message being closed automatically (as opposed to you have to click OK)?
0
 

Author Comment

by:cadman56
ID: 38816600
the alert message closed automatically
0
 
LVL 12

Expert Comment

by:mlongoh
ID: 38816618
OK, two problems.  The macro isn't getting run by the script, and the background script is interpreting the NoChange message incorrectly.

Let me investigate both (I'll setup an workbook to contain the macro, so it's not in the workbook being processed - which I think matches your scenario) and I'll post back when I have some updates.  Today if I get it worked out quickly, otherwise tomorrow.
0
 

Author Comment

by:cadman56
ID: 38816619
I put a few files in the directory to see if it would try to open another file.
It does not
If I manually run the macro after using your script it opens the next file but when all done none of the files have been changed
0
 

Author Comment

by:cadman56
ID: 38816624
Thank you,
0
 
LVL 12

Expert Comment

by:mlongoh
ID: 38816638
Well I got the one issue figured out already, so I know why it was reporting nochange when it actually updated - just  a small mistake on my part.

Almost there.

The macro... what is the name of the file that it is stored in?
0
 

Author Comment

by:cadman56
ID: 38818701
The name of the file that it is stored in is personal.xls.
0
 
LVL 12

Expert Comment

by:mlongoh
ID: 38818801
OK, thanks. Should have something better today.
0
 
LVL 12

Expert Comment

by:mlongoh
ID: 38819718
OK, updated and hopefully this will do the trick.  Replace the old scripts with these (rename appropriately), and you'll need to copy personal.xls into the same folder (it won't get processed with the other .XLS files, but it does need to be there so the script can open it - otherwise it doesn't work right - yes even though Excel is set to autoopen it).

Try it and let me know.
RunExcelMacroBG-vbs.xls
RunExcelMacro-vbs.xls
0
 

Author Comment

by:cadman56
ID: 38819861
It looks like it is partially working
When it comes to a file that fails it stops with a run-time error '13'" Type mismatch
It trys to run the macro on personal.xls which will always fail. It appears that when the personal.xls fails this is when it gets the error.
If it fails on other files it seems to be ok
Here is the log file
C:\CONVERT\2 will not convert KCTSB5Q1.xls      Failed
C:\CONVERT\350MEPCQ9.xls      Converted
C:\CONVERT\918SABBQ9.xls      Converted
C:\CONVERT\BK-172537CA1Q5.xls      Converted
C:\CONVERT\Personal.xls      Failed
0
 

Author Comment

by:cadman56
ID: 38819905
I think if you can make it so that when it comes the the personal.xls file that it ignores it and does not try to run the macro on it all may be done
0
 
LVL 12

Expert Comment

by:mlongoh
ID: 38820202
Personal.xls has an uppercase p, and that's what i think is throwing it off, as odd as it sounds.

Edit RunExcelMacro.vbs with Notepad, and find this line:
If InStr(File, "personal.xls") = 0 AND Lcase(Right(File, 4)) = ".xls" Then

and change it to this:
If InStr(1,File, "personal.xls",1) = 0 AND Lcase(Right(File, 4)) = ".xls" Then

and then save, exit and try re-running.  The change tells the InStr function to be case insensitive and should truly keep it from trying to process Personal.xls.
0
 

Author Comment

by:cadman56
ID: 38820667
So far all is looking good,
0
 
LVL 12

Expert Comment

by:mlongoh
ID: 38820677
Wonderful.
0
 

Author Comment

by:cadman56
ID: 38827835
Found a problem, I don't know what would cause this.
This macro that I am running converts the workbook all the way from a revision 1 to a revision 20.
When you look at the workbook after converting everything looks good.
But then under more testing I found that a dropdown in only some of the workbooks does not work after converting using your script.
If I run the macro manually, the dropdown works
0
 
LVL 12

Expert Comment

by:mlongoh
ID: 38827925
Well, here's what the script does, that's different from manual:
Opens personal.xls (personal.xls is opened automatically when you manually launch Excel)
Logs to a file based on the content of the alert message
Automatically closes the alert messages (a person has to do it manually)
Based on the content of the "alert message log file" either saves and closes the .xls file being processed or closes without saving and logs the result of processing the .xls to a log file.

That's it.  The script itself does not interact with the .XLS file in any other way, so I can't resolve the issue back to the script.  It's possible I suppose that the macro could still be doing something when the alert message comes up and the script acts quickly to close the alert and close the .xls before the macro finishes completely?  That's a guess because without knowing everything that the macro does, I can't do more than guess.
0
 

Author Comment

by:cadman56
ID: 38827926
Also,
In the workbooks that I have this above problem in there are buttons with macro's attached to them. Now when I click on them it tells me that a document named personal.xls is already open. When I check the macro for the button it is now referring to personal.xls in the convert directory. ( The convert directory is the directory I put your script in and a copy of the personal.xls and then the file I want to run your script on)
0
 

Author Comment

by:cadman56
ID: 38827995
You may have something there in the timing or speed.
It appears to only be happening on files older that revision 10.
If I take the file that I ran your script on and change the revision on it back to 9 and then run it again everything seems to work.
would it be possible for you to put a timer in it so I could give it a try?

Thank You for all the hard work you have done on this. I believe it is getting really close
0
 
LVL 12

Accepted Solution

by:
mlongoh earned 500 total points
ID: 38828012
Edit RunExcelMacro.vbs with Notepad, and find this line:

wscript.echo "Open " & strpath & "\personal.xls"
Set objMacroWorkbook = objExcel.Workbooks.Open(strPath & "\personal.xls")

and change it to this:

wscript.echo "Open " & "XXXX\personal.xls"
Set objMacroWorkbook = objExcel.Workbooks.Open("XXXX\personal.xls")

but replace the XXXX to the path where the "proper" personal.xls is, and delete personal.xls from the CONVERT folder.

See if that helps.  Also, if you think that the script is possibly saving and closing the sheet before the macro is done, you can add a delay into the script prior to it saving.  Let me know if you think it's needed.
0
 
LVL 12

Expert Comment

by:mlongoh
ID: 38828098
Find these lines:
            If InStr(Content, "2") > 0 Then
                  objWorkbook.Save

and change it this:
            If InStr(Content, "2") > 0 Then
                  wscript.sleep(3000)
                  objWorkbook.Save


This will provide a 3 second delay before it saves the workbook.  You can increase it if you think it's needed (wscript.sleep(5000) would be 5 seconds).
0
 

Author Comment

by:cadman56
ID: 38828552
Right now it is looking like just changing the callout for the personal.xls may have fixed the problems

Will let you know

Thank You
0
 
LVL 12

Expert Comment

by:mlongoh
ID: 38830500
Please take your time to determine if it's working. In fact it's preferable that if it is working that you wait until after the end of this month to accept the answer; if you don't mind.
0
 

Author Comment

by:cadman56
ID: 38844829
Working good, have more workbooks than I thought, closer to 4,000
0
 
LVL 12

Expert Comment

by:mlongoh
ID: 38844846
Glad to hear it - it would stink to have to do those manually.  You can accept the answer anytime - every month I have to maintain a certain number of points, and I already hit my mark for January, so that's why the request to wait until Feb.  

FYI: after the question is closed, I'll still be monitoring the question and will respond if you have any other related issues or questions.
0
 

Author Closing Comment

by:cadman56
ID: 38844884
Thanks for all your help, I has worked really good.
Is there there any way I can buy you a drink?
0
 
LVL 12

Expert Comment

by:mlongoh
ID: 38844911
LOL - I don't think EE has a points to booze currency exchange... yet. Really I'm just glad I could help and that it's working for you.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now