Importing Automation

[Excel_Query]

Hey,

I have this code set up to import all the excel files from a certain
folder, and since these files are protected, i had to place some extra
code in there to open-unprotect-import and close each excel file it
wants to import, which works perfectly fine...

Except that although the excel application is closed it is for some reason still running in the background which causes those particular excel files to open only in read only...unless you ctrl+alt+del and force excel off from the processes... Any ideas on that??

Now The second issue is that i setup that do loop in order to reiterate
throughout the folder and import a specific range from all the xls files there,

But I have a user list table with the fields of 'Name', 'Active' (a checkbox), and 'FileName' pretaining to each user

How would I be able to place conditions within my current code that would only import any user that is active on that table, ofcourse it would have to use the 'FileName' field for each user and concatonate that with the strPath and StrFile

here is the code...

Option Compare Database 
Public xlapp As New Excel.Application 

Public Sub ImportAll() 
    Dim strPath As String 
    Dim strFileName As String 
    Dim strPass As String 
    
    strPath = "G:\C\B\T\"                   'Set Path 
    strFileName = Dir(strPath & "*.xls")    'Set first file 

    Do 
        On Error GoTo ErrTrp 
    
        DoCmd.TransferSpreadsheet acImport, 8, "Test 2", strPath & strFileName, True, "Access_Upload!C13:L34" 
        
ErrTrp: 
        If Err.Number = 3161 Then        'Encription error so unprotect workbook 
            xlapp.Visible = False                       'Open Excel 
            xlapp.EnableEvents = False              'Disable Events (Macro's) 
            xlapp.workbooks.Open strPath & strFileName    'Open File 
            xlapp.ActiveWorkbook.Unprotect (strPass)       'Unprotect 
            
            'Try and Import again 
            DoCmd.TransferSpreadsheet acImport, 8, "Test 2", strPath & strFileName, True, "Access_Upload!C13:L34" 

            xlapp.ActiveWorkbook.Protect (strPass)   'protect      
            xlapp.ActiveWorkbook.Save                   'Save 
            xlapp.EnableEvents = True                    'Enable Events 
            xlapp.ActiveWorkbook.Close                  'Close File 
            xlapp.Quit                                           'Quit Excel 
            
        Else 
        End If 
        
        strFileName = Dir()                         'look for next file 
        
        If strFileName = "" Then                    'no more files 
            Exit Do 
        End If 
        
    Loop 

End Sub

Thanks in advance,

Ayyad

 

[richary]

I'm taking a bit of a stab at this (hope you don't mind!)

xlApp is declared as a global, but it appears to be quit'ed in

            xlapp.Quit                                           'Quit Excel

which seems to be inconsistent.

I would consider making xlApp a local variable and would move the line above to just before you exit the Sub.
Also, is there any point in saving the workbook, since you only import the data rather than changing anything?

 

[Excel_Query]

Richary, I localized xlApp and placed the quit command in the place you mentioned and also tried other places to put it in ... sometimes even inserting it twice, but unfortunetly nothing worked, 'EXCEL.exe' still showed up in the processes

As for saving, you are right... there is no point, but if i dont do that, excel would ask the user if they wanted to save the file for every file... since Idid unprotect it

Do you know the code for letting it automatically choose 'No' Everytime excel asks? because then i would not have to place that save command, which could be causing the problem with that excel.exe running in the processes

Ayyad

 

[Steve2003]

Ayyad,

Sorry Richary if I am interferring but I use would use

xlapp.ActiveWorkbook.Close savechanges:=false

As for your first question I have the same problem.

Steve

 

[Bat17]

I would try adding
xlapp.UserControl = False
before quitting the App

HTH

Peter