button code to open specific excel document

[james]

Hi
Sorry I am not experienced with VB.
Could someone please provide a code I can assign to a command button that
will open a specific excel spreadsheet that is password protected and input
the password on our behalf?

Adapting the following would be great

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

Dim oApp As Object

Set oApp = CreateObject("Excel.Application")
oApp.Visible = True
'Only XL 97 supports UserControl Property
On Error Resume Next
oApp.UserControl = True

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub

Thanks

 

[Daniel Pineault]

You could use something like:

Function OpenPwdXLS(strWrkBk As String, sPwd As String)
Dim xlApp As Object
Dim xlWrkBk As Object

On Error GoTo Error_Handler

On Error Resume Next
Set xlApp = GetObject(, "Excel.Application") 'Bind to existing instance
of Excel

If Err.Number <> 0 Then
'Could not get instance of Excel, so create a new one
Err.Clear
On Error GoTo Error_Handler
Set xlApp = CreateObject("excel.application")
Else
On Error GoTo Error_Handler
End If

xlApp.Visible = True 'make excel visible to the user
Set xlWrkBk = xlApp.Workbooks.Open(strWrkBk, , , , sPwd)

'... the rest of your code goes here

Error_Handler_Exit:
On Error Resume Next
Set xlWrkBk = Nothing
Set xlApp = Nothing
Exit Function

Error_Handler:
MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf &
"Error Number: " & _
Err.Number & vbCrLf & "Error Source: OpenPwdXLS" & vbCrLf & "Error
Description: " & _
Err.Description, vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
End Function
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

 

[james]

Thanks, I managed to get the following to work:

Private Sub Command1_Click()
On Error GoTo Err_Command1_Click

Dim ExcelApp As Object 'Excel.Application
Dim ExcelWkb As Object 'Excel.Workbook

Set ExcelApp = CreateObject("Excel.Application")

Set ExcelWkb = ExcelApp.Workbooks

ExcelWkb.Open "C:\Test.xls", , , , "ABCDE", UpdateLinks:=1

ExcelWkb.Close

ExcelApp.Quit

Set ExcelWkb = Nothing
Set ExcelApp = Nothing

Exit_Command1_Click:
Exit Sub
Err_Command1_Click:
MsgBox Err.Description
Resume Exit_Command1_Click

End Sub

It opens a spreadsheet that is password protected. This spreadsheet has
links to another password protected spreadsheet. The button enters the
password and sets update links to yes but it then asks for a password to
update the links I would like it to input the password automatically. Don't
suppose you know how to do this?

Thanks