Can you open an Excel workbook from Access?

  • Thread starter Thread starter Paul B
  • Start date Start date
P

Paul B

I know you can have Access open excel, but can you have it open a workbook
also, here is the code I get when you put a button on a from to do it

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

Dim oApp As Object

Set oApp = CreateObject("Excel.Application")
oApp.Visible = True

'***** can it open the workbook here??******

'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
 
yes. i use the following code to open a specific workbook, perform some
actions, and then close the workbook again. note that this code does not
select a specific worksheet in the workbook, simply because in my process i
am always using the first worksheet and that is always the worksheet at the
"front" when the workbook opens - so you may need further coding if that's
not the case in your situation.

hth


Public Sub isOpenWorkbook()

On Error GoTo OpenErr

Dim MyXL As Object, rst As DAO.Recordset
Dim strSQL As String

Set MyXL = GetObject("P:\Dbs\Book2.xls")

MyXL.Application.Visible = True
MyXL.Application.WindowState = 3
MyXL.Parent.Windows(1).Visible = True
MyXL.Parent.ActiveWindow.WindowState = 2

' put here any code that you need to "do something"
' in the workbook.

OpenEnd:
Set MyXL = Nothing
Exit Sub

OpenErr:
Select Case err.Number
Case 432 ' file doesn't exist
MsgBox "File not found. Sorry, you'll have to do " _
& "it manually."
Set MyXL = Nothing
Exit Sub
Case Else
MsgBox err.Number & " - " & err.Description, , _
"Unknown error: isOpenWorkbook()"
Resume OpenEnd
End Select

End Sub
 
Got it, looks like this will work

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

Dim oApp As Object

Set oApp = CreateObject("Excel.Application")
oApp.Visible = True
oApp.Workbooks.Add ("C:\My Documents\test.XLS")
'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
 
oops, the third line of the module should read

Dim MyXL As Object

(the recordset is specific to the operation i perform with my code, but not
needed to generically open a workbook.)

hth
 
Back
Top