By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,490 Members | 1,398 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,490 IT Pros & Developers. It's quick & easy.

Controlling Excel from Access: Using Sendkeys to enter data into an Excel Input Box

P: n/a
Windows 2K Pro
Access/Excel 2003

Hi there,
I have a large number of password protected Excel Workbooks. The files
are protected by code that runs when the workbook opens. The code asks
the user to key in a password into an input box, the password is
compared to a a partciular cell on a hidden (xlVeryHidden) sheet and if
it matches the Workbooks opens. I have not used the usual Excel
password protection as I need to provide for several levels of access
to the Workbooks.

I use an Access database to keep track of the Excel files and to
control the process of reading data from the files into one of the
database tables. To automate the process of reading data from the Excel
workbooks, I now need to automate the process of entering the password
into the input box.

I have successfully used Sendkeys within Excel ie used an Excel
workbook to open another Excel workbook and 'pass' the password to the
input box. When I attempted to replicate this using an instance of
Excel created within Access, I have run into difficulties. It seems to
me that, between Access and Excel, the applications are not sure who
the sendkeys keystrokes are for. I have tried to use explicit
references in the Excel object controlled by Access:

'some code to ID the file (strFileName) to be opened and its associated
password (strMasterPW)

Set AppExcel = New Excel.Application
With AppExcel
.Caption = "Scanning Files"
.Visible = True
.SendKeys strMasterPW, True
.Workbooks.Open strFileName, UpdateLinks:=False, ReadOnly:=True
End with

'Code to import s/sheet data using TransferSpreadsheet

Any inputs would be most appreciated!!

Jack

Nov 13 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.