SaveAs Vba Excel 2010

jay_waugh
jay_waugh used Ask the Experts™
on
Experts

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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

Commented:
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
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.

and

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.
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)
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

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 Programming
Top Expert 2015
Commented:
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.

Author

Commented:
Good training tips
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

Commented:
thanks, Jay, and you're welcome ~

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial