Free Microsoft Excel 2013 Quick Reference

"Save As" Question

Should I be able to do a "Save As" "Web Page(*.htm;*.html)". The option
is in the drop-down "Save As" list and is hi-lited so it seems it should
work - however it does not wrok. I know it works in reverse (ie) I am
able to import html script into Excel.

Joe Miller
Joe Miller's Profile:
View this thread:

Post your answer or comment

comments powered by Disqus
Hi all,

saw a similar question to mine earlier, but I am too stupid to figure out
the tweaking I need to do myself.

I have created a file name to save as using cell values, however, I want to
allow the user to specify the folder to which to save this file without
changing the file name.

Any help would be much appreciated.


Greetings Again Board:

I am trying to finish up my VBA. The code copies seveal sheets from an existing workbook and pastes them in a new workbook. I want the Save As dialog box to open and prompt the user to save the new workbook. I would like the default in the save as box to be .xls. I can't write the correct code for this. See Code below.

Any thoughts?

NewFileName = Application.GetSaveAsFilename(, filefilter:=("*.xls"))

If NewFileName = False Then 'Handles cancel from
End If 'save as box
Exit Sub

ActiveWorkbook.SaveAs NewFileName & ".xls" 'Saves new workbook with name


Hello -

I'm relatively new to VBA, but am learning quite fast. I'm working on a
user-template and thought I had an 'issue' solved, but apparently I was
wrong. Here's my code:
Private Sub SaveNewVersion_Click()
Ans = MsgBox("Did you update the data links yet?", vbYesNo)
Select Case Ans
Case vbYes
'Remove formulas to make file smaller
Call ValueOutFormulas
'Hide the Admin tab to prevent confusion
ActiveWindow.SelectedSheets.Visible = False
With ActiveWindow
.DisplayWorkbookTabs = False
End With
With Application.FileDialog(msoFileDialogSaveAs)
.InitialFileName = "CommonLogisticsService LevelSL06"
.Title = "Please select a location for the Availability Update"
If Err <> 0 Then MsgBox "File was NOT saved!"
If ThisWorkbook.Saved = True Then ThisWorkbook.Close
If ThisWorkbook.Saved = False Then _
MsgBox "File_Save was cancelled!"
Sheets("Admin").Visible = True
With ActiveWindow
.DisplayWorkbookTabs = True
End With
End With
Case vbNo
MsgBox "You must do that first!"
End Select
End Sub

What's SUPPOSED to happen is that the user presses a button to 'Save As',
all formulas are valued out, the Admin tab is hidden, all tab-names are
hidden, and the file is saved. However, I have TWO problems -- If/when the
user hits 'OK' from Save As dialog box, 1) the file isn't saved at all & 2)
the 'File_save cancelled' msgbox pops up (and tabs are unhidden) every time
(not just on cancellation).

Any ideas on how to change the code to fix these problems? I want to save
the file as a write-protected Excel workbook.



I have a file named TheBasis.xls. I would like allow a user to append to
the name, however, always keep TheBasis as the first part of the name. I
would prefer to disable the simple Save command. Is there a way to do this
in VB? I was thinking that there may be some way to trap the attempt to Save
or Save As, and then go to VB, however, I don't know how.

Thanks in advance for your help!


Hello all,

I have a couple of questions regarding macros with which I hope someone here can kindly assist. I'm modifying a master workbook for my organization that will be distributed to about 20 different users, mostly senior-citizens with minimal computer expertise who do volunteer work.

I'm doing this in Excel 2007 but most of our guys and gals are working with Excel 2003 or earlier. I have a recorded working macro to do some simple maintenance tasks for the workbook but what I need is the code necessary to do the following simple steps before the actual maintenance tasks are started.

1) Save the current document with it's current name (easy enough, I suppose).
2) Save As, then give the user the option to select the path and filename. Typically the worksheets will be named like this: "Letter-A February 2011," I would like for the user to be able to enter any name he/she wants, but if possible, to suggest the next month/year as the default.
3) Perform the actual save-as (so far this has eluded looks like it saves the file but it doesn't)
4) Close the old month, open the newly-created month then run the maintenance routine and pop up a dialog saying the new month's sheet has been created, and it can now be safely closed.

