Hi, Out of my depth using code to update an Access field from columns in XL 2003 spreadsheet. Works except Excel is still a running task in Windows (XP or 7 x64) after code finishes.
So if user tries to open same XL file manually after code run, there is brief flash on screen then XL runs invisible mode, as it does for the update to Access. But if end XL task in Task Manager, XL file opens correctly.
All code re XL & jist of Access operations is below ["......" replaces common Access code that's irrelevant I think]:

Sub GetXL_Data(Optional bInputBox As Boolean)

Dim xlApp As Excel.Application, xlWBk As Excel.Workbook
Dim xlSheet As Excel.Worksheet, xlRange As Excel.Range 'Dim xl objects
Dim db As DAO.Database
Dim rsHTbl As DAO.Recordset
Dim Unfound(9) As String 'Array 0-9
...........

'OPEN XL FILE
Set xlApp = New Excel.Application 'CreateObject("Excel.Application") is older way?
' open the workbook file
Set xlWBk = xlApp.Workbooks.Open(strPath & strFileName & ".xls")
' select the desired worksheet in the file
Set xlSheet = xlWBk.Worksheets(strSheetName)
xlSheet.Activate
..........
xlApp.Visible = False
..........

XL File names into variables
HWB = xlApp.ActiveWorkbook.Name
HSh = xlApp.ActiveSheet.Name
.........

'Set Access variables
Set rsHTbl = db.OpenRecordset(strHTbl, dbOpenDynaset)
U = -1 'Initialise variable for Unknown data
CopyCol = "A"
'DATA FROM XL SHEET into variable
For HR = 3 To 70 'Rows to search
'Get data from XL (@ row 70, code to repeat for another column)
........................

strFind = "[ID#]=" & vID
With rsHTbl
.MoveFirst
.FindFirst strFind
If .NoMatch Then ..........
.................
End If
.Edit
........................
End With
Next HR

'LIST UNFOUND DATA
If U < 0 Then
MsgBox "ALL DATA COPIED into Access"
GoTo END_UNKNOWN_DATA
End If
If U < 6 Then
Queries = MsgBox("ERRORS with THIS DATA:-" & vbCrLf & _
Unfound(0) & vbCrLf & _
Unfound(1) & vbCrLf & _
Unfound(2) & vbCrLf & _
Unfound(3) & vbCrLf & _
Unfound(4) & vbCrLf & _
Unfound(5) & vbCrLf & _
" in XL SHEET" & vbCrLf & _
"..Fix & ...RE-RUN [this code].", vbOKOnly, "ERROR")
End If
If U > 5 Then
...[similar to < 6 code]
End If

END_UNKNOWN_DATA:
Erase Unfound
U = Empty
Queries = ""

Next 'Another XL Sheet to do?
GoTo ResetVariables

Err_Handler:
.............

ResetVariables:
xlApp.Visible = True
xlWBk.Close
xlApp.Quit
Set xlSheet = Nothing
Set xlWBk = Nothing
Set xlApp = Nothing
Set rsHTbl = Nothing
E: bInputBox = Empty 'If didn't Get Data, this is only var set

End Sub

Hi,

The reason for this week be an unqualified reference to an excel object within your code. However you have truncated your code and I suspect that the offending reference occurs in the code that you've cut.

Repost with the missing code (and please use code tags to make it more readable) and we can help you identify where the issue is.

hth,

d

Not, old in fact most efficient way.
Check first if Excel is already running before creating an instance of Excel.

On exit of procedure test it again

Check again the object you may have not closed like Excel.Application, Excel.WorkSheet, Excel.WorkBook, Excel.Range
Better yet debug this module by pressing F9 on the procedure name.
Happy bug hunting.

'Most efficient' depends on your definition of efficient.

It would be more accurate to say that each method is more efficient depending on the scenario where it is being used:

Using Set xlApp = New Excel.Application is known as 'Early Binding'. It requires that a reference is set to the Excel object in the VBA references. It's advantages are that it is marginally faster than using the CreateObject() method because all the properties and methods are pre-compiled. Coding using early binding is easier because the intellisense in the VBA editor will list all properties/methods/constants. The disadvantage however is that problems can arise in a multi-user situation where different users have machines with different versions of Office/Excel installed (eg some have 2003 and others have 2007)

Using Set xlApp = CreateObject("Excel.Application") and Set xlApp = GetObject( , "Excel.Application") is known as 'Late Binding'. This circumvents the version problem by loading the required Excel library at runtime as its methods are called and will determine the library installed on the particular machine. However, its disadvantages are that this will make the code run (though probably imperceptibly) slower and there is no intellisense when you write the initial code, you must use the numerical values of the intrinsic constants (unless you re-declare them).

As a developer you must weigh up the pros and cons of each method that suits your knowledge and your application best.

The reason for the instance of Excel still running after your code has completed is unrelated to all this - it will be because of an unqualified reference in your code. Post the whole code and we will show you how to identify it.

hth,

d

When you explicitly declare an object: Dim xl as Excel.Application, that is already early-bound.
Late-bound is when you declare it as: Dim xl As Object.

You can try this on code:
Dim xl as Excel.Application
set xl = createobject("Excel.Application")

then type: xl (dot), and all the property will appear in intellisense.

As a rule, I never create an office Application variable but rather create a property so that there's an uniform method of acquiring the application object.

Here's my standard for getting Excel instance. All of my code that does Excel automation then call this procedure to obtain a reference and as a consequence, I only have one Excel instance and there's no need to check whether it's running or not to stop it (as it'll end at the end of Access process unless it's available for user control). In a sense, it's basically the same behavior as CurrentDb() procedure we use.

What a fine code you have there Mr.BananaRepublic, can I pirate your code?
My question is how can you dispose the Static e object?

This post has been edited by arnelgp: Mar 12 2012, 08:40 AM

If I didn't want anyone to take the code, then I really shouldn't have pasted it here, yes? (IMG:style_emoticons/default/smile.gif)

As for disposing, you don't. It's implicitly disposed of when Access quits. If someone opens Access and does stuff that doesn't use Excel, it's never initialized but once they touch any piece of code that calls ExcelApp, then the reference to Excel instance is held on until Access quits. Note that I said _reference_; if you for instance made a new Excel report, and displayed to your user, the user is free to quit the Excel. In this case, the reference will still not be a Nothing but no longer valid (hence the check on its name). But if your user happens to quit Access while Excel is visible and open, Access will release the reference but not the instance itself.

If Excel is hidden and UserControl = 0 then when Access shuts down, the reference will be released and the instance terminated normally.

Just to make it explicit for benefits of those unfamiliar with conditional compilation - LateBind is a flag that one must define. I normally define it in the project properties so it's global to all modules. For a primer, Conditional Compilation will get one started.

this one I learned!

Thanks everyone.
cheekybuddha u put me on right track looking for unqualified ref to XL object. I did post all the XL references, but noticed dim xlRange in 2nd row, but I don't use it, nor set or close it. As soon as I deleted that dim, XL process terminated & same worksheet can be opened manually. I don't know about code tags & couldn't c mention in help on this site. I of course have comment headings for each section of code as posted. Did u mean if posting more code, make sure comment headings, or that more comments needed to explain the code, or what do u mean please?

Everyone U have lost me re late binding, & checking if XL running. The senario: The db & code is a big & the main job (say 2 hours) on the same computer once a week. But before running the main process, db needs to check what updates are from files traditionally in XL (where lot easier to format spreadsheet for printout). XL files each list about 70 people in 2 columns with the person's number in the next cell same row (blank cells after that for handwritten updates to enter into XL). Updates can be names of new people, or new # for any of them. Over time, or with new people, a few names mightn't match (wrong spelling). Code runs to update db from the XL files, then user notified if names not matched & asked to manually open XL to compare to db list of names.

As I set xlAppobject as new instance of XL, I thought quiting xlApp only closed that instance only, no need to check if others instances open. Should I change the code for dim & set XL variables to change when bound? Not large XL files being opened.

BananaRepublic Sorry your post 1:21pm yesterday has gone over my head re Office application variables versus properties. Given the senario above, do u think I should use your code, early binding or not?

Thanks again everyone for the qucik & plentiful replies.

Hi lateron,

I'm glad you were able to find the cause of your problem. (IMG:style_emoticons/default/thumbup.gif)

Code tags are purely for when you write/post code on the forum. It helps make your code more readable by preserving formatting (ie it looks like the code in the VBA editor with a similar font etc.)
You do it by surrounding the code in your post with tags like this:
[code]Your code here[/code]


@arnelgp
Thank you for pointing out the glaring and fundamental omission in my explanation of the difference between early and late binding with regards to the declaration of the object as a specific Excel.Application object or the more generic 'As Object'.

(IMG:style_emoticons/default/thumbup.gif)

d

Thanks again cheekybuddha,

I now what the far right button above forum reply box does!

Everyone any ideas if I need to check for other instances of XL or change binding for use of file as in my last post? Or don't try to fix what's not broken?

This is really up to you to decide what is appropriate.

In my experience, there will be more than one procedure that involves some kind of Excel automation, so having one consistent way to retrieve an Excel instance helps a lot in ensuring that all procedures automating Excel behaves the same. Furthermore, I typically have Excel generate a spreadsheet to some kind of specifications, then display the spreadsheet to the user. Once user take over the control of Excel, there is no way for me to know with any certainty what state the Excel is in (e.g. they could close it at any time, closed the original spreadsheet, etc. etc.). By consistently checking for existing instance first before creating a new one, I can be assured that I've at least made the effort to keep number of Excel instances to a minimum.

Now, a case can be made for creating a new instance of Excel, especially if it's meant to run without any output or intervention by user, and that can be useful in case where you do not want users to see their Excel changing (e.g. opening another spreadsheet, losing their place on the active spreadsheets, etc.). So, it really depends on what you actually to do with Excel during you're automating it and what will happen once you're done with it.

You could even adopt a hybrid approach where you maintain private instance of Excel that's designated for Access' use in automation, then when you need to expose spreadsheet to the user, use the GetObject/CreateObject to either retrieve an existing instance created by user and display them the spreadsheet. However, the work required is typically not worth it in ROI, and most of times, it suffices to simply retrieve an existing Excel instance and automates, especially because I typically do it with a new spreadsheet and I've not had my users complain to me about losing their place -- I think they even expect that since they asked to generate a new spreadsheet.

I hope that the above help illustrates the considerations you have to think about what you're going to do with your Excel instance and what is the best approach.

Best of luck!

BananaRepublic, thanks for taking the time to explain in simplier terms. I'm thinking of hybrid of parts of above code & my original. Thanks everyone for your time & help.

Lateron