Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 9 of 9

Thread: UserForm: File/Path Access Error & I/O Error

  1. #1
    Join Date
    19th January 2006
    Posts
    77

    UserForm: File/Path Access Error & I/O Error

    Dear All,

    Please can you help?

    I've rambled on for serveral threads recently about problems I am experiencing with userforms. Finally I have managed to replicate my problem in the smallest file I can.

    First off, the spreadsheet needs to be copied to a directory called "C:\downloads" as it contains a ODBC query to itself (In reality, this is a query to an External Oracle Database)

    On loading, it should pop up a simple userform, with a combo and two command buttons, which when pressed takes you to a (hidden) tab that displays a pivottable.

    All works well until I try to close /save when 60% of the time, Excel encounters problems and closes and will not load up the file the next time until either quit excel or disable macros. Messages include "file/path access error", "I/O Error" or get restarts excel.

    On a casual run through, I expect you might report back that "All worked ok for me". Please can you give it a bit of a thrashing, comment out the userform show, save the file (frequently) becuase i assure you it will break ultimately!

    This is a brand-new file and I've tried it on about 5 different PC running different versions of Excel and generally get the same result.

    I must be doing something wrong.

    I would be extremely grateful for any help / guidance you can provide.

    Sincere regards,


    Pete
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    14th January 2005
    Location
    Cyprus
    Posts
    2,252

    Re: Userform Corruption

    My guess would a memory leak in MS Query. I could not see anything with your sample so I extended the database to 30k lines. I could then see the Excel memory usage slowly increasing with each query. I coded it to repeat and after a while I managed to use most of the memory and and received the i/o error.

    Then I rewrote the query using ADO and put the 30k lines into an Access db. I asked it to query 1000 times. It is still running but the memory looks ok.

    rough code based on Access.

    VB:
    Sub LoadData(TheType As String) 
         ' This sub requires a reference to Microsoft Active X Data Object (ADO) 2.x
         ' In the VBE Tools > references and check the required object
        Dim cn As ADODB.Connection 
        Dim rs As ADODB.Recordset 
        Dim ws As Worksheet 
        Dim sSQL As String 
        Dim DataSource As String 
        Dim FieldCounter As Integer 
         
        DataSource = "c:\Downloads\Test.mdb" 
        Set ws = ThisWorkbook.Worksheets("QueryTable") 
        If TheType = "period" Then 
            sSQL = sSQL + " SELECT Contract, Period, TheValue FROM TestTable" 
             
        Else 
            sSQL = " SELECT Contract,[Employee Name], TheValue FROM TestTable " 
        End If 
        Set cn = New ADODB.Connection 
        cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _ 
        "Data Source=" & DataSource & ";" 
        Set rs = New ADODB.Recordset 
        rs.Open sSQL, cn, adOpenStatic, adLockOptimistic 
        ws.Range("A1:Z65534").ClearContents 
        For FieldCounter = 0 To rs.Fields.Count - 1 
            ws.Cells(1, FieldCounter + 1).Value = rs(FieldCounter).Name 
        Next FieldCounter 
        ws.Range("A2:AZ65534").CopyFromRecordset rs 
         
         ' tidy up
        rs.Close 
        Set rs = Nothing 
        cn.Close 
        Set cn = Nothing 
        Set ws = Nothing 
    End Sub 
    
    

    HTH
    Carl

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,708

    Re: Userform Corruption

    "file/path access error" on UserForms loading is often due to how a Control is being populated on loading.

    Try these MSKB Search Results
    I/O Error
    File/Path Access Error

  4. #4
    Join Date
    19th January 2006
    Posts
    77

    Re: UserForm: File/Path Access Error & I/O Error

    Carl,

    Thank you for your help - much appreciated.

    If you are saying that its something to do with either MSQuery or Excel then that makes me feel a whole lot better because I had assumed it was something I was doing wrong, but couldn't see what - and that was the bit that bothered me!

    However, if it were down to MSquery, wouldn't the clever programmers at microsoft have done something to sort the problem - its inconvenient for me and my "janet & john" spreadsheet, but potentially significant for some developers?

    For fun and as a learning excercise I think I will re-write the query side of the spreadsheet using ADO using your code as a starting point.

    Thanks again,

    Pete

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    19th January 2006
    Posts
    77

    Re: UserForm: File/Path Access Error & I/O Error

    Thanks Dave,

    I look into those.

    Pete

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    14th January 2005
    Location
    Cyprus
    Posts
    2,252

    Re: UserForm: File/Path Access Error & I/O Error

    Hi Pete,

    Hmmm. Thinking about it more, it is possible that the memory leak is due the fact that we are querying an open workbook ? If that were the case then it is strange that I only managed to recreate your i/o error when I ran out of memory ? I'm not so sure now.

    In the long run it is better to use ADO so I would go for that. Check out Dave's links first. I have had a few UserForm issues before that have been fixed by downloading the latest service packs .... so that is a good idea too. It is also a good to avoid using linked cells in controls (load controls/write cells programmatically using the events).

    From what I know MS Query is redundant. There are quite a few problems with it but I doubt they will fix them now.

    Carl

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    19th January 2006
    Posts
    77

    Re: UserForm: File/Path Access Error & I/O Error

    Just in case someone stumbles upon this thread in times to come, an update of my problem.

    Thanks to the help provided by Carl, I have re-coded my spreadsheet to use ADO instead of ODBC queries and am pleased to say that it has appeared to cure the problem.

    I guess the morale of the story, particularly if msquery is dead, is to use ADO - I certainly will in the future.

    Presumably though, Excel 2007 or Office 2007 or whatever, still ships with msquery so microsoft are still intending for it to be used?

    Anyhow, thanks again

    Pete

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,708

    Re: UserForm: File/Path Access Error & I/O Error

    Thanks for sharing Pete. Thanks to Carl for being so helpful.

  9. #9
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,206

    Re: UserForm: File/Path Access Error & I/O Error

    Nigel,

    Posting your question in threads started by others is a violation of the forum rules and is known as thread hijacking.
    ALWAYS start a new thread for your own questions and, if you find it helpful, include a link to this thread, or any other.

    Start a new thread and be sure to give it a title using ONLY search friendly key words that accurately describe your [u]thread content or overall objective[//u].

    Click the link to the forum rules in my signature and take some time to read them.
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Path/File Error Showing UserForm
    By pete_bristol in forum EXCEL HELP
    Replies: 15
    Last Post: September 20th, 2011, 18:21
  2. Path/file access error 75
    By CDT in forum EXCEL HELP
    Replies: 2
    Last Post: September 1st, 2005, 19:10
  3. File/Path Access error
    By sona in forum EXCEL HELP
    Replies: 1
    Last Post: January 29th, 2005, 22:23

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno