Results 1 to 2 of 2

Access VBA - Workbooks.Open, Excel cannot find the file - "Refresh" file system?

  1. #1
    ironfelix717 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    123

    Access VBA - Workbooks.Open, Excel cannot find the file - "Refresh" file system?

    Hi all,

    Please note, this question has been asked here:

    https://bit.ly/2Tv0zCv



    I am exporting a query via DoCmd.ExportSpreadsheet to Excel. After I export this workbook, I must open it and edit some things in code. See below for snippet of code:

    Code:
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, qdf.Name, PathandFilename, True
        MsgBox PathandFilename
        
        Set wBook = appExcel.Workbooks.Open(PathandFilename)
    
    The variable pathandfilename is a path which is obtained by the msofiledialogpicker object. When the code trys to set the wBook object, Access hangs and Excel prompts me with an error message saying:

    "Sorry we couldnt find {the path}. Is it possible it was move, changed or deleted?"

    Now, the most interesting component to this problem is that when I "replace" an existing file with the filepicker, the code runs fine. It is when I create a new file name in the picker where excel cannot find the file after.



    Hoping there is some help!
    Thanks for your time.




  2. #2
    ironfelix717 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    123
    SOLVED:

    My mistake! I should've questioned this!

    The file extension was not being included in the pathandfilename variable when establishing a new workbook because the picker/saver doesn't know what extension you intend to save your file. When overwriting an existing workbook, the pathandfilename extension exists already!

    Stupid me!

    Thanks

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

Similar Threads

  1. "Could not find file <file path." - Error 3024
    By jmitchelldueck in forum Access
    Replies: 4
    Last Post: 07-30-2015, 02:15 PM
  2. Replies: 4
    Last Post: 11-05-2013, 03:46 PM
  3. Replies: 1
    Last Post: 05-23-2012, 10:05 AM
  4. File Names in "Open Recent Database" list
    By Ron.Sul in forum Access
    Replies: 3
    Last Post: 12-29-2011, 10:06 AM
  5. Replies: 0
    Last Post: 12-06-2011, 11:01 AM

Posting Permissions

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