Open xls files using batch or vbs

[rsulliva]

I have 25 files that I have to open at once. They are all passworded with different passwords. I have created a batch file that will open them all. It works, but I have to enter the password for each one over and over. I know there has to be a way to modify my batch file to enter the passwords. I read that it may be possible to use vbs to do this.

My batch file looks like this now (it is located in the folder with the files):

Start /max "S:\Budget\2012\Set up Files\" "Bgt 2012 File1.xls"
Start /max "S:\Budget\2012\Set up Files\" "Bgt 2012 File2.xls"
Start /max "S:\Budget\2012\Set up Files\" "Bgt 2012 File3.xls"
Exit

This is what I found on the net but is not working for some reason even though I saved it as a vbs:

sub open_passworded_file ()
set wshshell = wscript.CreateObject("wscript.shell")
Workbooks.open "S:\Budget\2012\" "Bgt 2012 File1.xls" 'this is where your file goes
WshShell.SendKeys "password{enter}" 'this is where your password goes
End Sub

Even if I have to make a dummy excel file and run a macro from that file that opens all the other files and enters the respective password that would be beneficial.

Any suggestions appreciated.

Thanks,

rsulliva

 

[GTO]

Hi there,

It may become beneficial to mention why we are opening the files?

That aside, as you are opening Excel workbooks, why not use VBA to do so? VBScript will work, but it just seems like extra effort for naught.

If you were using vbs to open though, the Object you're wantiing to create would be Excel.Application, and you'd use the variable to qualify anything to do with it, eg - Workbooks.Open etc.

Mark

 

[rsulliva]

As to why I am opening all the files at once....

There are 25 different people that have to update their own file in a folder that I give them access to and give them their own password. After the files are updated, I have to open all of them including the database file so they can all merge into the database. The database has many formulas (including sumifs) linking to the other 25 files. Likewise the other files link to the database. So in order to get the correct figures, I periodically have to open all the files at once to get the to update properly.

Thanks,

rsulliva

 

[GTO]

With the Excel workbook containing the code residing in the folder that the other files are in, here's a basic example using Dir().

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br>    <br><SPAN style="color:#00007F">Sub</SPAN> OpenAll()<br><SPAN style="color:#00007F">Dim</SPAN> FileName()  <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> n           <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#007F00">'// Initially size our dynamic array                            //</SPAN><br>    <SPAN style="color:#00007F">ReDim</SPAN> FileName(0 <SPAN style="color:#00007F">To</SPAN> 0)<br>    <br>    <SPAN style="color:#007F00">'// See VBA help for Dir                                        //</SPAN><br>    FileName(0) = Dir(ThisWorkbook.Path & "\*.xls*")<br>    <SPAN style="color:#007F00">'// If we got something (We're guaranteed in the example, since //</SPAN><br>    <SPAN style="color:#007F00">'// ThisWorkbook is in the folder), loop in the rest of the     //</SPAN><br>    <SPAN style="color:#007F00">'// filenames.                                                  //</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Len(FileName(0)) = 0 <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#007F00">'// Kill repaints and save eyeballs                         //</SPAN><br>        Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>        <SPAN style="color:#00007F">Do</SPAN><br>            <SPAN style="color:#007F00">'// Increase size of array to hold next value           //</SPAN><br>            <SPAN style="color:#00007F">ReDim</SPAN> <SPAN style="color:#00007F">Preserve</SPAN> FileName(0 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(FileName) + 1)<br>            FileName(UBound(FileName)) = Dir()<br>        <SPAN style="color:#007F00">'// Loop until Dir() returns an empty string                //</SPAN><br>        <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">While</SPAN> <SPAN style="color:#00007F">Not</SPAN> FileName(UBound(FileName)) = vbNullString<br>        <SPAN style="color:#007F00">'// Ditch the empty element at the end of the array         //</SPAN><br>        <SPAN style="color:#00007F">ReDim</SPAN> <SPAN style="color:#00007F">Preserve</SPAN> FileName(UBound(FileName) - 1)<br>        <br>        <SPAN style="color:#007F00">'// Loop back thru array, skipping past ThisWorkbook        //</SPAN><br>        <SPAN style="color:#00007F">For</SPAN> n = <SPAN style="color:#00007F">LBound</SPAN>(FileName) <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(FileName)<br>            <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> ThisWorkbook.Name = FileName <SPAN style="color:#00007F">Then</SPAN><br>                Workbooks.Open ThisWorkbook.Path & "\" & FileName<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN><br>        <SPAN style="color:#007F00">'// Yikes, that's a bunch o' buttons on that Task Bar!</SPAN><br>        Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[rsulliva]

Well,

I made a workbook, and passworded it, that simply has a button that runs a single workbook.open and password macro on all the 25 files sequentially. Not exactly what I wanted but this seems to do the trick.

Now I have to find out how to make each file, if opened individually, "update" itself automatically with data from the closed passworded dBase.

Thanks again,

Russell