+ Reply to Thread
Results 1 to 6 of 6

Trying to Open Files Not Located in Specified Path

  1. #1
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Philadelphia, PA
    MS-Off Ver
    Excel 2010
    Posts
    228

    Trying to Open Files Not Located in Specified Path

    Hello Everyone,

    I have a macro that has run just fine as recently as yesterday, however, when I try to run it today, I keep getting a "Run-time error '1004': Microsoft Excel cannot access the file..."

    My problem is that the file the error message lists is not even in the path that is specified. My code tells it to open any .xls file within a certain folder.

    Does anyone know why the macro would try to open a file that isn't even located in the path? Does this sound like a network or server issue?

    Thanks!

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Trying to Open Files Not Located in Specified Path

    Sounds like the logic that constructs the filename to be opened and/or the command that tries to open the file using the constructed filename is faulty. Show us, and point out where the error lies when you DEBUG, which line of code?
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  3. #3
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Philadelphia, PA
    MS-Off Ver
    Excel 2010
    Posts
    228

    Re: Trying to Open Files Not Located in Specified Path

    JBeaucaire - Could it really be the code though? I used the same exact file with the same exact path yesterday and it worked just fine. I opened it up today, tried to run it without changing anything, and now it doesn't work.

    It is part of a larger macro, but here is the part of code this pertains to:

    Please Login or Register  to view this content.
    The error occurs at "Set oWbk = Workbooks.Open(sPath & "\" & sFil)"

    As you can see in the code, I paste the path of the folder I would like the macro to go to in cell A16 of the "Info" sheet. The macro then goes in to each xls file in that folder and copies a section of the document. But like I said, the error that I'm receiving is telling me that a sheet that isn't even located in the folder does not exist. I had a colleague run the same thing and he got the same error only the error named a different file that didn't exist.

  4. #4
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Trying to Open Files Not Located in Specified Path

    Should that path separator be "/" instead of "\" ?


    Or should it be there at all? Perhaps:
    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Philadelphia, PA
    MS-Off Ver
    Excel 2010
    Posts
    228

    Re: Trying to Open Files Not Located in Specified Path

    sPath definitely needs to be there because that is where the path to the folder is pasted. Since the path is pasted without the "\" at the end, the "\" also needs to be in there.

    I'm really not sure if this is a code issue. This morning I opened up the file, did not change a thing, and it worked fine. Now, 14 hours later, I opened it up again and did not change a thing and its back to giving me the error.

    I guess this might be more of a server issue or something along those lines, but I'm not really sure where else to post this question since it only happens when I run this macro.

  6. #6
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Trying to Open Files Not Located in Specified Path

    Earlier, near the top, you have this line:

    Please Login or Register  to view this content.
    That means you have set Excel's local directory to sPath, whatever that may be. That means you shouldn't need to include the path, you're already in it.

    Please Login or Register  to view this content.
    During the time when the macro is actually working, try that edit, you'll find it is still working.



    Then the next time it ISN'T working, click on DEBUG and carefully hover your mouse over sPath and sFil in the line of code that is highlighted... what are the current values.

    Or press CTRL-G to open the immediate window while that line of code is still highlighted, and type this command into that window?

    ? sPath & "\" & sFil

    What response do you get? Is it exactly right? Even a single errant space (is there one hidden in there anywhere?) will make it not work.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1