still having problem with open currently open excel file

[John Coon]

I am still having trouble with the link to a already open excel workbook.
I am able to open/load a wookbook with the filename location. However I want
to be able to remove this and just have it use whatever the currently open
wookbook.

any hints?

John Coon

Dim ExcelApp As Excel.Application
Dim wbkObj As Workbook
Dim shtObj As Worksheet
On Error Resume Next
UserForm1.Hide
Err.Clear
Set ExcelApp = GetObject(, "Excel.Application")
If Err <> 0 Then
Err.Clear
Set ExcelApp = CreateObject("Excel.Application")
If Err <> 0 Then
MsgBox "Could Not Start Excel", vbExclamation
End
End If
End If
ExcelApp.Visible = True
Set wbkObj = ExcelApp.Workbooks.Open(Filename:="d:\vba\sample\part4.xls")
Set shtObj = ExcelApp.Worksheets(1)
UserForm1.Show
End Sub

 

[Ron de Bruin]

Try this

Set wbkObj = ExcelApp.ActiveWorkbook

 

[John Coon]

Ron,

Thanks for your quick reply, I'll add your comments and see if that does the
trick!

John Coon