button click event

I had to alter my beforesave coding because it was just causing problems when I need to code something. So, instead, I created a button that is titled saved and the end-user clicks on that to save the data they entered in the main form which is an .xlsm format but it saves it as another file name with another file extension. The extension is .xlsx. When I attempt to save, it will say it was saved but then when I try and access the file saved I get this error:

Excel cannot open '123456789101 john doe 10-20-2013.xlsx' because the file format or file extension is not valid. verify the file has not been corrupted and the file extension matches the format of the file.

Again, I cannot put this in a beforesave and had to do a button because beforesave causes issues with other things I am doing with beforesave. So, the button works although I cannot open the files I create without the error. I am sure it has something to do with I need to code something in the button to say I want to save as a macro free workbook?

here is my code:

Please Login or Register  to view this content.

That did not work either. It gives me an error of:
compile error
named arguement not found and that is on the filetype:=51

Well, it almost works, but it renames my original excel which is my template that is not to be renamed. The way I had it set up in the beforesave, anything the nurse enters on the main template when they click the blue disk, it auto saves to another location using the items in 3 cells. cell 1 claim number, cell 2 member name, cell 3 date of service. But, that messes up doing it in beforesave.

So, I created a save button instead and put the code in there so when they click the save button, it is to look at the 3 cells and select those and then saveas a new xls or xlsx in the path listed but not change the name of my template or workbook because they will continue to enter their audit data which could be 50 separate audits, creating 50 separate workbooks all while never chnging the name of my original workbook, original template.


I don't want the current workbook to change its name. I only want what they enter to saveas something else going somewhere else.

That works. But now the next dilemma. Because it saves it as .xlsm, I open another workbook that has 17 macros built to gather all these audits they create, like this week 50 of them and move them into a single workbook so I can copy the fields on the workbook into 1 spreadsheet to import to an Access DB. We have to do it this way. I know there are easier methods but we have no funding so we have to do it for free and it has to be healthcare privacy compliant so cannot use any online free based tools. Excle and Access is it and it has to be within my limited coding ability. I am a SAS programmer.

That being said, now my macros that I have don't work to import the files because they are .xlsm and not .xls or .xlsx.........is there a way to turn it off or change the path upon attempting to import or something??? My macro code to get all these sheets is:

Please Login or Register  to view this content.

This is why I was trying to change the extension in the audit form and not this import the sheets in my other workbook because these macros I set up months ago and have worked just fine until management wanted to make the form more automated for the nurses.....

I did try that. But because it is now back to a .xlsm, macro enabled set of sheets, then it has all these pop ups when I try and import. So I will research online how to disable the macro events so they just import smoothly without any user having to do a bunch of click click click click for the pop-ups or maybe research how to convert on import to .xlsx or something that is macro disabled. Thanks.

I got it. I put in prior to my import of all the sheets code

Please Login or Register  to view this content.