Can I enter a value in the prompt of a password-protected workbook?

[Eclectic]

I'm using Office 2007 and I'm a self-taught VBA user. This is an interaction between Access and Excel but I feel it's more relevant to Excel and thus posted here.

Basically I'm storing sensitive notes from an Access database in a password-protected Excel workbook and use VBA to at times import these notes into an Access report. After consulting many forums this is the code I use to import:


Dim OpenNotes As Object
Set OpenNotes = CreateObject("Excel.Application")
With OpenNotes
.Visible = False
.Workbooks.Open FileName:="Path", Password:="Password"
End With
DoCmd.TransferSpreadsheet acImport, 8, _
Report, Path, True, "A:D"
OpenNotes.ActiveWorkbook.Close SaveChanges:=False
OpenNotes.Quit
Set OpenNotes = Nothing

This works fine, but when this code is run and Excel is already running, the password is not automatically entered. Instead, the usual prompt for password appears in Excel and the code fails in Access because it can't run the TransferSpreadsheet command.

If this scenario occurs, can I instruct VBA to populate the password prompt input box with the password and carry on as usual? Alternatively, how would I check if Excel is already running so I can tell the user to close it before trying again?

[Kenneth Hobs]

Welcome to the forum!

I don't have an example like that exactly. Try using GetObject() similar to what was done with the MSWord application below.


'http://www.mrexcel.com/forum/showthread.php?t=333200
Sub FillForm()
Dim wdApp As Object, WD As Object, rn As Long
rn = ActiveCell.Row
On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then
Set wdApp = CreateObject("Word.Application")
End If
On Error GoTo 0
Set WD = wdApp.Documents.Open(ThisWorkbook.Path & "\Car Information Page.doc")
wdApp.Visible = True
With WD
.FormFields("Brand").Result = Cells(rn, "B")
.FormFields("Model").Result = Cells(rn, "C")
.FormFields("Chasis").Result = Cells(rn, "D")
.FormFields("Engine").Result = Cells(rn, "E")
.FormFields("Color").Result = Cells(rn, "F")
.FormFields("YearMonth").Result = Cells(rn, "G").Value & "/" & Cells(rn, "H").Value
End With
Set WD = Nothing
Set wdApp = Nothing
End Sub

[Eclectic]

That worked, thanks!