SaveAs Vba Excel 2010


I have a excel 2010 .xlsm workbook that I have a button on to "export" data.

I am doing this by using the

ThisWorkbook.SaveCopyAs (filename)

Command and then removing the Sheets that are not required.

The file saves ok but then when I try and open the export, when either xls or xlsx or csv I get an error saying that the file is "in a different format than the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file.

I. was going to remove all macros code from ThisWorkbook to get around this but am struggling to do that too as I get a "Programmatic access to Visual Basic Project is not trusted"

Sub removecode()
ThisWorkbook.VBProject.VbComponents("ThisWorkbook").CodeModule.DeleteLines 1,ThisWorkbook.VBProject.VbComponents("ThisWorkbook").CodeModule.CountofLines
End sub

Can someone help me with the logic or the code.

Many thanks.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
to solve the trust issue:

File > Options > Trust Center

click the Trust Center Settings command button

on left sidebar: Macro Settings
check --> Trust access to the VBA project object model
Excel amusantCommented:
Navigate to File Tab, and then click Excel Options--> Trust Center -->Trust Center Settings --> Privacy Options.

Clear the Check Office documents that are from or link to suspicious Web sites check box.


Navigate to File Tab--> Options-->Trust Center --> Trust Center Settings --> Trusted Locations.

Click Add new location.

Click Browse to find the folder you wish to add as trusted location, select the folder, and then click OK.
jay_waughAuthor Commented:
Thanks for the feedback. Sadly I don't seem to have the rights to

"Click Add new location."

Is that the only option?

Down a similar route I am struggling to code the below can you assist? I keep getting an error saying

Compile error: Expected:=

pathfilename = "c:\temp\output"

ThisWorkbook.SaveAs (Pathfilename,xlExcel12,,,False,false,xlNoChange,xlUserResolution,false,,true)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

jay_waughAuthor Commented:
Was a simple case of removing the brackets to get the code to work.

ThisWorkbook.SaveAs Pathfilename, xlExcel12
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
thanks for sharing your solution, Jay ~

remember to only use parentheses (which might also be called brackets) if you are handling a return value.  When you are just issuing a command, like Save, you do NOT use parentheses  ~ only commas to separate parameters.  

Only use parentheses if you are handling a return value. For example, this does not use parentheses since no return value is being handled:
MsgBox "Just saying something, all you can do is press OK"

Open in new window

On the other hand, this use is testing a return value so parentheses are needed around the arguments:
if MsgBox("Do you want to do this?", vbYesNo, "My Title") = vbYes then
   'blah blah
end if

Open in new window

 Each individual parameter CAN have parentheses around it.  I find it best, however, to evaluate everything first and only use variables in the actual statement to save, thus alleviating the need to prioritize execution with parentheses.
jay_waughAuthor Commented:
Good training tips
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
thanks, Jay, and you're welcome ~
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.