none
maximum calls to dsofile open method? RRS feed

  • Question

  • Hello.  I'm using VBA in Excel and am having some dsofile troubles using its open method.  The code works great until it gets called the 5012 th time then I get the error:  Method 'Open' of object '_OleDocumentProperties' failed. Am I hitting a maximum number of calls to that in the sub?  If so, am I missing something to close it after I use it?  See code; I tried 2 different things I found elseware to try and close it, but both create errors themselves.  I need to read > 60000 files from a server and capture their custom properties we have.

    I need help ASAP.  Please!!!!  Thanks ahead of time.

    -Bob

    Sub ListMyFiles(folderPath, IncludeSubfolders)
        If folderPath = "" Then Exit Sub
        Set fsObject = New Scripting.FileSystemObject
        Set folderInfo = fsObject.GetFolder(folderPath)
        
        For Each FileInfo In folderInfo.Files
            Set fileDSOinfo = New DSOFile.OleDocumentProperties
            dummy = fileDSOinfo.Open(FileInfo, True, dsoFileOpenOptions.dsoOptionOpenReadOnlyIfNoWriteAccess)

         ' Here goes a bunch of code to read the properties and put them into cells.  Works fine.

        '        fileDSOinfo.Close (True)   <--- tried this but generates an error
        '        fileDSOinfo = Nothing     <--- tried this but generates an error
        Next
        If IncludeSubfolders Then
            For Each subFolder In folderInfo.SubFolders
                Call ListMyFiles(subFolder.path, True)
            Next
        End If
    End Sub

    Saturday, April 12, 2014 3:50 AM

Answers

  • Cindy,

    Thank you very much for your replies and suggestions!

    I read that link and it's looks like it's getting too involved.  What I decided to do is just add a ERR column to my table where I can mark files, like the ones I noted and others, that have a problem getting read.  We can filter on that to look for problem files and handle them manually.  I'm going to mark this thread as 'answered.'

    Since you've been so helpful, and if you are looking for something to do... :)  ... I started a new thread in Excel Developer forum related to the same code I'm working on, but with a different problem.

    Excel VBA Getting file attributes from network files copying files to my computer

    Thanks again!

    -Bob


    Robert J Staas, Sr.

    • Marked as answer by RsIsMe Monday, April 14, 2014 8:26 PM
    Monday, April 14, 2014 8:26 PM

All replies

  • I don't know if there is an upper limit, but it's certainly possible...

    Have you tried setting the objects you're creating to Nothing in order to release them, in the reverse order they were created? Before the Next line:

    dummy = Nothing
    fileDSOinfo.Close (True)
    fileDSOinfo = Nothing

    If you're still getting an error, what's the error message?


    Cindy Meister, VSTO/Word MVP, my blog

    Saturday, April 12, 2014 4:34 PM
    Moderator
  • Thanks for replying.

    Both dummy=Nothing and fileDSOinfo=Nothing give the error:  Object variable or With block variable not set.

    fileDSOinfo.Close (True)  gives the error: The command is not available because document was opened in read-only mode.


    Robert J Staas, Sr.

    Saturday, April 12, 2014 8:14 PM
  • To anybody.

    I was chasing ghosts with  a probem calling dsofile. too many times.  Though, it still seems like the thing should have a .close statement since it's getting opened.  But, like I said in my previous post .close generates an error.

    The true problem is coming from trying to read Excel files that have an incorrect extension.  Now, a bad file was my first thought and I removed the file it was erroring on before I ever posted.  It turns out it's hitting a whole directory of bad excel file types.  I found this out by manually trying to open these files and getting the following error:  "The file you are trying to open, "filename", is in a different format than specified by the file extension..."

    Ok, so that makes sense to me in that if there is a file extension problem, the dsofile thing can't read it and it crashes.  BUT, I looked around more and found a bunch of Excel files (this a big problem at work) that have this same file type problem and the dsofile open call DID work fine.  So, I don't understand what the difference is in it working in some cases and crashing in other cases.

    Here's two examples.  Both give me the error I just noted when I try and open them manually.

    FAILS:  dummy = fileDSOinfo.Open("C:\Users\u539751\Documents\My Web Sites\MSR\Templates\ITC\_vti_cnf\MSR Campaign Summary v2r18_ITC.xltm", True, dsoFileOpenOptions.dsoOptionOpenReadOnlyIfNoWriteAccess)

    WORKS:  dummy = fileDSOinfo.Open("C:\Users\u539751\Documents\My Web Sites\MSR\Templates\Archive\_vti_cnf\MSR Campaign Summary v2r17.xlt", True, dsoFileOpenOptions.dsoOptionOpenReadOnlyIfNoWriteAccess)

    Any ideas on why one would work and one doesn't?

    Any idea on how to actually fix these errors on the file? It always says it's the wrong file type, but I can never find a file type that works when I try saving it.

    Thanks!

    -Bob


    Robert J Staas, Sr.

    Saturday, April 12, 2014 10:23 PM
  • Hi Bob

    Hmm. Are you able to open both of the files ("Fails" and "Works") in Excel?

    The one thing I notice is that the extension of the first of these is in the new file type, for Excel 2007 and later, while that of the second is for Excel 2003 and prior versions. DSOFile was developed for the binary file formats, not the Open XML file formats. It should work with the latter, but you may need to do some tweaking. See, for example:

    http://blogs.msdn.com/b/vsod/archive/2012/10/20/considerations-when-using-dsofile-on-64-bit-operating-systems.aspx


    Cindy Meister, VSTO/Word MVP, my blog

    Sunday, April 13, 2014 5:54 PM
    Moderator
  • Cindy,

    Thank you very much for your replies and suggestions!

    I read that link and it's looks like it's getting too involved.  What I decided to do is just add a ERR column to my table where I can mark files, like the ones I noted and others, that have a problem getting read.  We can filter on that to look for problem files and handle them manually.  I'm going to mark this thread as 'answered.'

    Since you've been so helpful, and if you are looking for something to do... :)  ... I started a new thread in Excel Developer forum related to the same code I'm working on, but with a different problem.

    Excel VBA Getting file attributes from network files copying files to my computer

    Thanks again!

    -Bob


    Robert J Staas, Sr.

    • Marked as answer by RsIsMe Monday, April 14, 2014 8:26 PM
    Monday, April 14, 2014 8:26 PM
  • Hi Bob

    I'm actually a Word person, not Excel... and I'm pretty busy :-) 

    However, if your problem is mainly due to the new file formats you might consider working with the Open XML file format for accessing information stored in the files.


    Cindy Meister, VSTO/Word MVP, my blog

    Tuesday, April 15, 2014 5:09 PM
    Moderator