Two "would be nice to have" features are:
a) Have this called by a button that can be placed on one worksheet but perform the maintenance on another
b) Present an information box with text that will ask the user to confirm that he/she is finished with the previous month's report and acknowledging that if he/she proceeds in running the macro the changes made will be permanent. Basically a "do you really want to do this" last-chance box.

The second big point is macro security. Can I distribute this so that barely-computer-literate folks don't have to create their own self-signed certificates, install them and sign the sheet with them? Once they see warning boxes pop up my phone lights up like a Christmas tree. I'd like for them to be able to just use this.

Any assistance would be greatly appreciated, with sincere thanks in advance.

- Peter

Hello all,

I have 30 .DBF Files to open, edit, save and then close. I have a macro that opens all 30 of the files, edit and attemps to close, a message appears and say that the.DBF cannot be saved in the current format. To save your changes, click OK, then save it as the latest format. The macro takes care of the first dialoug boxes.

Then it goes to the "SAVE AS" box and there it wants a manual save the file. The file name is ok, and it wants to save it as a *.xlsx, and that all great. My question is - is there a way to automate with a macro and to just let it say yes to save this action with having to click on the Save button every time - which would be 30 times.

Please let me know, Thanks and I appreciate you all help.

Dim itm As Variant 
Dim strFileNames  As String 
sPath = "C:DataLoging" 
 '    Retrieve the current xl files in directory
sFile = Dir("C:DataLoging" & "*.DBF") 
Do While sFile  "" 
    strFileNames = strFileNames & "," & sFile 
    sFile = Dir() 
 '  Open each file found
For Each itm In Split(strFileNames, ",") 
    If itm  "" Then 
         ' This will cause the opened workbook to be come the active workbook
        Set Wb = Workbooks.Open(sPath & itm) 
        Application.Run ("Moving_N_Columns") 
    End If 
Next itm 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This code does everything accept closes the SAVE AS dialoug box. It's not a really big deal if I have to manually close each file, but I figure it would sure be cool if this part could be automatically done.

Thanks again

Below is a code to save historical price data in separate files. I.E. you type all the symbols you want to download in column A and each one's entire historical data will be saved in its own file titled with its respective symbol.

Question: I need these files in CSV format (currently xls); how do I edit this code to have the files save as csv files.

'If I change "xlNormal" to "lCSV" It asks me do I want to save every time, is there a way around this... so It just saves without the dialog box popping up?

Thanks in advance for the help!

Sub Yahoo() 
    Dim cellvalue1 As String 
    Dim myfilename As String 
    Do While IsEmpty(Range("A1")) = False 
        cellvalue1 = Range("A1") 
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _ 
        "" & cellvalue1 & "&ignore=.csv", TextToDisplay _ 
        :="" & cellvalue1 & "&ignore=.csv" 
        Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True 
        ActiveWindow.Visible = False 
        Windows("table.csv").Visible = True 
        myfilename = Range("M1") 
         'If I change "xlNormal" to ":xlCSV" It asks me do I want to save every time, is there a way around this... so It
just saves without the dialog box popping up?
        ActiveWorkbook.SaveAs myfilename, FileFormat:= _ 
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ 
        , CreateBackup:=False 
        Selection.Delete Shift:=xlUp 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

First off let me thank EVERYONE who actually listens to peoples problems and helps them out. Never have I seen so many people willing to help other without even knowing them. That being said, I have a stupid question

I just want a button to perform the "save as" function for a workbook. I tried the .SaveAs feature but it did not bring up the dialog box where you can type in the name of of the file? Any help would be greatly appreciated as always.

I am trying to set up my workbook that I created so that when information is entered into the workbook and someone goes to save it that they are forced to "Save As" so that the main workbook is untouched. Basically the main workbook is going to be my template. This is just a way that would be helpful.

