Opening an Excel file from a Userform

[assertec]

Hi all,
I need some code that will allow me to open an Excel Workbook from a
command button I have placed on my Userform.

My Userform form has various command buttons that allow me to open Word
documents, and I need the equivalent that will allow me to now open
Excel files as well.

This is an example of the code that sits under a command button that
will allow me to open a Word document.

Private Sub cmdShowDocument1_Click()
Call OpenFile("C:\Document1.doc")
End Sub

.... and this is the called function.

Public Sub OpenFile(ByVal File_Name As String)
Dim OpenFileVar
OpenFileVar = ShellExecute(0&, "open", File_Name, vbNullString,
vbNullString, 1)
End Sub

I need something similar that will allow me to open Excel Workbooks.

Thanks very much for your help with this.

Regards
Karen

 

[Bob Phillips]

Private Sub cmdShowDocument1_Click()
Call OpenExcelFile("C:\Workbook1.xls")
End Sub

.... and this is the called function.

Public Sub OpenExcelFile(ByVal File_Name As String) as boolean
Dim oWB As Workbook
On Error Resume Next
Set oWB = Workbooks.Open(File_Name)
On Error Goto 0
OpenExcelFile = Not oWB Is Nothing
End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[assertec]

Thanks for the code Bob.

I could not get this line - "OpenExcelFile = Not oWB Is Nothing" - to
work, so I removed it and all seemed to be OK then.

Also, do you know how I can modify the code so that the Workbook then
has the focus when it is opened. Currently the opened workbook sits
behind the Userform, and the only way that I can view it is by closing
the Userform. What I'm try to acheive is to keep the Userform running
all the time, so the user can open the Word docs and the Excel
workbooks (via the Userform) then work with the opened files - and when
they are closed, the Userform is still running.

Thanks again Bob

Regards Karen

 

[Bob Phillips]

That is because it should be a Function not a sub

Private Sub cmdShowDocument1_Click()
Call OpenExcelFile("C:\Workbook1.xls")
End Sub

.... and this is the called function.

Public Function OpenExcelFile(ByVal File_Name As String) as boolean
Dim oWB As Workbook
On Error Resume Next
Set oWB = Workbooks.Open(File_Name)
On Error Goto 0
OpenExcelFile = Not oWB Is Nothing
End Sub

You do need that so that the calling routine can test whether the open
worked okay. Like this

res = OpenExcelFile("C:\Workbook1.xls")
if res Then
... all okay

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[assertec]

Thanks Bob,

That works much better now that it is a function, however the workbook
opens in the background behind the Userform - which means that I need
to close the Userform to get to the Workbook. Is it possible to allow
the user to open the Excel workbook (via the Userform), and have the
workbook displaying in front of the Userform instead of behind the
Userform?

Thanks again Bob

Regards
Karen

 

[Bob Phillips]

In short, no it is not.

What you can do is to open the form non-modally, which means that work can
be done on the workbook

Userform.Show 0 'or vbModeLess

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[assertec]

Thanks again Bob - greatly appreciated.