dummy Excel instance.

[dgs2001]

Hi

I use vba from a module in an access database to store data from excel workbooks to the database.

This works great except if anybody in my house trys to use excel whilst this code is running.

When that happens the newly opened workbook takes focus and my code hangs.

I think I need to open a hidden dummy instance of excel before I loop through my workbooks but am not sure how to do this.

Any help would be appreciated. Thanks

This is the code I use at the moment.

    Dim objFSO As Object
    Dim appExcel As Excel.Application
    Dim wb As Excel.Workbook
    Dim objFolder As Object
    Dim objFile As Object
    Dim usr As String
    
    usr = Environ$("UserName")
 
    path = "C:\Users\" & usr & "\Documents\tempSheets"
 
    Set objFSO = CreateObject("Scripting.FileSystemObject")
 
    Set appExcel = Excel.Application
    appExcel.DisplayAlerts = False
 
    ' check how many files we are processing
    fc = CountFiles(path)
    Debug.Print "File Count " & fc
 
    'Get the folder object associated with the directory
    Set objFolder = objFSO.GetFolder(path)
 
     'Loop through the Files collection
    For Each objFile In objFolder.Files
    Debug.Print (vbNewLine & "files to process " & fc & vbNewLine)
    Debug.Print (objFile & vbNewLine)
        Set wb = appExcel.Workbooks.Open(objFile)
 
 '''' Import the data to the database
 
   Next
'''Close and release everything.

Thanks Dgs2001

 

[spikepl]

Try
Set appExcel = Excel.Application ->
Set appExcel = CreateObject("Excel.Application")

 

[dgs2001]

Thanks for the reply

However I think I may have confused you, as your solution does not work.


What I wanted to do was lock the instance of excel created with the line

Set appExcel = Excel.Application 
'or
Set appExcel = CreateObject("Excel.Application")

Having searched the net I discovered that you cannot lock an instance of Excel.
The solution apparently is to create Two instance's of excel.

The first instance is a dummy instance which is hidden, and contains no open workbook objects.

The second instance of Excel contains the workbook object Im working with.

Apparently if anybody now opens a workbook it will be attached to the first instance of Excel (the dummy one), and not to the second instance which contains the hidden workbook being imported into my database.

I also need to check for the following when my code has finished :-
Check the dummy instance of Excel and if it contains a workbook object then ignore it. (Somebody else has a workbook open so dont close it !!)



I hope that makes more sense now

Dgs2001

 

[dgs2001]

Ok I'm making progress

I needed to dim another object

 Dim dummyAppExcel As Excel.Application

''' then create  the dummy instance first
Set dummyAppExcel = CreateObject("Excel.Application")
''' followed by the needed instance
Set appExcel = CreateObject("Excel.Application")

This works great when I double click any workbook while my import code is running this new workbook is opened in the dummyAppExcel instance and so my code continues and my source workbook remains hidden

Now all I need is to check whether there is a workbook object held by dummyAppExcel when my code finishes. If there is then I need to ignore this instance and leave it open.

If there isn't then I need to set dummyAppExcel to nothing.

Dgs2001

 

[dgs2001]

I think I have solved this now

I have added the following to check for a workbook being attached to the dummy instance of excel

If dummyAppExcel.Workbooks.count = 0 Then Set dummyAppExcel = Nothing

Thanks Dgs2001