My ultimate goal is to have the workbook set up so that when someone hits the save button the workbook is saved with the contents of B2 then a space then the contents of J/K2. The J2/K2 cells are merged and centered and also consist of drop down boxes with locations.
The contents of B2 is the date in 01-Jan-1900 format, this is going to be the date the workbook was opened. The contents of J2/K2 is a specific location such as Store 12 in Montana.
So ultimately it would automatically be saved as: 01-Jan-1900 Store 12 in Montana.

If the answer to my question has to be coded in the sheet under view code I already have a code in place for a time stamp. I would also like to know how to add another code without messing up the one I already have.
The current code is located under the worksheet menu and is as follows:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Intersect(Target, Range("$a$7:$a$16")) Is Nothing Then Exit Sub
Target = Now
Cancel = True
End Sub

I appreciate all help in advance. Thanks.

Hello All,

I am trying to hide/grey SAVE option from the menu bar and reset menu back to normal after Save AS has been used.

I was able to do disable SAVE using the following code.


If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

the SAVE icon on the Standard toolbar still exist. Is there any way I can disable it and also prevent CTRL. S being used.

Second part of the question.
Once I use Save as I would like menu to be reset back to normal.

This is my first post and I hope I have followed the rules. Kindly excuse, will be more carefull next time.

Any help, suggestions.

I've searched this forum and the MSDN knowledge base and come up empty handed.

My problem is that I was e-mailed a csv file and it was opened up in Excel from the e-mail client. This was done to make sure the data 'looked' ok. When the data was "saved as" to another location there were '>' characters added into the data. I ran a diff on the excel "saved as" file and the filed saved directly from my mail client to disk and it came up with 50 lines where the '>' character was the only difference.

My question is why did excel add the greater than signs into the data?

I have an excel macro that

1. Prints sheets to .PS
2. Converts the PS to PDF
3. Replaces the pages in a PDF template with the pages from the newly created PDF

The problem is with the last step. The PDF template seems to be experiencing disk size bloat. Every time the pages are replaced, the file gets larger (in terms of disk space). When I use PDF save as (outside of the macro) the file shrinks in size considerable.

My question is, what is the code to save as instead of a plain save? My existing code is below.

PDFTemplate.Open "C:ReportTemplate.pdf" 
PDFUpdate.Open "C:Update.pdf" 
myNew = PDFTemplate.ReplacePages(0, PDFUpdate, 0, CounterEur, False) 
PDFTemplate.Save PDSaveFull + PDSaveCollectGarbage + PDSaveLinearized, _ 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I have ms excel 2000 first of all.

I have been doing some time series analysis called lagging in spss where a column of data is lagged (or put forward one day) in relationship to the original column version of it. I need to save this lagged-data file as a .dat (tab delineated) file; but all excel has for its "save as" options are text (tab delineated) file saving options.

Question: Is there an "add in" for my version of excel 2000 that will enable it to also save my data file in .dat (tab-delineated) file format? When I tried it with the text (tab delineated) "save as", it unlags my data and makes it useless since it's not completely compatible with the type of spreadsheet I am trying to save.

My spss is able to save on part of it as a .dat (tab del.); but since it's only a student version it won't allow me to have more than 50 variables--hence why I am using excel for this particular project. Thanks in advance.


This is very weird... please help.

Whenever I hit save, the charts in the "PM Recap" tab behave quite odd. There are 2 charts that change data depending on what is selected in the combo box. The first chart resets all formatting so that the scale on the value axis is too large to show the miniscule (sometimes percentage values), while the 2nd chart changes to some unknown chart. But when I change what's in the drop down box, it returns to what it's supposed to be. Whenever I open the files from when I "saved as", they are working just fine, too. In one other instance, the radio buttons from the Tropicana sheet disappeared.
Attached is the pared down file (the original is too large)... the second chart is supposed to display only the "PM Variance" column on the data sheet (one series), whose categories are the regions.

Any ideas how to troubleshoot this? Could I be bugged, maybe?

Below is the code:

