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\p art4.xls")
Set shtObj = ExcelApp.Worksheets(1)
UserForm1.Show
End Sub

Try this

Set wbkObj = ExcelApp.ActiveWorkbook

--
Regards Ron de Bruin
http://www.rondebruin.nl


"John Coon" wrote in message ...
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\p art4.xls")
Set shtObj = ExcelApp.Worksheets(1)
UserForm1.Show
End Sub

Ron,

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

John Coon
"Ron de Bruin" wrote in message
...
Try this

Set wbkObj = ExcelApp.ActiveWorkbook

--
Regards Ron de Bruin
http://www.rondebruin.nl


"John Coon" wrote in message
...
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\p art4.xls")
Set shtObj = ExcelApp.Worksheets(1)
UserForm1.Show
End Sub