+ Reply to Thread
Results 1 to 12 of 12

Runtime Error 1004 cant find the file, but it knows it's name

  1. 01-22-2016, 10:50 AM #1
    PJC2013 is offline
    Registered User
    Join Date
    09-21-2013
    Location
    Reading, UK
    MS-Off Ver
    Excel 2010
    Posts
    41

    Runtime Error 1004 cant find the file, but it knows it's name

    Hi,

    I get the Runtime Error 1004 cant find "Completed Test Survey a.xls" when running the below VBA

    Please Login or Register  to view this content.
    I'm trying to open about 150 Excel files, copy the data from a column and transpose it into a blank row of a new summary document called ZCollation Tool v1.xlsm.

    Any suggestions?

    Thanks
    Reply With QuoteRegister To Reply
  2. 01-22-2016, 10:53 AM #2
    Norie is offline
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: Runtime Error 1004 cant find the file, but it knows it's name

    Have you tried using the full path?
    If posting code please use code tags, see here.
    Reply With QuoteRegister To Reply
  3. 01-22-2016, 10:56 AM #3
    PJC2013 is offline
    Registered User
    Join Date
    09-21-2013
    Location
    Reading, UK
    MS-Off Ver
    Excel 2010
    Posts
    41

    Re: Runtime Error 1004 cant find the file, but it knows it's name

    rather than (myfile)?

    Just tried it and it says the same but gives file path
    Reply With QuoteRegister To Reply
  4. 01-22-2016, 10:57 AM #4
    Norie is offline
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: Runtime Error 1004 cant find the file, but it knows it's name

    No, I mean replace 'C:\Users\Paul\Documents\........\Completed Surveys\' with the full path, specifically the '........' part, which I don't even think is a valid folder name.
    Reply With QuoteRegister To Reply
  5. 01-22-2016, 10:59 AM #5
    PJC2013 is offline
    Registered User
    Join Date
    09-21-2013
    Location
    Reading, UK
    MS-Off Ver
    Excel 2010
    Posts
    41

    Re: Runtime Error 1004 cant find the file, but it knows it's name

    Quote Originally Posted by Norie View Post
    No, I mean replace 'C:\Users\Paul\Documents\........\Completed Surveys\' with the full path, specifically the '........' part, which I don't even think is a valid folder name.
    In the script it is the full path, I've just shortened it for this post.
    Reply With QuoteRegister To Reply
  6. 01-22-2016, 11:10 AM #6
    bulina2k is offline
    Valued Forum Contributor bulina2k's Avatar
    Join Date
    11-20-2012
    Location
    Urziceni, Ialomita, Romania
    MS-Off Ver
    2019 and 365
    Posts
    867

    Re: Runtime Error 1004 cant find the file, but it knows it's name

    try this way:
    Please Login or Register  to view this content.
    .. and don't forget to have fun!
    Bogdan.

    mark SOLVED and Add Reputation if my answer pleases you
    Reply With QuoteRegister To Reply
  7. 01-22-2016, 11:15 AM #7
    Norie is offline
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: Runtime Error 1004 cant find the file, but it knows it's name

    Right, didn't realise that.

    What you need to do then is include the full path when opening the file.
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  8. 01-22-2016, 11:43 AM #8
    bulina2k is offline
    Valued Forum Contributor bulina2k's Avatar
    Join Date
    11-20-2012
    Location
    Urziceni, Ialomita, Romania
    MS-Off Ver
    2019 and 365
    Posts
    867

    Re: Runtime Error 1004 cant find the file, but it knows it's name

    carefull with that Range("B4:B9").Copy there.. it is not fully referred and it will NOT copy from the new opened file... I would do it this way:
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  9. 01-22-2016, 02:02 PM #9
    PJC2013 is offline
    Registered User
    Join Date
    09-21-2013
    Location
    Reading, UK
    MS-Off Ver
    Excel 2010
    Posts
    41

    Re: Runtime Error 1004 cant find the file, but it knows it's name

    Thanks guys, that's worked.
    One of the cells that it is copying is based on a drop down, so gives a pop up for every file that it opens saying that it already exists "click yes to use that version of the name..."
    Is there a way of making stopping this would a transpose of the value work?
    Last edited by PJC2013; 01-22-2016 at 02:46 PM.
    Reply With QuoteRegister To Reply
  10. 01-22-2016, 03:55 PM #10
    Norie is offline
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: Runtime Error 1004 cant find the file, but it knows it's name

    How is the popup being generated?

    Is it via data validation, code or something else?
    Reply With QuoteRegister To Reply
  11. 01-25-2016, 06:13 AM #11
    PJC2013 is offline
    Registered User
    Join Date
    09-21-2013
    Location
    Reading, UK
    MS-Off Ver
    Excel 2010
    Posts
    41

    Re: Runtime Error 1004 cant find the file, but it knows it's name

    I think it's something else, it's relating to the dropdown options already copied over by the above code.

    Is there a way of amending the code so it only takes values and doesn't overwrite the formatting in place for ZCollation Tool v1.xlsm?
    Reply With QuoteRegister To Reply
  12. 01-25-2016, 06:43 AM #12
    PJC2013 is offline
    Registered User
    Join Date
    09-21-2013
    Location
    Reading, UK
    MS-Off Ver
    Excel 2010
    Posts
    41

    Re: Runtime Error 1004 cant find the file, but it knows it's name

    This is sorted now, I amended the Pastespecial line to include Paste:=xlPasteValues
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Runtime error 1004 when trying to save file via VBA
    By avh in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-09-2015, 03:20 AM
  2. [SOLVED] Receiving Runtime Error 1004 -Cannot Access file
    By Dean Staples in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-30-2013, 09:27 PM
  3. SaveCopyAs Runtime Error 1004 - save a backup without altering open file
    By kamelkid2 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-27-2013, 08:11 PM
  4. [SOLVED] Runtime-error '1004' cannot access the file, depending on the computer
    By yuichi in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-03-2012, 06:05 PM
  5. VBA vlookup won't find numbers - runtime error 1004
    By wonderdunder in forum Excel General
    Replies: 9
    Last Post: 05-26-2011, 04:53 PM
  6. Runtime Error 1004 : file could not be accessed
    By deepyogi in forum Excel General
    Replies: 1
    Last Post: 07-24-2009, 11:54 AM
  7. File Name Causes Runtime Error 1004
    By Robert in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-10-2005, 10:55 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1