Private Sub cboPMRecap_change() 
    Dim PMQuestionNum As Integer 
    Dim LastCol As Integer 
    Dim VarianceCol As Integer 
    Dim rIndex As Integer 
    Dim rStartIndex As Integer 
    Dim StartCell As Range, EndCell As Range 
    Dim VarStart As Range, VarEnd As Range, VarCatStart As Range, VarCatEnd As Range 
    Application.ScreenUpdating = False 
     '    On Error Resume Next
    PMQuestionNum = cboPMRecap.ListIndex + 1 
     'Set the start and end rows from the dataset depending on the question selected
    rIndex = PMQuestionNum * 6 
    rStartIndex = rIndex - 5 
     'Set the cells for source data for the first chart
    Set StartCell = Sheets("PMRecap").Cells(rStartIndex, 2) 
    Set EndCell = Sheets("PMRecap").Cells(rIndex, 4) 
     'Set the cells for source data for the second chart
    Set VarStart = Sheets("PMRecap").Cells(rStartIndex, 5) 
    Set VarEnd = Sheets("PMRecap").Cells(rIndex, 5) 
     'Set the category names for the Variance chart _
    (needed because the data cells are Not adjacent To the category names) 
    Set VarCatStart = Sheets("PMRecap").Cells(rStartIndex + 1, 2) 
    Set VarCatEnd = Sheets("PMRecap").Cells(rIndex, 2) 
     'Update the first chart
    With Sheets("PM Recap").ChartObjects(1).Chart 
        .SetSourceData Source:=Worksheets("PMRecap").Range(StartCell, EndCell), PlotBy:=xlRows 
        .Axes(xlValue).MaximumScaleIsAuto = True 
        .Axes(xlValue).MinimumScaleIsAuto = True 
    End With 
     'Update the variance chart
    With Sheets("PM Recap").ChartObjects(2).Chart 
        .SetSourceData Source:=Worksheets("PmRecap").Range(VarStart, VarEnd), PlotBy:=xlColumns 
        .Axes(xlCategory).CategoryNames = Worksheets("PMRecap").Range(VarCatStart, VarCatEnd) 
    End With 
     '    ActiveChart.Deselect
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Hi all!

This is, I think, the second time I've been to the Word section. (and it's not because I'm better at it than Excel!)

I Have a project where I'll be doing a lot of e-mailing of letters (as attachments) that have been personalised with a mail merge.

Up until now, I'd merge a specific record, say for Harry, then go to "main document setup" in the Mail Merge toolbar, change it to Normal Word Document, and Save it As whatever, say Harry.

I'd then do it for the next record, and the next, each time having to open the original Merge Document.

This is my question:

What can I do to use the value of one of the Merge fields (this value will change for each record, eg. Harry, Inge, etc) as a "Save As" name and run some sort of command / macro that will then loop through ALL of the records that need to be merged and save each of those as a Normal Word Document with its own name and in a specific folder while retaining the original merge document?

Hope there's help out there!

Best possible wishes

Harry Fröhlich
In a slightly warming Bethlehem, South Africa

Let me start with I did search for an answer before posting but only info on Savings from 07 to 97-03 format.

I use 2007, but all files are saved as 97-2003 files. So I have this file which is 808KB, saved in 97-03 format. It contains a Macro which removes a tab and then saves as an Unpassword Protect version, in 97-03 format, to an alternate location within the current file structure. It works smashingly, but I am left with a file that is 1,410KB; which is a 75% increase. Any ideas as to why this is. Or does the first statement cover my question? Thank you.



This is the first time I am posting a question to this forum.

I've created a little billing application for the 2 attorney's I work for. I saved the application as a template and put a copy in each of their billing directories. When they open the template to set up billing for a new client, they are asked for the Client's name and the Case Name. I then populate various fields and then prompt them to save the file with the following code:


When the Save As dialog opens, I want the default directory in which to save the new billing file to be the directory they launched the template from. If this is possible, how can I do this?

Thank you,

Hi there,

I have another question surrounding VB coding being used to initiate Dialog Boxes. I have managed to activate the "Save As..." dialog box however I would find it handy if I could populate the filename box with a pre-determined file name such as "Application (PLEASE ENTER YOUR NAME).xls"

Is this possible. I would normally have the program determining everything here but the user has to be able to define the save location manually.

Many thanks,


