Open xls files using batch or vbs

rsulliva

Board Regular
Joined
Sep 13, 2007
Messages
116
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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
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

Board Regular
Joined
Sep 13, 2007
Messages
116
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

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
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(n) <SPAN style="color:#00007F">Then</SPAN><br>                Workbooks.Open ThisWorkbook.Path & "\" & FileName(n)<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

Board Regular
Joined
Sep 13, 2007
Messages
116
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,551
Messages
5,636,965
Members
416,952
Latest member
prakashkumar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top