Help with Excel Automation Import

[DoctorV3774 via AccessMonster.com]

This module is outstanding for importing several workbooks from a specific
folder. The problem i am having is that the Workbooks are very sensitive
and have a Password on them. The Password is Test and it is the same for
every workbook. What I need to do is run this Module EXACTLY like it
works, but just add the ability to open the workbook in this folder with a
password named Test close it save changes false and go on to the next one.
Also is there a way to grab data from different sheets say sometimes it is
DBOutput!A2 but then also SalaryStructure!C2 It will be the same range for
every workbook, just using multiple sheets

I need to use Excel Automation to Open Workbook (Not Visible While Opening)
, Insert Password Test, grab range, Append to tbl_data, close workbook save
changes false, then go open the next Excel file in the folder

Please Help Very urgent.
***Code***********

Sub ImportFireFighterForms()
Dim strPath As String
Dim strFile As String

strPath = "C:\FireFighterForm\"
strFile = Dir(strPath & "*.xls")

While strFile <> ""
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"tbl_Data", strPath & strFile, False, "DBOutput!A2:H2"
strFile = Dir()
Wend
End Sub

 

[John Nurick]

There's basic example code for automating Excel at
http://www.mvps.org/access/modules/mdl0006.htm

You'll need to modify it to open a password protected workbook, it would
be something like

Set objActiveWkb = objXL.Workbooks.Open( _
FileName:="C:\Folder\Filename.xls",
Password:="Test"
)