Results 1 to 8 of 8
  1. #1
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904

    UDF Referencing Excel Not Working Properly

    I have an Access database that, among other things, imports a multi-tabbed Excel file into multiple tables in Access. Wanting to make sure that there are no import errors (so that no records get "dropped"), I created a UDF that opens up the Excel file, and counts the number of records on a tab and compares it to the number of records that were imported to the table (and warning messages are returned if the two numbers do not match).



    I thought that I had it all working during testing, but now people are reporting errors, and I can't figure out what is going on. First, here is my UDF:
    Code:
    Function CountRows_Excel(myFileName As String, mySheetName As String, myColumn As String) As Long
    '   Counts the number of data rows in an Excel file using the specified column (excludes first row for header)
    '       myFileName = full file path and name of Excel file
    '       mySheetName = name of Excel sheet containing data you want to count
    '       myColumn = letter of column on the Excel to look at to use to determine the row count (i.e. "C")
        
        Dim excelapp As Excel.Application
        Dim myRange As Range
        Dim myLastRow As Long
        Dim myCount As Long
        
    '   Set and open Excel workbook
        Set excelapp = CreateObject("excel.application")
        excelapp.Workbooks.Open (myFileName)
        
    '   Count number of rows in specified column
        Set myRange = excelapp.Sheets(mySheetName).Range(myColumn & ":" & myColumn)
        On Error GoTo err_Chk
        excelapp.Sheets(mySheetName).Activate
        myCount = Excel.Application.WorksheetFunction.CountA(myRange)
        On Error GoTo 0
        
    '   Ignore first row (header)
        If myCount > 0 Then
            CountRows_Excel = myCount - 1
        Else
            CountRows_Excel = 0
        End If
        
    '   Close Excel workbook
        excelapp.Quit
        Set excelapp = Nothing
        
        Exit Function
        
    err_Chk:
    '   Return which sheet is having error, and list error description
        MsgBox Err.Number & ": " & Err.Description, vbOKOnly, "ERROR ON " & mySheetName & " SHEET!"
        CountRows_Excel = -1
        
        excelapp.Quit
        Set excelapp = Nothing
        
    End Function
    Here is how it is being called:
    Code:
    If CountRows_Excel(myExcelFileName, "Participant", "C") <> DCount("*", "qry15-PT_Export") Then ...
    The myExcelFileName variable is the same one being used to import the data, and data is importing, so I know that the file name is correct. I verified the sheet and column references also. Basically, when I try this, I get the following error message:
    "1004: Unable to get the CountA property of the WorksheetFunction class".

    I also verified that the "Microsoft Excel 12.0 Object Library" is selected in my VB References.

    Any idea of why this might be happening?

    Thanks

    EDIT: I just uncovered a big clue. I get this error when I try to import a ".xlsx" (Excel 2007) file. However, it works fine for ".xls" (Excel 97-2003) files. BTW, the database is an "accdb" file, as I am using Access 2007. Is there some issue with my VBA code that it will not handle "xlsx" files?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    What method are you using for the import - TransferSpreadsheet?

    Could you just set links to the sheets and query the linked sheets for a Count of records? Or even use the link as source for records to update the Access table?

    I tested your function with xlsx file and it worked once. Then I got error "426: The remote server machine does not exist or is unavailable" - weird. I closed/reopened database. The function again worked once then I get your error. The procedure did not kill Excel.EXE in Windows - it still shows up in Task Manager. I ended the process, tried function again and get 426 error. Close/open db, the function works, EXCEL.EXE still running in Task Manager, the function call fails.

    Review this discussion http://forums.aspfree.com/microsoft-...el-413629.html

    I don't know why Quit is not working in your procedure.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Yes, I am using TransferSpreadsheet to import each tab (there are 6 in all). Here is what the first one:
    Code:
    DoCmd.TransferSpreadsheet acImport, 10, "tbl-PT_Records", myExcelFileName, True, "Participant!"
    Could you just set links to the sheets and query the linked sheets for a Count of records? Or even use the link as source for records to update the Access table?
    I haven't done to much with trying to do this dynamically (which is how the links would have to be updated). Basically, I have a Form where the user enters in key data. They can browse/enter the file name they want to import, and that is what gets stored in to the "myExcelFileName" variable.

    So are you suggesting setting up my six tabs as linked tables, and have my VBA code dynamically update those links, and then use the linked tables to get the counts I am after?
    If so, I will see if I can put to use some of the ideas we discussed here earlier this year: https://www.accessforums.net/program...vba-25624.html

  4. #4
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I was talking to another IT guy in our office, and he has another "clue" which may help. He thinks the issue is with this command right here:
    Code:
    excelapp.Workbooks.Open (myFileName)
    He says that "xlsx" files actually have an XML component/definition to them, so that they might not be able to be opened by these traditional methods that were able to be used in previous versions (xls files). If that is the case, then I would just need to find the proper syntax to open the "xlsx" files in my VBA code, and I can use an IF...THEN statement to determine which method to use based on the file extension.

    So, I just need to find out what that syntax might be. I am going to hunt around the net and see what I can find...

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Setting links is a consideration.

    I edited my previous post, perhaps while you were posting yours. Might review it again. Also, another link http://forums.aspfree.com/microsoft-...le-413493.html

    Still testing. I changed code to just make the Excel visible and not Quit. Then I manually closed Excel. The Excel process persists in Task Manager. The process terminates when Access closes. I put your code into a db that already has code that successfully opens and closes an Excel file, at least it used to, haven't used in a long time. I am at a loss as to why this procedure bombs.

    I provided references that demonstrate different syntax. I tried them, the issues persist.

    I tested with an xls file. The first Excel process does not terminate but subsequent executions of the function run and each of these Excel processes terminates. The first process terminates when the db closes.

    Now I notice my old procedure was using xls file. Your IT guy might be onto something. You could do a SaveAs to xls and use that file.

    Eureka! This seems to work. Note that I set the active worksheet before setting the range. The Excel process from first execution still persists until db closes but no problem with repeatedly calling the function for xlsx file:
    Code:
        Dim excelApp As New Excel.Application
        Dim excelBook As New Excel.Workbook
        Dim myRange As Range
        Dim myCount As Long
        
    '   Set and open Excel workbook
        Set excelBook = Workbooks.Open(myFileName)
        
    '   Count number of rows in specified column
        On Error GoTo err_Chk
        excelBook.Worksheets(mySheetName).Activate
        Set myRange = Excel.Range(myColumn & ":" & myColumn)
        myCount = Excel.Application.WorksheetFunction.CountA(myRange)
    More quirkiness. I ran some other code before closing db then when I did close, the Excel app persisted in Task Manager.
    Last edited by June7; 09-26-2012 at 06:40 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    June,

    Thank you so much! I made the modifications to my code, and it works now!!!
    I see what you mean about leaving an Excel app open in the Task Manager, but that seems to go away once the database is closed. So I don't think there should be much reason for concern there.

    Here is the final variation of the code I used. The only other thing I added where statements to close the Excel workbook, and get rid of the application "Quit" statements.
    Code:
    Function CountRows_Excel(myFileName As String, mySheetName As String, myColumn As String) As Long
    '   Counts the number of data rows in an Excel file using the specified column (excludes first row for header)
    '       myFileName = full file path and name of Excel file
    '       mySheetName = name of Excel sheet containing data you want to count
    '       myColumn = letter of column on the Excel to look at to use to determine the row count (i.e. "C")
        
        Dim excelBook As New Excel.Workbook
        Dim myRange As Range
        Dim myLastRow As Long
        Dim myCount As Long
        
    '   Set and open Excel workbook
        Set excelBook = Workbooks.Open(myFileName)
        
    '   Count number of rows in specified column
        On Error GoTo err_Chk
        excelBook.Worksheets(mySheetName).Activate
        Set myRange = Excel.Range(myColumn & ":" & myColumn)
        myCount = Excel.Application.WorksheetFunction.CountA(myRange)
        On Error GoTo 0
        
    '   Ignore first row (header)
        If myCount > 0 Then
            CountRows_Excel = myCount - 1
        Else
            CountRows_Excel = 0
        End If
        
    '   Close Excel workbook
        excelBook.Close
        Set excelBook = Nothing
        
        Exit Function
        
    err_Chk:
    '   Return which sheet is having error, and list error description
        MsgBox Err.Number & ": " & Err.Description, vbOKOnly, "ERROR ON " & mySheetName & " SHEET!"
        CountRows_Excel = -1
        
        excelBook.Close
        Set excelBook = Nothing
        
    End Function

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Did you note my last comment of previous post? Did you observe the same in your testing?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    No, closing the database seems to release the lingering Excel app in the Task Manager.
    My database is all menu driven. The only VBA that they can run is through Command Buttons; one to run the process that uses this code, and one to close the database. I even tried processing multiple files, and closing the database, and it still released it.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Update Query Not Working Properly !
    By hamxa7 in forum Queries
    Replies: 3
    Last Post: 09-20-2012, 04:58 PM
  2. NotInlist Event not working properly
    By thanosgr in forum Programming
    Replies: 2
    Last Post: 06-14-2012, 01:46 PM
  3. access program not working properly!
    By accesshelpme in forum Access
    Replies: 1
    Last Post: 05-13-2012, 03:43 PM
  4. Count or DCount Not Working Properly
    By Chris1112 in forum Forms
    Replies: 3
    Last Post: 05-09-2012, 02:51 PM
  5. Delete SQL statement not working properly
    By Alexandre Cote in forum Programming
    Replies: 3
    Last Post: 10-18-2010, 12:56 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums