Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SaveAs - - provide option to Save As .xlsm or .xls
I have the following snippet of code
filesavename = Application.GetSaveAsFilename( _ InitialFileName:=SuggestedName & ".xls", _ FileFilter:="Excel Files (*.xls), *.xls") How would I need to change this supply a suggestedname for either XLS or XLSM files. When I use this, it's only .XLS I think what I'm wanting is a File Filter that includes xls and xlsm. Can this be done? If so, I guess I need to change the initial file name somehow. Suggestions? Thanks, Barb Reinhardt |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SaveAs - - provide option to Save As .xlsm or .xls
I think that your filefilter should be:-
"Excel Files (*.xls;*.xlsm), *.xls;*.xlsm" -- Alan Moseley IT Consultancy http://www.amitc.co.uk If I have solved your problem, please click Yes below. Thanks. "Barb Reinhardt" wrote: I have the following snippet of code filesavename = Application.GetSaveAsFilename( _ InitialFileName:=SuggestedName & ".xls", _ FileFilter:="Excel Files (*.xls), *.xls") How would I need to change this supply a suggestedname for either XLS or XLSM files. When I use this, it's only .XLS I think what I'm wanting is a File Filter that includes xls and xlsm. Can this be done? If so, I guess I need to change the initial file name somehow. Suggestions? Thanks, Barb Reinhardt |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SaveAs - - provide option to Save As .xlsm or .xls
hi Barb
See http://www.rondebruin.nl/saveas.htm See the second macro -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Barb Reinhardt" wrote in message ... I have the following snippet of code filesavename = Application.GetSaveAsFilename( _ InitialFileName:=SuggestedName & ".xls", _ FileFilter:="Excel Files (*.xls), *.xls") How would I need to change this supply a suggestedname for either XLS or XLSM files. When I use this, it's only .XLS I think what I'm wanting is a File Filter that includes xls and xlsm. Can this be done? If so, I guess I need to change the initial file name somehow. Suggestions? Thanks, Barb Reinhardt |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
SaveAs - - provide option to Save As .xlsm or .xls
I believe I tried that and it didn't work. I did find the answer herer
http://www.rondebruin.nl/saveas.htm Thanks, Barb Reinhardt "Alan Moseley" wrote: I think that your filefilter should be:- "Excel Files (*.xls;*.xlsm), *.xls;*.xlsm" -- Alan Moseley IT Consultancy http://www.amitc.co.uk If I have solved your problem, please click Yes below. Thanks. "Barb Reinhardt" wrote: I have the following snippet of code filesavename = Application.GetSaveAsFilename( _ InitialFileName:=SuggestedName & ".xls", _ FileFilter:="Excel Files (*.xls), *.xls") How would I need to change this supply a suggestedname for either XLS or XLSM files. When I use this, it's only .XLS I think what I'm wanting is a File Filter that includes xls and xlsm. Can this be done? If so, I guess I need to change the initial file name somehow. Suggestions? Thanks, Barb Reinhardt |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
SaveAs - - provide option to Save As .xlsm or .xls
I've FINALLY bookmarked your site. I've found a lot of useful code there.
Thanks for posting all of it. Thanks, Barb Ron de Bruin" wrote: hi Barb See http://www.rondebruin.nl/saveas.htm See the second macro -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Barb Reinhardt" wrote in message ... I have the following snippet of code filesavename = Application.GetSaveAsFilename( _ InitialFileName:=SuggestedName & ".xls", _ FileFilter:="Excel Files (*.xls), *.xls") How would I need to change this supply a suggestedname for either XLS or XLSM files. When I use this, it's only .XLS I think what I'm wanting is a File Filter that includes xls and xlsm. Can this be done? If so, I guess I need to change the initial file name somehow. Suggestions? Thanks, Barb Reinhardt |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
SaveAs - - provide option to Save As .xlsm or .xls
Ron,
I tried using several versions of the code that you propose, but I still have the same recurring problem. Here is my situation... I am doing a template for Expense Reports for everybody in my office. Some people have office '03 installed, some have office '07 installed. I have a macro which saves the file into a user-specific folder when an on-screen button is clicked. It works fine for the Office '03 users, but since I am using macros, the users running office '07 need to save as a "Macro-Enabled Worksheet" (*.xlsm). For these users, this causes an error box reading: The following features cannot be save in macro-free workbooks: VB Project To save a file with the features, click No, and then choose a macro-enabled file type in the File Type list. To continue saving as a macro-free workbook, click Yes [ YES ] [ No ] [ Help ] Here is a section of the code that I have for the macro (Cases for several other users were removed for simplicity). Please help me! Feel free to email me directly at ryanjgeorge *at* gmail *dot* com -----------Code--------------- Sub SelectSaveFileName() 'When the user clicks the button, the workbook is automatically named and saved Dim SaveAs As Variant ThisFile = Range("I4").Value 'this cell contains the date information User = Range("C6").Value 'this cell contains the user's name Select Case User Case "Luke" 'Luke has Office '03 SaveLoc = "W:\Customers\General\" 'Where Luke saves his files Case "Ryan" 'Ryan has Office '07 SaveLoc = "X:\Expenses\" 'Where Ryan saves his files Case Else SaveLoc = "C:\Expenses\" 'Where all other users save their files End Select SaveAs = Application.GetSaveAsFilename(SaveLoc & ThisFile & ".xlsm", filefilter:= _ " Excel Macro Free Workbook (*.xlsx), *.xlsx," & _ " Excel Macro Enabled Workbook (*.xlsm), *.xlsm," & _ " Excel 2000-2003 Workbook (*.xls), *.xls," & _ " Excel Binary Workbook (*.xlsb), *.xlsb", _ FilterIndex:=2, Title:="Choose Save Location") If SaveAs = False Then MsgBox User & " cancelled save" Else ActiveWorkbook.SaveAs Filename:=ThisFile End If End Sub -----------Code--------------- "Ron de Bruin" wrote: hi Barb See http://www.rondebruin.nl/saveas.htm See the second macro -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Barb Reinhardt" wrote in message ... I have the following snippet of code filesavename = Application.GetSaveAsFilename( _ InitialFileName:=SuggestedName & ".xls", _ FileFilter:="Excel Files (*.xls), *.xls") How would I need to change this supply a suggestedname for either XLS or XLSM files. When I use this, it's only .XLS I think what I'm wanting is a File Filter that includes xls and xlsm. Can this be done? If so, I guess I need to change the initial file name somehow. Suggestions? Thanks, Barb Reinhardt |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
SaveAs - - provide option to Save As .xlsm or .xls
Ryan,
If you are using this syntax in Excel 2007 ActiveWorkbook.SaveAs Filename:=ThisFile You'll have errors when you go to open the file. You need to also define a FileFormat which is different for each extension (.xls, .xlsx, .xlsm, etc). Ron has provided a "cheat sheet" of sorts here http://www.rondebruin.nl/saveas.htm If you plan to save the file with the same extension, his code will work as written for you. If, however, you want to change the file extension (.xls to ..xlsm), you'll need to figure out the file format that goes with the extension before you save. I generally do something like this if Val(Application.Version) =12 then ActiveWorkbook.SaveAs Filename:=ThisFile, FileFormat=:myFileFormat else ActiveWorkbook.SaveAs Filename:=ThisFile end if If you have other questions, come back. And if I'm making this too complicated, I'm sure Ron will set me straight. ;) HTH, Barb Reinhardt "RyanG" wrote: Ron, I tried using several versions of the code that you propose, but I still have the same recurring problem. Here is my situation... I am doing a template for Expense Reports for everybody in my office. Some people have office '03 installed, some have office '07 installed. I have a macro which saves the file into a user-specific folder when an on-screen button is clicked. It works fine for the Office '03 users, but since I am using macros, the users running office '07 need to save as a "Macro-Enabled Worksheet" (*.xlsm). For these users, this causes an error box reading: The following features cannot be save in macro-free workbooks: VB Project To save a file with the features, click No, and then choose a macro-enabled file type in the File Type list. To continue saving as a macro-free workbook, click Yes [ YES ] [ No ] [ Help ] Here is a section of the code that I have for the macro (Cases for several other users were removed for simplicity). Please help me! Feel free to email me directly at ryanjgeorge *at* gmail *dot* com -----------Code--------------- Sub SelectSaveFileName() 'When the user clicks the button, the workbook is automatically named and saved Dim SaveAs As Variant ThisFile = Range("I4").Value 'this cell contains the date information User = Range("C6").Value 'this cell contains the user's name Select Case User Case "Luke" 'Luke has Office '03 SaveLoc = "W:\Customers\General\" 'Where Luke saves his files Case "Ryan" 'Ryan has Office '07 SaveLoc = "X:\Expenses\" 'Where Ryan saves his files Case Else SaveLoc = "C:\Expenses\" 'Where all other users save their files End Select SaveAs = Application.GetSaveAsFilename(SaveLoc & ThisFile & ".xlsm", filefilter:= _ " Excel Macro Free Workbook (*.xlsx), *.xlsx," & _ " Excel Macro Enabled Workbook (*.xlsm), *.xlsm," & _ " Excel 2000-2003 Workbook (*.xls), *.xls," & _ " Excel Binary Workbook (*.xlsb), *.xlsb", _ FilterIndex:=2, Title:="Choose Save Location") If SaveAs = False Then MsgBox User & " cancelled save" Else ActiveWorkbook.SaveAs Filename:=ThisFile End If End Sub -----------Code--------------- "Ron de Bruin" wrote: hi Barb See http://www.rondebruin.nl/saveas.htm See the second macro -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Barb Reinhardt" wrote in message ... I have the following snippet of code filesavename = Application.GetSaveAsFilename( _ InitialFileName:=SuggestedName & ".xls", _ FileFilter:="Excel Files (*.xls), *.xls") How would I need to change this supply a suggestedname for either XLS or XLSM files. When I use this, it's only .XLS I think what I'm wanting is a File Filter that includes xls and xlsm. Can this be done? If so, I guess I need to change the initial file name somehow. Suggestions? Thanks, Barb Reinhardt |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Provide option to specify level of difficulty of the Question | Excel Discussion (Misc queries) | |||
Can't save xlsm as web page | Excel Discussion (Misc queries) | |||
Can't save a .xlsm as a web page | Excel Discussion (Misc queries) | |||
SaveAs XLSM question | Excel Programming | |||
SaveAs works on one PC, not another. "Save" not really an option. What to do? | Excel Programming |