Open Excel 2003 workbook run macro save workbook

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?

I'm a noobie with VBA, and have never done anything with vbscript, but can usually get whatever to work

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?

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.

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).

Yes, the actual macro name is Database_query

OK.  I believe that I can provide you with what you need.  Can you wait a day or two for this?

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

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?

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"

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)?

Yes, I can wait that long,

Thank You

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

Thanks

Hello, mlongoh

Any luck with this?

Still working on it.

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.

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.

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.

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

Select allOpen 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

Select allOpen in new window

This comes up if rverything is good

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

Select allOpen in new window

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

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

Did you see Excel start?

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.

Did what you suggested, no change, except the script window opens and closes.
The excel file in the directory opens and just sits there

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.

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

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

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

The query is being run.  Can you manually run the macro after it opens the sheet and then see what happens?

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

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>

Did you happen to see the alert message being closed automatically (as opposed to you have to click OK)?

the alert message closed automatically

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.

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

Thank you,

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?

The name of the file that it is stored in is personal.xls.

OK, thanks. Should have something better today.

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

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

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

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.

So far all is looking good,

Wonderful.

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

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.

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)

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

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).

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

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.

Working good, have more workbooks than I thought, closer to 4,000

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.

Thanks for all your help, I has worked really good.
Is there there any way I can buy you a drink?

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.