Run-time error '1004': The file could not be accessed. Excel template wont save & I don't know why

  • Hey everyone!


    This is my first post so I apologize if I have posted wrong/not followed guidelines.


    I am having trouble with saving my excel file. I have used SaveAs before & it has always worked fine so I would like your help with solving this please!


    Background: I am creating a file which uses an excel template to create multiple files. I need to create 10-17 files each month with new data from SAP, so to save time I thought of creating one file which uses Do loop (for the changing variable of company code) to do this. It incorporates SAP Scripting. The debugger pop up box says the following:

    "Run-time error '1004':

    The file could not be accessed. Try one of the following:

    - make sure the specified folder exists.

    -make sure the folder that contains the file is not read-only.

    make sure the filename and folder path do not contain any of the following characters: < > ? [ ] : or *

    -make sure the filename and folder path do not contain more than 218"


    As mentioned I am opening an excel template to do this & saving the file once I input some information. I am then calling the macro which has the SAP scripting in it. The reason I am saving prior to calling the macro is so that in the SAP scripting macro is it easy to distinguish between my files. The suggested name with path is only 133 characters. I have removed the path for privacy of the company.


    The code is:

    Please help! Is there anything clearly wrong with the code which can explain why I am having this error!?


    Thanks ,


    Mel:)

  • royUK

    Changed the title of the thread from “HELP! Run-time error '1004': The file could not be accessed. Excel template wont save & I don't know why” to “Run-time error '1004': The file could not be accessed. Excel template wont save & I don't know why”.
  • If you save a file as a template file then it should be available from File -> New so why do you need VBA to open it.


    When you save the opened file with VBA you need to include the FileFormat


    51 = xlOpenXMLWorkbook (without macro's in 2007-2016, xlsx)

    52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007-2016, xlsm)

    50 = xlExcel12 (Excel Binary Workbook in 2007-2016 with or without macro's, xlsb)

    56 = xlExcel8 (97-2003 format in Excel 2007-2016, xls)


  • Hi Roy,


    Thanks for this! That is great however as this needs to run for 10-17 files, I do not want to write the name for every file as I would like the macro to run in the background. Is there a code which brings up the file dialog to select my path for the files to save in & then save the file name as "(company code) TB Flux 2020 Q1" where the company code changes based on this: Work.Cells(i, 4).


    Also, I use VBA to open the template because it contains a format which I need to use for every country/company code. So the template brings in information about the accounts I am analysis. If you agree that there is a better way could you please explain further how & why.


    In addition, could you please explain why you need to use the if statement? Sorry if this seems like a silly question, as mentioned I am pretty novice with VBA & it is all self taught from online forums so there are several gaps in my knowledge. Also, thanks for the links to your website! These will definitely come in handy & I will be sharing this with the individuals in my company who are keen to learn more!


    Thanks,


    Mel

  • I'm not sure why you have declared TBSAVE as a public variable.


    You could have something like this. I'm assuming Work is sheet name.


    Code
    1. Work.Cells(i, 4)&" TB Flux 2020 Q1"


    It's usual that a template is already formatted ready for use.


    The If statement is to allow for the user cancelling or not making a selection