Linking to Excel - many Excel processes left open? 
Author Message
 Linking to Excel - many Excel processes left open?

You have to call the xlApp.Quit method plus you need to set all references
to Nothing.
(xlApp, xlWB, xlWs all need to = Nothing.)

Also, test to see if Excel is running in memory before creating a new
instance.
If it is running the use it.

Try to use the existing instance:
   objXL = CType(GetObject(, "Excel.Application"), Excel.Application)

If there isn't one then an error will be raised:
   'then in your error handler create a new instance of Excel if there isn't
one running.
    objXL = New Excel.Application
--
Joe Fallon
Access MVP

"John Pritchard (E-mail is careof - I'm new to company)"

Quote:
> The Full Code is below.

> I'm loading a set of spreadsheets from an excel workbook
> to a set of access tables and generating a query to access
> the data dynamically. The process works fine (except for
> intermittant problem 2.) unless I run it more that one in
> the same access session?

> Two Problems:-

> 1. If I run the Getdata routine more than once it starts
> another excel.exe process leaving the original in memory -
> where it stays even when I close access. I have to remove
> these rougue excel processes manually. Performance soon
> dies a death as a result.

> 2. The CheckQry routine sometimes fails at the docmd stmt
> claiming it can't find the "mainqry" I've just checked for
> in the set of querydefs.

> ' Global declarations

> Dim dbscredan As DAO.Database

> Sub GetData(ByVal FullPath As String)

>     Dim Centre As String
>     Dim TableName As String
>     Dim i As Integer
>     Dim qrystr As String

> 'Open database and workbook

>  '    MsgBox "Current Database is " & DBEngine.Workspaces
> (0).Databases(0).Name

>      BeginTrans

>      Set dbscredan = DBEngine.Workspaces(0).Databases(0)

> '    Set dbscredan = OpenDatabase("D:\Test
> Credan\newcredan.mdb", False)

>     Workbooks.Open Filename:=FullPath

>     For i = 1 To Worksheets.Count

>         Centre = Worksheets(i).Name
>         TableName = Centre & "tbl"

>         ' Check table exists if does delete it

>         Call CheckTable(TableName)

>         ' Create the table for this tab (centre) with a
> link back to the excel sheet

>         DoCmd.TransferSpreadsheet acLink,
> acSpreadsheetTypeExcel9, TableName, _
>         FullPath, -1, Centre & "!"

>         ' dynamically create sql string to place in main
> qry

>         If i < Worksheets.Count Then

>             qrystr = qrystr & "SELECT " & """" & Centre
> & """" & " as centre,* FROM " & TableName & _
>             " Where Amount Is Not Null And batch Is Not
> Null Union ALL "

>         Else

>             qrystr = qrystr & "SELECT " & """" & Centre
> & """" & " as centre,* FROM " & TableName & _
>             " Where Amount Is Not Null And batch Is Not
> Null;"

>         End If

>     Next i

>     ' check if main query exists if so delete it

>     Call CheckQry

>     ' create main qry

>     Call CreateMainQry(qrystr)

>     ' Close workbook - but not current database

>     ActiveWorkbook.Close
>  '   dbscredan.Close
>     CommitTrans

> End Sub

> Sub CreateMainQry(ByVal qrystr As String)

>     Dim MainQry As QueryDef

>     With dbscredan

>         Set MainQry = .CreateQueryDef("MainQry", qrystr)

>     End With

> End Sub

> Sub CheckTable(ByVal TableName As String)

>     Dim i As Integer

>     With dbscredan

>     For i = 0 To .TableDefs.Count - 1

>         If .TableDefs(i).Name = TableName Then

>             DoCmd.DeleteObject acTable, TableName

>         End If

>     Next i

>     End With

> End Sub

> Sub CheckQry()

>     Dim i As Integer
>     Dim t As Integer

>     With dbscredan

>         For i = 0 To .QueryDefs.Count - 1

>             If .QueryDefs(i).Name = "MainQry" Then

>                 For t = 1 To 10000

>                     t = t + 1 - 1

>                 Next t

>                 DoCmd.DeleteObject acQuery, .QueryDefs
> (i).Name

>             End If

>         Next i

>     End With

> End Sub



Sun, 27 Nov 2005 10:43:00 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. Excel Linking and Excel/Access Linking

2. Problem with opening Excel after opening Excel inside Microsoft Internet Controls

3. EXCEL.exe process staying open

4. Prompts to update links when opening Excel spreadsheet from Access

5. Test for open linked Excel spreadsheet

6. opening excel that has links within it from vb

7. Opening a recordset in a linked table from Excel

8. Opening an excel file through a form/running excel macros through access

9. Refresh Access/Excel connection when Excel opened

10. Excel file opened in IE3 - on one machine starts Excel, others does not

11. Problem opening Excel Addin with Excel application object

12. Outlook VBA - open Excel, read in two fields, send email with these, close excel

 

 
Powered by phpBB® Forum Software