Solved Transferring data to Excel Template (1 Viewer)

Samantha

still learning...
Local time
Today, 08:52
Joined
Jul 12, 2012
Messages
180
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.

Code:
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!
 

Attachments

  • Excel Screenshot 2021-03-31 121505.jpg
    Excel Screenshot 2021-03-31 121505.jpg
    216.7 KB · Views: 540

Isaac

Lifelong Learner
Local time
Today, 05:52
Joined
Mar 14, 2017
Messages
8,738
First thing I would change it this line:

Set XLBook = GetObject(MySheetPath)

to:

Set XLBook = XL.Workbooks.open(MySheetPath)
 

Samantha

still learning...
Local time
Today, 08:52
Joined
Jul 12, 2012
Messages
180
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!
 

Users who are viewing this thread

Top Bottom