Excel VBA : Run Time Error 1004 : Excel cannot access to file





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}







1















I am creating a macro of the report generator that let the user to save a copy of the file to its destination.



Cell value ("E5") is where the user input the date.
Cell value ("E11") is where user keyin the record name (in this case colour values)



The macro will save it to the location in the C drive



Here are the code :



Sub CTemplate()

'Select up the macro generator
Sheets("File Generator").Select

'Save file according to the textbox values

Dim filename As String
Dim varDatevalue As String
Dim varColourvalue As String

varDatevalue = Range("E5").Value
varColourvalue = Range("E11").Value

ActiveWorkbook.SaveAs filename:="C:Colour Log" & varDatevalue & "--" & varColourvalue & ".xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False


However, there are some problems as I encounter to run time error:
enter image description here



I already tried as followed:




  • Debugging and search for SO but couldn't find any one else with the same problems

  • I already created the folder at the desired locations

  • Uncheck ("Read Only") check box for the file so it can be written


Thank you .










share|improve this question





























    1















    I am creating a macro of the report generator that let the user to save a copy of the file to its destination.



    Cell value ("E5") is where the user input the date.
    Cell value ("E11") is where user keyin the record name (in this case colour values)



    The macro will save it to the location in the C drive



    Here are the code :



    Sub CTemplate()

    'Select up the macro generator
    Sheets("File Generator").Select

    'Save file according to the textbox values

    Dim filename As String
    Dim varDatevalue As String
    Dim varColourvalue As String

    varDatevalue = Range("E5").Value
    varColourvalue = Range("E11").Value

    ActiveWorkbook.SaveAs filename:="C:Colour Log" & varDatevalue & "--" & varColourvalue & ".xlsm", _
    FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False


    However, there are some problems as I encounter to run time error:
    enter image description here



    I already tried as followed:




    • Debugging and search for SO but couldn't find any one else with the same problems

    • I already created the folder at the desired locations

    • Uncheck ("Read Only") check box for the file so it can be written


    Thank you .










    share|improve this question

























      1












      1








      1








      I am creating a macro of the report generator that let the user to save a copy of the file to its destination.



      Cell value ("E5") is where the user input the date.
      Cell value ("E11") is where user keyin the record name (in this case colour values)



      The macro will save it to the location in the C drive



      Here are the code :



      Sub CTemplate()

      'Select up the macro generator
      Sheets("File Generator").Select

      'Save file according to the textbox values

      Dim filename As String
      Dim varDatevalue As String
      Dim varColourvalue As String

      varDatevalue = Range("E5").Value
      varColourvalue = Range("E11").Value

      ActiveWorkbook.SaveAs filename:="C:Colour Log" & varDatevalue & "--" & varColourvalue & ".xlsm", _
      FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False


      However, there are some problems as I encounter to run time error:
      enter image description here



      I already tried as followed:




      • Debugging and search for SO but couldn't find any one else with the same problems

      • I already created the folder at the desired locations

      • Uncheck ("Read Only") check box for the file so it can be written


      Thank you .










      share|improve this question














      I am creating a macro of the report generator that let the user to save a copy of the file to its destination.



      Cell value ("E5") is where the user input the date.
      Cell value ("E11") is where user keyin the record name (in this case colour values)



      The macro will save it to the location in the C drive



      Here are the code :



      Sub CTemplate()

      'Select up the macro generator
      Sheets("File Generator").Select

      'Save file according to the textbox values

      Dim filename As String
      Dim varDatevalue As String
      Dim varColourvalue As String

      varDatevalue = Range("E5").Value
      varColourvalue = Range("E11").Value

      ActiveWorkbook.SaveAs filename:="C:Colour Log" & varDatevalue & "--" & varColourvalue & ".xlsm", _
      FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False


      However, there are some problems as I encounter to run time error:
      enter image description here



      I already tried as followed:




      • Debugging and search for SO but couldn't find any one else with the same problems

      • I already created the folder at the desired locations

      • Uncheck ("Read Only") check box for the file so it can be written


      Thank you .







      excel vba






      share|improve this question













      share|improve this question











      share|improve this question




      share|improve this question










      asked Nov 5 '18 at 2:19









      Alan Koh W.TAlan Koh W.T

      488




      488
























          2 Answers
          2






          active

          oldest

          votes


















          1














          "A filename cannot contain any of the following characters: / : * ? " < > |" - your file name seems to be "5114192C700" which effectively means that you are trying to save your file in a non-existent directory c:Colour Log5114192C700. You have to change the slashes in the file name for other characters.






          share|improve this answer
























          • Sure, at the cell "E5" , I format the date to DD.MM.YYYY format and it works like a charm.

            – Alan Koh W.T
            Nov 5 '18 at 6:16



















          1














          The ' / : * ? < > | [ ] "' Issue



          Sub CTemplate()

          'Always place values, especially text into constants, so you can
          'quickly change them and you don't have to search and change them
          'wherever they appear in the code.
          Const cStrPath As String = "C:Colour Log"
          Const cStrWsName As String = "File Generator"
          Const cStrDateCell As String = "E5"
          Const cStrColorCell As String = "E11"

          Dim arrNope As Variant
          Dim strNope As String

          Dim strFileName As String
          Dim strDate As String
          Dim strColour As String
          Dim intNope As Integer

          'Characters you can't have in a filename
          strNope = " / : * ? < > | [ ] " & Chr(34) 'Chr(34) is double quotes (")
          'You can add other characters like "." if you don't want them in the
          'filename, just make sure to separate the characters and end the string
          'with a space (" ").

          'Paste the characters into an array
          arrNope = Split(strNope)

          'Calculate strings
          With Worksheets(cStrWsName)
          'Loop through the array of characters
          For intNope = LBound(arrNope) To UBound(arrNope)
          'With 'Cstr' you coerce each value to a string data type.
          'With 'Replace' you replace each character with "", practically you
          'delete each 'unwanted' character if it is found.
          strDate = Replace(CStr(.Range(cStrDateCell).Value), _
          arrNope(intNope), "")
          Next
          'Coerce the value to a string datatype
          strColour = CStr(.Range(cStrColorCell).Value)
          End With

          'Calculate filename
          strFileName = cStrPath & strDate & "--" & strColour & ".xlsm"

          'The following line is used only to suppress the error that could occur when
          'a file already exists and at the prompt "No" or "Cancel" is selected.
          On Error Resume Next

          'Save the file
          ActiveWorkbook.SaveAs filename:=strFileName, _
          FileFormat:=xlOpenXMLWorkbookMacroEnabled

          End Sub





          share|improve this answer


























            Your Answer






            StackExchange.ifUsing("editor", function () {
            StackExchange.using("externalEditor", function () {
            StackExchange.using("snippets", function () {
            StackExchange.snippets.init();
            });
            });
            }, "code-snippets");

            StackExchange.ready(function() {
            var channelOptions = {
            tags: "".split(" "),
            id: "1"
            };
            initTagRenderer("".split(" "), "".split(" "), channelOptions);

            StackExchange.using("externalEditor", function() {
            // Have to fire editor after snippets, if snippets enabled
            if (StackExchange.settings.snippets.snippetsEnabled) {
            StackExchange.using("snippets", function() {
            createEditor();
            });
            }
            else {
            createEditor();
            }
            });

            function createEditor() {
            StackExchange.prepareEditor({
            heartbeatType: 'answer',
            autoActivateHeartbeat: false,
            convertImagesToLinks: true,
            noModals: true,
            showLowRepImageUploadWarning: true,
            reputationToPostImages: 10,
            bindNavPrevention: true,
            postfix: "",
            imageUploader: {
            brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
            contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
            allowUrls: true
            },
            onDemand: true,
            discardSelector: ".discard-answer"
            ,immediatelyShowMarkdownHelp:true
            });


            }
            });














            draft saved

            draft discarded


















            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53147490%2fexcel-vba-run-time-error-1004-excel-cannot-access-to-file%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown

























            2 Answers
            2






            active

            oldest

            votes








            2 Answers
            2






            active

            oldest

            votes









            active

            oldest

            votes






            active

            oldest

            votes









            1














            "A filename cannot contain any of the following characters: / : * ? " < > |" - your file name seems to be "5114192C700" which effectively means that you are trying to save your file in a non-existent directory c:Colour Log5114192C700. You have to change the slashes in the file name for other characters.






            share|improve this answer
























            • Sure, at the cell "E5" , I format the date to DD.MM.YYYY format and it works like a charm.

              – Alan Koh W.T
              Nov 5 '18 at 6:16
















            1














            "A filename cannot contain any of the following characters: / : * ? " < > |" - your file name seems to be "5114192C700" which effectively means that you are trying to save your file in a non-existent directory c:Colour Log5114192C700. You have to change the slashes in the file name for other characters.






            share|improve this answer
























            • Sure, at the cell "E5" , I format the date to DD.MM.YYYY format and it works like a charm.

              – Alan Koh W.T
              Nov 5 '18 at 6:16














            1












            1








            1







            "A filename cannot contain any of the following characters: / : * ? " < > |" - your file name seems to be "5114192C700" which effectively means that you are trying to save your file in a non-existent directory c:Colour Log5114192C700. You have to change the slashes in the file name for other characters.






            share|improve this answer













            "A filename cannot contain any of the following characters: / : * ? " < > |" - your file name seems to be "5114192C700" which effectively means that you are trying to save your file in a non-existent directory c:Colour Log5114192C700. You have to change the slashes in the file name for other characters.







            share|improve this answer












            share|improve this answer



            share|improve this answer










            answered Nov 5 '18 at 2:46









            Michal RosaMichal Rosa

            1,3661815




            1,3661815













            • Sure, at the cell "E5" , I format the date to DD.MM.YYYY format and it works like a charm.

              – Alan Koh W.T
              Nov 5 '18 at 6:16



















            • Sure, at the cell "E5" , I format the date to DD.MM.YYYY format and it works like a charm.

              – Alan Koh W.T
              Nov 5 '18 at 6:16

















            Sure, at the cell "E5" , I format the date to DD.MM.YYYY format and it works like a charm.

            – Alan Koh W.T
            Nov 5 '18 at 6:16





            Sure, at the cell "E5" , I format the date to DD.MM.YYYY format and it works like a charm.

            – Alan Koh W.T
            Nov 5 '18 at 6:16













            1














            The ' / : * ? < > | [ ] "' Issue



            Sub CTemplate()

            'Always place values, especially text into constants, so you can
            'quickly change them and you don't have to search and change them
            'wherever they appear in the code.
            Const cStrPath As String = "C:Colour Log"
            Const cStrWsName As String = "File Generator"
            Const cStrDateCell As String = "E5"
            Const cStrColorCell As String = "E11"

            Dim arrNope As Variant
            Dim strNope As String

            Dim strFileName As String
            Dim strDate As String
            Dim strColour As String
            Dim intNope As Integer

            'Characters you can't have in a filename
            strNope = " / : * ? < > | [ ] " & Chr(34) 'Chr(34) is double quotes (")
            'You can add other characters like "." if you don't want them in the
            'filename, just make sure to separate the characters and end the string
            'with a space (" ").

            'Paste the characters into an array
            arrNope = Split(strNope)

            'Calculate strings
            With Worksheets(cStrWsName)
            'Loop through the array of characters
            For intNope = LBound(arrNope) To UBound(arrNope)
            'With 'Cstr' you coerce each value to a string data type.
            'With 'Replace' you replace each character with "", practically you
            'delete each 'unwanted' character if it is found.
            strDate = Replace(CStr(.Range(cStrDateCell).Value), _
            arrNope(intNope), "")
            Next
            'Coerce the value to a string datatype
            strColour = CStr(.Range(cStrColorCell).Value)
            End With

            'Calculate filename
            strFileName = cStrPath & strDate & "--" & strColour & ".xlsm"

            'The following line is used only to suppress the error that could occur when
            'a file already exists and at the prompt "No" or "Cancel" is selected.
            On Error Resume Next

            'Save the file
            ActiveWorkbook.SaveAs filename:=strFileName, _
            FileFormat:=xlOpenXMLWorkbookMacroEnabled

            End Sub





            share|improve this answer






























              1














              The ' / : * ? < > | [ ] "' Issue



              Sub CTemplate()

              'Always place values, especially text into constants, so you can
              'quickly change them and you don't have to search and change them
              'wherever they appear in the code.
              Const cStrPath As String = "C:Colour Log"
              Const cStrWsName As String = "File Generator"
              Const cStrDateCell As String = "E5"
              Const cStrColorCell As String = "E11"

              Dim arrNope As Variant
              Dim strNope As String

              Dim strFileName As String
              Dim strDate As String
              Dim strColour As String
              Dim intNope As Integer

              'Characters you can't have in a filename
              strNope = " / : * ? < > | [ ] " & Chr(34) 'Chr(34) is double quotes (")
              'You can add other characters like "." if you don't want them in the
              'filename, just make sure to separate the characters and end the string
              'with a space (" ").

              'Paste the characters into an array
              arrNope = Split(strNope)

              'Calculate strings
              With Worksheets(cStrWsName)
              'Loop through the array of characters
              For intNope = LBound(arrNope) To UBound(arrNope)
              'With 'Cstr' you coerce each value to a string data type.
              'With 'Replace' you replace each character with "", practically you
              'delete each 'unwanted' character if it is found.
              strDate = Replace(CStr(.Range(cStrDateCell).Value), _
              arrNope(intNope), "")
              Next
              'Coerce the value to a string datatype
              strColour = CStr(.Range(cStrColorCell).Value)
              End With

              'Calculate filename
              strFileName = cStrPath & strDate & "--" & strColour & ".xlsm"

              'The following line is used only to suppress the error that could occur when
              'a file already exists and at the prompt "No" or "Cancel" is selected.
              On Error Resume Next

              'Save the file
              ActiveWorkbook.SaveAs filename:=strFileName, _
              FileFormat:=xlOpenXMLWorkbookMacroEnabled

              End Sub





              share|improve this answer




























                1












                1








                1







                The ' / : * ? < > | [ ] "' Issue



                Sub CTemplate()

                'Always place values, especially text into constants, so you can
                'quickly change them and you don't have to search and change them
                'wherever they appear in the code.
                Const cStrPath As String = "C:Colour Log"
                Const cStrWsName As String = "File Generator"
                Const cStrDateCell As String = "E5"
                Const cStrColorCell As String = "E11"

                Dim arrNope As Variant
                Dim strNope As String

                Dim strFileName As String
                Dim strDate As String
                Dim strColour As String
                Dim intNope As Integer

                'Characters you can't have in a filename
                strNope = " / : * ? < > | [ ] " & Chr(34) 'Chr(34) is double quotes (")
                'You can add other characters like "." if you don't want them in the
                'filename, just make sure to separate the characters and end the string
                'with a space (" ").

                'Paste the characters into an array
                arrNope = Split(strNope)

                'Calculate strings
                With Worksheets(cStrWsName)
                'Loop through the array of characters
                For intNope = LBound(arrNope) To UBound(arrNope)
                'With 'Cstr' you coerce each value to a string data type.
                'With 'Replace' you replace each character with "", practically you
                'delete each 'unwanted' character if it is found.
                strDate = Replace(CStr(.Range(cStrDateCell).Value), _
                arrNope(intNope), "")
                Next
                'Coerce the value to a string datatype
                strColour = CStr(.Range(cStrColorCell).Value)
                End With

                'Calculate filename
                strFileName = cStrPath & strDate & "--" & strColour & ".xlsm"

                'The following line is used only to suppress the error that could occur when
                'a file already exists and at the prompt "No" or "Cancel" is selected.
                On Error Resume Next

                'Save the file
                ActiveWorkbook.SaveAs filename:=strFileName, _
                FileFormat:=xlOpenXMLWorkbookMacroEnabled

                End Sub





                share|improve this answer















                The ' / : * ? < > | [ ] "' Issue



                Sub CTemplate()

                'Always place values, especially text into constants, so you can
                'quickly change them and you don't have to search and change them
                'wherever they appear in the code.
                Const cStrPath As String = "C:Colour Log"
                Const cStrWsName As String = "File Generator"
                Const cStrDateCell As String = "E5"
                Const cStrColorCell As String = "E11"

                Dim arrNope As Variant
                Dim strNope As String

                Dim strFileName As String
                Dim strDate As String
                Dim strColour As String
                Dim intNope As Integer

                'Characters you can't have in a filename
                strNope = " / : * ? < > | [ ] " & Chr(34) 'Chr(34) is double quotes (")
                'You can add other characters like "." if you don't want them in the
                'filename, just make sure to separate the characters and end the string
                'with a space (" ").

                'Paste the characters into an array
                arrNope = Split(strNope)

                'Calculate strings
                With Worksheets(cStrWsName)
                'Loop through the array of characters
                For intNope = LBound(arrNope) To UBound(arrNope)
                'With 'Cstr' you coerce each value to a string data type.
                'With 'Replace' you replace each character with "", practically you
                'delete each 'unwanted' character if it is found.
                strDate = Replace(CStr(.Range(cStrDateCell).Value), _
                arrNope(intNope), "")
                Next
                'Coerce the value to a string datatype
                strColour = CStr(.Range(cStrColorCell).Value)
                End With

                'Calculate filename
                strFileName = cStrPath & strDate & "--" & strColour & ".xlsm"

                'The following line is used only to suppress the error that could occur when
                'a file already exists and at the prompt "No" or "Cancel" is selected.
                On Error Resume Next

                'Save the file
                ActiveWorkbook.SaveAs filename:=strFileName, _
                FileFormat:=xlOpenXMLWorkbookMacroEnabled

                End Sub






                share|improve this answer














                share|improve this answer



                share|improve this answer








                edited Nov 25 '18 at 10:52

























                answered Nov 5 '18 at 6:16









                VBasic2008VBasic2008

                3,5372517




                3,5372517






























                    draft saved

                    draft discarded




















































                    Thanks for contributing an answer to Stack Overflow!


                    • Please be sure to answer the question. Provide details and share your research!

                    But avoid



                    • Asking for help, clarification, or responding to other answers.

                    • Making statements based on opinion; back them up with references or personal experience.


                    To learn more, see our tips on writing great answers.




                    draft saved


                    draft discarded














                    StackExchange.ready(
                    function () {
                    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53147490%2fexcel-vba-run-time-error-1004-excel-cannot-access-to-file%23new-answer', 'question_page');
                    }
                    );

                    Post as a guest















                    Required, but never shown





















































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown

































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown







                    這個網誌中的熱門文章

                    Xamarin.form Move up view when keyboard appear

                    Post-Redirect-Get with Spring WebFlux and Thymeleaf

                    Anylogic : not able to use stopDelay()