want to convert Macro enabled file to xlsx in my CDO sub

  • Hi

    My CDO works fine to send my file macro enabled, but I want to convert my file to disabled macros as it is being prepared to send with the CDO. Below is the code that I used:

    I need to make this process for the user as simple as possible as my target audience that will be sending the file will be seniors sending data to their doctor. Therefore some information must be automatically deleted when the patient activates the SEND button. The program has about 25 pages each interactive and hard to explain.

    I'm getting errors in the SAVEAS area. I've tried different variations but can't figure out the syntax


    Any help will be appreciated.

    Thanks JimmyB


    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

    Edited 2 times, last by royUK: edit code using correct code tags ().

  • Code
    1. wb.SaveCopyAs FilePath & FileName.xlsx, FileFormat:= _
    2. xlOpenXMLWorkbook, CreateBackup:=False

    The line above taken from your code saves the workbook as .xlsx so code will be removed

  • Hi Roy Sorry for the late reply...crazy busy lately

    I tried what you suggested. When I tried to run it, I got and error: "Invalid qualifier" on the variable FileName. Before I put the extension on it, ".xlsx" the file worked a it did previously, and sent it macro enabled.

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • I've worked through your code.


    You cannot use SaveCopyAs and specify a different file format so I've changed to use saveas but added the date to filename. This workbook is then used as the attachment.


    I've tried to remove unnecessary selecting and feel the code could be shortened more but I haven't time at the moment.


    This code should send a macro free workbook, remember I haven't tested it because I do not have the full workbook so use a backup of your workbook.


  • crossposted: https://www.excelforum.com/exc…sx-in-cdo-code-block.html


    Cross-posting Without Telling Us


    Your post does not comply with our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.


    Post a link to any other forums where you have asked the same question.


    Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.


    Go to https://www.excelguru.ca/content.php?184 to understand why we ask you to do this.


    I have added the crosspost reference for you today. Please comply with this and all our rules in the future

  • Sorry Allan,

    actually I originally posted in this forum and had no replies in the first couple of days earlier on, so I posted in The Excel F?rum. There, I'd advised them that it was cross posted, and didn't succeed with figuring out my problem, with the help that they provided, so I came back here to check if anyone responded and Roy had. In the confusion I forgot to advise Roy that I had cross posted. Sorry JimmyB

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • Roy I just got back in town and will try out you recommendation Tuesday night

    Thanks Jimmy

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • Well. I've been trying for a few hours to make the code work that you provided to no avail. I understand that it is hard for you to go at this, kind of blindly, but the program is about 25 or so worksheets long and interactive amongst each other, so I'm giving you information the best that I can, without creating too much fog. I did although, combine a few lines from yours to mine and have made some progress. Almost there but still a minor problem. The code now converts the file to xlsx. and deletes the sheets that I need deleted, but stops at the last second before sending. I get the error code: "Filename, Directory Name or volume label Syntax is Incorrect". This pops up just at the bottom, highlighting ".Add Attachment FilePath & FileName". I know that you can help improve to streamline the code but I really need it to work (first) LOL. Below is the adjusted code:

    Your usual patience & help will be greatly appreciated

    Thanks so much Jimmy

    (Hitting the sack. Will check back tomorrow)

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • Hi Roy. When I hover over the FileName & FilePath, they are both identified as = ""

    For whatever reason it isn't finding the files that it has created.

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • Hi Roy. Hope you aren't too swamped. I'm so close to getting my program right. One problem still haunting me and I need some help. When I run the program I get the error (line just before the SEND. error= The process cannot access the file because it is being used by another process

    It is converted to xlsx as it should be and all else seems good except for this. Here's the code:

    Any help will be greatly appreciated

    THANKS Jimmy

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • Hi Roy

    It's been 8 days since I last heard from you. If you read my last post, you'll see that we're so very close to solving my problem. I feel like we're one line away from ending this thing. I really need to finish this aspect of the program, before i go on much further. I just can't seem to figure it out. You've worked on this extensively and know what I'm after here. If you don't have time to look at this, please just let me know with a quick reply. It'll be like reinventing the wheel but I will need to cross-post this on a different forum...if you or no one else in this forum can help me.

    Thanks JimmyB

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • Ok Thanks Roy. I'm really having a hard time with this. There are sheets stated for deletion before sending. Instead of using the 'Call' feature I put the lines in the actual program. Here is the code as it stands. right now I'm getting a subscript error and the 'debug' text doesn't show as an option, so I can't say for sure where that error is. GRRRR

    Thanks again for your help.

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)