Eclectic
06-08-2011, 11:57 AM
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?
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?