Jump to content


 


Register a free account to unlock additional features at BleepingComputer.com
Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.


Click here to Register a free account now! or read our Welcome Guide to learn how to use this site.

Photo

Vba Code For "save As"


  • Please log in to reply
1 reply to this topic

#1 IrishLefty

IrishLefty

  • Members
  • 24 posts
  • OFFLINE
  •  
  • Local time:12:57 AM

Posted 30 April 2008 - 09:33 PM

I want to put a button marked "SAVE AS" on my spreadsheet, and when it's clicked have the macro read the contents of Cell A1, add .xls to it and incorporate it in the Save As function. In other words, I need to speed up the Save As process such that all I have to do is type the name in Cell A1 and click the SAVE AS Button to save the document. Any ideas? I'm using Excel 2007.

Thank you

Edited by IrishLefty, 30 April 2008 - 09:33 PM.


BC AdBot (Login to Remove)

 


#2 adh_amit

adh_amit

  • Members
  • 20 posts
  • OFFLINE
  •  
  • Local time:02:27 PM

Posted 12 May 2008 - 02:05 AM

Try this code:

Sub main()
fileI = ActiveSheet.Cells(1, 1).Value
FileN = fileI + ".xlsm"

ActiveWorkbook.SaveAs Filename:=FileN, FileFormat:=52

End Sub

Filefomat = 52 means file type is Open XML Workbook Macro Enabled. Excel2007 wont let you save the workbook in any other fomat since it contain a macro.

About the button part, I would suggest having a keyboard shortcut when you write this macro.e.g if Ctrl+T is your shortcut,
once you type Ctrl T, the current workbook will be saved to A1value.xlsm.

If you also want to close the file as soon as you save, add one more line to the code
activeworkbook.close savechanges:= true


Hope this helps.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users