Solved Transferring data to Excel Template

[Samantha]

Hello,
I am thumping my head looking at different methods of transferring data into my template file. This used to work perfectly prior to I believe it was moving from Access 2013 to 2016- so its been going on for sometime. I now have a subscription to 365 and am on semi-annual updates currently version 2008.

My problem is that when it transfers the information it opens two instances of excel windows (image attached). If I close the empty window it also closes my template.

Private Sub cmdMergeXLbttn_Click()
'On Error GoTo Error_Handler

'Set up object variables to refer to Excel and Objects
Dim XL As Excel.Application
Dim XLBook As Excel.Workbook
Dim XLSheet As Excel.Worksheet
Dim ctl As Control
Dim varItem As Variant
Dim MySheetPath As String, strFileNamePath As String, strLink As String

'Open an instance of Excel, open the workbook.
Set XL = CreateObject("Excel.Application")
Set XLBook = GetObject(MySheetPath)
Set XLSheet = XLBook.Worksheets(1)

'Make sure everything is visible
XL.Visible = True
XLBook.Application.Workbooks(1).Activate
XLBook.Windows(1).Visible = True

'set file path to actual sheet
MySheetPath = "O:\Access\ProjectSheet.xlsx"

'Define top sheet in Workbook as XLSheet
Set XLSheet = XLBook.Worksheets(1)

After this bit I go on to build strings, fill ranges and save the file created to our network. It all works as expected except this second empty window is launching and for the life of me every time I try to figure it out I am defeated and give up.

Thanks in Advance!

 

[Isaac]

First thing I would change it this line:

Set XLBook = GetObject(MySheetPath)

to:

Set XLBook = XL.Workbooks.open(MySheetPath)

 

[Samantha]

First thing I would change it this line:

Set XLBook = GetObject(MySheetPath)

to:

Set XLBook = XL.Workbooks.open(MySheetPath)

Brilliant Isaac! Thank you so much!