Excel workbook not closing 3

[KellyK]

Hi all, hopefully this is an easy question, seems like it should be but I don't know the answer!

I have some Access code which opens an Excel doc (referenced in a text box on a form) and imports the document into a new table. Easy enough. I don't need or want to see the Excel document being opened, I don't need it opened for any reason other than to import it into the Access table. I need to actually open it versus simply importing it because there's a goofy Excel error message due to formatting and I need to set DisplayAlerts = False for it to import. If I do not have an instance of Excel open on my computer prior to running the code, this process is seamless and "invisible" to me. However, if I have a workbook open and then run the code, I "see" the file I'm importing and then it does not close when the code completes. Then, within Excel I have a message box stating that the file is now available, do I want to open it or read only? I can close the file manually, but I do not want my users to be confused or irritated by this unnecessary step. Can someone point out how I'd have to modify my code to achieve this? Sorry for the wordy description!

Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Set objXL = New Excel.Application

'Turn off excel warnings
objXL.DisplayAlerts = False

'Import JIT file
DoCmd.TransferSpreadsheet acImport, , "tmpJIT", txtJITReportFile, True

'Close the JIT file
objWkb.Close
objXL.Quit
Set objXL = Nothing
Set objWkb = Nothing

Kelly

 

[PHV]

objWkb.Close
Where and how is objWkb set ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[KellyK]

I'm so embarassed, I forgot to copy that part!

Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Set objXL = New Excel.Application

'Turn off excel warnings
objXL.DisplayAlerts = False

'Open the JIT file
Set objWkb = objXL.Workbooks.Open(txtJITReportFile)

Set db = CurrentDb()

'Delete existing temporary JIT table
db.TableDefs.Delete "tmpJIT"

'Import JIT file
DoCmd.TransferSpreadsheet acImport, , "tmpJIT", txtJITReportFile, True

'Close the JIT file
objWkb.Close
objXL.Quit
Set objXL = Nothing
Set objWkb = Nothing

I didn't want to post code that was irrelevant to the question but then I missed an important line! Duh. This is everything.

Kelly

 

[PHV]

Instead of the New operator I'd use the GetObject and CreateObject functions.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[KellyK]

How do I turn off the Excel alerts before I "Get" the object (file) that I want to use?

Kelly

 

[CautionMP]

KellyK,
I think this is right, changes in bold.

Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
[b]Dim blnSpawned As Boolean

On Error Resume Next
Set objXL = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
  Err.Clear
  Set objXL = CreateObject("Excel.Application")
  blnSpawned = True
End If
On Error GoTo 0[/b]

'Turn off excel warnings
objXL.DisplayAlerts = False
[b][green]'Hide the opening workbook if grabbing an open instance of Excel[/green]
If blnSpawned Then
  objXL.ScreenUpdating = False
End If[/b]


'Open the JIT file
Set objWkb = objXL.Workbooks.Open(txtJITReportFile)

[b]If blnSpawned Then
  'Turn excel warnings back on if Excel was already open
  objXL.ScreenUpdating = True
  objXL.DisplayAlerts = True
End If[/b]

Set Db = CurrentDb()

'Delete existing temporary JIT table
Db.TableDefs.Delete "tmpJIT"

'Import JIT file
DoCmd.TransferSpreadsheet acImport, , "tmpJIT", txtJITReportFile, True

'Close the JIT file
objWkb.Close
Set objXL = Nothing
[b]If blnSpawned Then objXL.Quit[/b]
Set objWkb = Nothing

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[KellyK]

Thanks, CMP, have a star!

Just one minor change -

'Close the JIT file
objWkb.Close
If blnSpawned Then objXL.Quit
Set objXL = Nothing
Set objWkb = Nothing

Need to set objXL = Nothing after the If blnSpawned line otherwise the thing bombs on "objXL.Quit". But otherwise lovely and gets me exactly what I need!! Bravo.

I do see the spreadsheet open up on my taskbar at the bottom of the screen but it closes almost immediately and is not too big of a bother. Besides, there is probably some setting I can change to make that go away too. I did find some code similar to yours that I was playing with but it didn't quite nail it like yours did. Thanks again!

Kelly