Macro to save selection as .csv Run-time error '1004'

  • Hello everyone,


    Hope life's great wherever you are reading this from.


    I am building a very simple macro to be able to export a selection in a sheet to a .csv file utf-8 encoded with ; and "" and field and text delimiters.


    I have the below code so far which is a mash-up of several threads I read on the topic, the idea is that I need to be able to generate those .csv with the name of the sheet as name of the file.


    I get an error message "Run-time error '1004' Cannot access '3. Output-OF0106_01_2016 01 45 PM.csv'."


    I pasted the code below, I would really appreciate if you can help.


    Thanks a million in advance.


    Cheers.


    luck_excel


  • Re: Macro to save selection as .csv Run-time error '1004'


    Hey cytop,


    the file is created by the macro so that's why I posted the code here, I reckon that there is something in the code that makes it fail, which is the fact that it creates a new file but can't save it because it's existing already and is opened.


    Not sure if that makes sense.


    Anyone has an idea?


    Cheers and thanks for your input though.

  • Re: Macro to save selection as .csv Run-time error '1004'


    There is nothing in the code to make it fail unless the workbook or sheet name name contains illegal characters for a filename (but then you would not be able to have saved the workbook in the first place).


    Your example name '3. Output-OF0106_01_2016 01 45 PM.csv' is a valid filename


    Quote

    but can't save it because it's existing already and is opened


    Exactly what I said, so I don't understand the relevance and I'll assume the "Anyone has an idea" means you don't need any other suggestions from me.

  • Re: Macro to save selection as .csv Run-time error '1004'


    Hey sorry I think we started off the wrong foot. I am very basic in my VBA knowledge and you said you hadn't run the code so I assumed you made a high level comment.


    Anyway do you have any idea how to fix the name? It seems that even if I run it on a sheet that has a friendly name it still does not workout properly. I tried remove the .csv part and it still does not solve the deal.


    Go ahead with any suggestion pal ;)

  • Re: Macro to save selection as .csv Run-time error '1004'


    This is untested, but i think switching the separator from "/" to "" may solve your problem?

    Code
    ActiveWorkbook.SaveAs MDir & "\" &


    Let me know if it works
    Caleeco

  • Re: Macro to save selection as .csv Run-time error '1004'


    Hey Calecco,


    Thanks for your suggestion, I tried it already and tried again I'm running on Mac Office 2016 if that makes any difference. Like you I thought the path would be using the \ but it seems that regardless / or \ I get the same error message.


    :)

  • Re: Macro to save selection as .csv Run-time error '1004'


    Quote from luck_excel;771773

    Hey Calecco,


    Thanks for your suggestion, I tried it already and tried again I'm running on Mac Office 2016 if that makes any difference. Like you I thought the path would be using the \ but it seems that regardless / or \ I get the same error message.


    :)


    ahh.... with Mac Office... the filepath structure will be different because it is UNIX. From the limited experience I have with MAC.. i dont believe the / or \ is needed at all... so just remove it. Failing that, have a read of this guide, maybe it'll provide some clues!
    http://www.rondebruin.nl/mac/mac010.htm


    Caleeco

  • Re: Macro to save selection as .csv Run-time error '1004'


    Cytop, probably you have a hint?


    Office 360 licence, I usually don't use that much Office products on Mac but it's part of my new job which is a place full of developer but no one knows VBA here.

  • Re: Macro to save selection as .csv Run-time error '1004'


    I changed the / to Application.PathSeparator and still have the same error.


    Thanks Caleeco for your support :).


    I'll look more in depth to your link.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!