Hello all,
I have a macro that saves the contents of the a selected cell in column "A" as an .html file named as the contents of the corresponding cell in column "B".
In other words, if the contents of "A1" is "bla bla bla" and the contents of B1 is "abc", then the macro would creat a file called "abc.html" that has "bla bla bla" in it.
My problem is that I have several hundred cells. Since I have the names of the html files named according to what is in column "B", I don't want to have to hit "enter" or "save" to confirm the file to be saved. I just want the file to be saved automaticly with out having to hit enter in the "save as" window.
Thank you,

BTW: my macro looks like this:

Sub DataDump()

'get the file name to write to
Dim FName As Variant 'must be variant type for potential boolean result
Dim defpath As String
defpath = Application.DefaultFilePath & "" & Range("B1")
FName = Application.GetSaveAsFilename(defpath, _
"Web Page (*.htm;*.html),*.html,Text File (*.txt),*.txt,ASCII File (*.asc),*.asc", _
1, "Export Formulas to HTML File")

If FName = False Then
MsgBox "You didn't enter a file name."
Exit Sub
End If

'open the file for writing
Dim fs, f
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.CreateTextFile(FName, True)
Dim eol As String
eol = Chr(13) & Chr(10)

'write the data from the range
Dim s As String
Dim row As Integer
Dim col As Integer
row = 0
col = 0
Dim cell As Range
For Each cell In Selection.Cells
If cell.HasFormula Then
s = cell.Formula
If Left(s, 1) = "=" Then
f.write Replace(cell.Address, "$", "") & ": "
If cell.HasArray Then
f.write "{" & s & "}" & eol
f.write s & eol
End If
End If
s = cell.Value
If Len(s) Then
f.write s & eol
End If
End If

'close the file and release objects
Set fs = Nothing

End Sub

Hi... My first 2008 question, simple but not in my arsenal of solutions yet...

I have an Expenses Report Form in an Excel sheet. It is a blank template, accessible via Intranet. Upon filling the data, the user who wishes to save it MUST change the filename and location to avoid overwritting the blank template. For that purpose, I have created a macro that forces the user to use the Save As option, with a warning message.

When the Save As screen shows up, the filename field is defaulted to the original template filename, highlighted. I would like that field to be blank (as if the user had pressed the escape key to change the filename as requested). It is an additional safeguard against mechanic pressing of the Enter key...

What is the syntax to attain that effect?

Running Excel 2003/XP Professional

Thanks a bunch!

I have made a form in excel named Form001.xls. I want this to be saved as the value in cell D10 but I don't want to see the save as-box pop up. I am using Excel 97 on Windows 98 and

Application.DisplayAlerts = False
filesavename = Application.GetSaveAsFilename(InitialFileName:=fname, FileFilter:="Excel Workbook(*.xls), *.xls")
Application.Goto Cells(1, 1), True
ActiveWorkbook.SaveAs filesavename
Application.DisplayAlerts = True
doesn't seem to work. Any help would be appreciated.

I have a workbook with a data entry sheet and product sheets. All data is entered in dataentry and then distributed to correct worksheet. My question is this: After data entry I want the active worksheet to be saved to a name I have provided in code(this works) but then I have a macro that I need to run to change info in the original worksheet (get cumalitive totals and wipe out data entry sheet). Where do I put macros so it runs properly?
I have a button that runs the 'save as' code but then the original workbook is not active and my 'savetotals' and 'clear dataentry' does not work.(all on same button)

Any help will be appreciated! Great site, by the way.

Rae Winn

Hi peeps!!
Really need your help on this one!!

I am trying to create an event when a workbook is open, it will save it a backup in a particular folder with a date and time stamp

So far I have managed to create a string with the value that I want the workbook to be saved as which resides in cell b2 on a sheet named Main. Question is
1./. How do I get excel to save it as that filename and
2./. How do I get it to save on opening up that workbook

Thanks peeps !!

Hi Everyone,

I have a quick question. I am working at two different computers. I have a workbook that I shared and when I did a file-save as it will allow me to call it another name and still remain shared. On another computer I created a workbook as shared, but when I did a file-save as it unshared it. Does anyone have any suggestions.

Much appreciate it.

No luck finding an answer? You could always try Google.