Opening Excel in a more stable manner

[Kemidan2014]

I am having issues with this code working and not completely working. its a 50 50 chance that the file remains open. Sometimes when executing the code via button click in access it executes WITH OUT excel remaining visible and thus puts one of those faded ~"filename" icons on my desktop signifying that excel is open but not visible even those i am specifying it int he code to be visible.

is there a slightly different way i should be opening excel in order to perform this? the transferspeadsheet works perfectly and does not overwrite the other tabs in the file i just wish it would open reliably.

Private Sub Qualitymetrics_Click()
'Declare stuff
Dim excelapp As Object
Dim wb As Object
Dim openpath As String
openpath = "C:\users\desktop\ComplaintMetricsQuery.xlsx" 'Set file path
Set excelapp = CreateObject("Excel.Application")
 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "ComplaintMetricsQuery", "C:\users\desktop\ComplaintMetricsQuery.xlsx", True

Set wb = Excel.Workbooks.Open(openpath)
excelapp.Visible = True
'wb.worksheets("QueryDump").Range("A1:K100").ClearContents
wb.Save
'wb.Close


End Sub

 

[Micron]

Can't say why this would be happening other than to suggest not using late binding unless you have to.

Early and Late Binding - Visual Basic

Learn more about: Early and Late Binding (Visual Basic)

learn.microsoft.com

 

[welshgasman]

Not sure why you are mixing variable and strings?, always potential for mistakes there?

I would have thought this would not even work?

Set wb = Excel.Workbooks.Open(openpath) as the object is ExcelApp ??

 

[RoryA]

It will work but it will create an implicit Excel.Application variable which is quite likely at least part of the problem.

 

[Kemidan2014]

I guess im still a little ignorant as to the difference between late binding and early binding. but i did switch it up a bit and added a message box and excel opens each time now but to Rory's point it does appear to leave Excel as an open process. when i add
'Excelapp = Nothing' at the end of the code the code errors out at "wb= ..." saying theres nothing there.

what other way to open an existing file is there?

 

[RoryA]

As suggested, you should be using:

Set wb = excelapp.Workbooks.Open(openpath)

and not:

Set wb = Excel.Workbooks.Open(openpath)

 

[Kemidan2014]

That works, however in my task manager it opens the file and i have 2 listings of excel 1 in the "Apps" and 1 as a background process. closing the file opened just closes the "Apps" listing.

if i open any other excel file as you normally would, i dont see the appearance of excel as a background process

 

[RoryA]

It shouldn't do, unless you have other similarly unqualified code that you haven't shown. (I'm assuming you already killed any previously created background processes from your original code)

 

[Kemidan2014]

Edited, i just noticed something let me adjust it

 

[Kemidan2014]

Sorry Rory, I am blind and missed the correct line that needed to be changed, The post i marked as solution did the trick!