Free Microsoft Excel 2013 Quick Reference

Saving as .xlsx from VBA MACRO

I am attempting to save files through a MACRO as .xlsx files using
getclosefilename function. I have Excell 2007 set to save all files as .xls
files. When I save the files, it places the .xlsx extension, but saves in
..xls format. I cannot open the files as they are, but can after changing the
extension to .xls through Explorer they open just fine.

How do I set Excel 2007to save files as .xlsx through the MACR VBA?


I'm (trying to) write a macro that will request the user to "save document as" in order for him/her to save the document wherever he/she wants but I can't seem to figure out what VB code I need to just have that "save as" box and let the user decide from there.

Ideally I would like the macro to:

Do the rest of what it's supposed to do after the user has saved the document
OR
Stop if the user clicks "Cancel".

Is there an easy way?

Thanks!

How do I save a sheet from the macro workbook to a new file?

My code runs, the last thing I want to do it to move sheet named "Summary" to another work book, save it as XLS in the same directory as the macro, with the name being "Pivot Table" &myStartDate& myEndDate&".xls"

I'm under what to put after...

Sheets("Summary").Select
Sheets("Summary").Move

Thanks!

I believe this is a bug in Excel 2003. I get an error when saving as
CSV with VBA password protection turned on. If I turn off password
protection, the same code runs without error.

To reproduce the problem...

1. Open a new Excel file.
2. Type "Test" in Sheet1 cell A1
3. using Excel's Control Toolbox toolbar, draw a button on the
worksheet
4. right click on the button and select "view code"
5. enter the following line of code in the button click event (be sure
to enter a path that exists on your system -- I used "c:temp" which
did exist on my system):
ThisWorkbook.SaveAs "c:temptest.csv", xlCSVWindows
6. save your Excel file
7. click the button and observe that the VBA code runs without error
8. close the CSV file that you saved in step 7
9. re-open your Excel file that you saved in step 6
10. open the Visual Basic editor and select ToolsVBAProject Properties
from the menu
11. click the Protection tab, check the "Lock project for viewing"
checkbox, enter a password and click OK (this protects your VBA code).
12. close the Excel file
13. re-open the Excel file
14. click on the button you added in step 3 and notice the error.
"Run-time error '1004':
Method 'SaveAs' of object '_Workbook' failed
15. open the Visual Basic editor and attempt to view your code
16. when prompted for your password, enter it
17. click on the button you added in step 3 notice now the code runs
without error.

So it's clear that the same code runs fine when VBA password protection
is turned on or the password has been entered, or produces an error
when VBA password protection is turned off or the password has been
entered.

Any ideas how to correct this issue or work around it?

Thanks!

I believe this is a bug in Excel 2003. I get an error when saving as
CSV with VBA password protection turned on. If I turn off password
protection, the same code runs without error.

To reproduce the problem...

1. Open a new Excel file.
2. Type "Test" in Sheet1 cell A1
3. using Excel's Control Toolbox toolbar, draw a button on the
worksheet
4. right click on the button and select "view code"
5. enter the following line of code in the button click event (be sure
to enter a path that exists on your system -- I used "c:temp" which
did exist on my system):
ThisWorkbook.SaveAs "c:temptest.csv", xlCSVWindows
6. save your Excel file
7. click the button and observe that the VBA code runs without error
8. close the CSV file that you saved in step 7
9. re-open your Excel file that you saved in step 6
10. open the Visual Basic editor and select ToolsVBAProject Properties
from the menu
11. click the Protection tab, check the "Lock project for viewing"
checkbox, enter a password and click OK (this protects your VBA code).
12. close the Excel file
13. re-open the Excel file
14. click on the button you added in step 3 and notice the error.
"Run-time error '1004':
Method 'SaveAs' of object '_Workbook' failed
15. open the Visual Basic editor and attempt to view your code
16. when prompted for your password, enter it
17. click on the button you added in step 3 notice now the code runs
without error.

So it's clear that the same code runs fine when VBA password protection
is turned on or the password has been entered, or produces an error
when VBA password protection is turned off or the password has been
entered.

Any ideas how to correct this issue or work around it?

Thanks!

I'm trying to display the Save As window using VBA. I can display the
window, but when I click on save button, nothing happens. I am assuming it
is possible to do this, and I figure I'm missing something.

In case it makes a difference, I'm using Excel 97.

I am trying to save a file from a macro enabled file into 'xlsx' format and automatically answer 'Yes' to the VB Project dialog box that comes up to warn about the removal of macros.

The object of the exercise is to save only the worksheet to the output file without any of the VB project files.

Saving the file as xlsx is easy enough with the normal VBA saveas routine but is there a way that I can automatically answer the VB Project warning dialog basic so that the dialog box does not appear and the output file save completes seamlessly like an ordinary save.

Thanks,
Jon

I would like to create a macro to save the current worksheet as referenced
from a cell on the worksheet, save in a preset directory, and then print the
worksheet.

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.


	VB:
	
 
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() 
Loop 
 
 '  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

Hi,

I am fairly new to this, but I have created a macro to pick up a txt file and copy and paste it and then save it as todays date as soon as the excel file opens using Task Scheduler on Windows. This has worked fine, but it is copying the Macro over and when i try to open up the file for any particular day the macro is trying to run and pick up from the txt file.

Is there any way I can still create the file on a daily basis, but not copy the Macro to the Save as file?

Any help would be greatly appreciated.

Thanks

Hi
I have spreadsheets with dates in dd/mm/yyyy format.
If I save the sheet using the tollbar "save as" the dates remain in dd/mm/yyyy format.
However if saving via a VBA macro they are converted to m/d/yyyy format with leading zeroes of the dd and mm suppressed e.g. 31/07/2004 saved as 7/31/2004 and 01/08/2004 saved as 8/1/2004.

Macro code
Sub Post()

' Post Macro
Dim MyString
Dim MyFilename

MyString = ActiveSheet.Name
MyFilename = "k:finfeedsCLJVS" & MyString & ".csv"

ActiveSheet.SaveAs FileName:=MyFilename, _
FileFormat:=xlCSV, CreateBackup:=False

On Error Resume Next
End Sub

I get a monthly extract that I can process manually. But I want a Mcro to do teh following:
1. Ask for the Name and Location of the Input (.csv) file
2. put the data in 41 separate sheets by Survey Code (1 thru 44; there are no surveys #3, 13, 33).
3. Save the output file as .xlsx/xlsm by prompting for the name and location of the output file (application.getfilename and application.getsaveasfilename, etc)

I tried doing it by using (and changing) code on page 847 (of John Walkenbach's Excel 2007 Power Programming with VBA) but did not succeed. I will pay usd 10.00 for this. No rush.
I have tried attaching the .csv sample file but the message says it is an invalid file. I have no clue as to why is it an invalid file?

I would like to update the file information such as "ReadOnly", "Author" and "Comments" for a workbook from VBA but can't find how I can do this. The ReadOnly property can be set when "Saving As" but can't see a way of accessing and updating the author/comments information.

Is it possible?

Thanks in anticipation

John

From within a Word macro I'm trying to save an Excel object as a csv file,
but can't seem to find any documentation that explains how to do it. Any
ideas? Code below -- I've tried objExcel.dialogs(xlDialogSaveAs).show but
get an error saying that xlDialogSaveAs is an undefined variable, and when I
try to specify xlCSV as a file format I get the same undefined variable error:

sub SaveAsCSV()

Dim objExcel As Object

Set objExcel = CreateObject("Excel.Application")

objExcel.Workbooks.Add

[snip - doing stuff to workbook]

objExcel.ActiveWorkbook.SaveAs FileName:="c:myfile.csv"
'The above line saves as an .xls even if the suffix is ".csv"

objExcel.Application.Quit

Set objExcel = Nothing

end sub

Originally Posted by smuzoen
Sub saveFileNoMacro()
fullfilename = Application.GetSaveAsFilename("DefaultFilename.xlsx", _
    "Excel files (*.xlsx),*.xlsx", 1, "Save File As")
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
        fullfilename, FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False
ActiveWorkbook.Close
End Sub
Hope this helps So i found SMUZOEN provided this code for a question similar to mine but my spreadsheet will be going out to ~60 people, most of whom will try to push the VBA buttons (which, when saved as XLSX are useless) and get frusterated why they dont work.. Is there a way to get it to save as XLSX and remove the [Button(Form Control)]'s on all the sheets in the workbook? My workbooks contains 3-8 sheets per workbook so it's a bother to go through and delete every button, every time.

I am using VBA for saving one worksheet as .csv file format from a Excel
workbook .One of the columns in that contains values as 00033 and so on.When
my save as statement in vba code executes then i lost the leading zeroes from
that column and finally get only 33 . Is there any way by which i can keep
leading zeroes in that perticuler column.

Hi,

I have an Excel 2010 file containing macros and Users often lose them by saving as .xlsx files. How can I stop this using VBA code?

Many thanks.

Hi all,

I have a macro enabled workbook, and I want it to
prevent the user from saving the file in any way. Canon anyone pls tell me how to disable the save and save as functionality using vba in the Auto_Open subroutine?

Thanks in advance.

Tino XXL

I have created an Excel Add-in using VS2010 using C#. Inside the ThisAddIn class, along with the Startup and Shutdown events, I have added a function, AddMapping - which essentially sets up an XML mapping to a worksheet. I want to trigger this function from inside a VBA macro inside a loaded spreadheet - how do I access this function in the Add-in module - I do not see the Add-In module in the Project Explorer when VBA is up - as per this

I'm very new to Add-ins - I believe this has created a COM add-in - not sure if that has anything to do with it.

Thanks for any help

Hi guys,

Hope u can solve these problems i'm facing.

Description:
The chart shown in the file is a picture using the Camera Tool. The real chart is in the worksheet "Chts". When using the dropdown & radio button, the chart is supposed to change accordingly.

Problem 1:
The picture from the camera tool seem to be blurry. The lines also seem thicker. Any rectifications for this?

Problem 2:
If I save this file as "xlsx", the chart will not change dynamically when the dropdown is changed.

2003 version
http://www.2shared.com/document/Qall..._20120323.html

2007 version
http://www.2shared.com/file/iqF6L3e5..._20120323.html

Dont know whether this is possible or not.
I want to open save as dialog box by Excel macro.

Many thanks in advance.

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.


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

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

Hi, I do apologise in advance for my lack of knowledge of VBA but i've only just registered as a member and started using VBA.

I'm trying to use the code below to paste special my first sheet to remove the formulas, then delete all other worksheets then save as a reference number (which is linked to the worksheet im saving) but I also need it to save in a specific folder. Would anybody be able to help me? As you can see i've already trawled through the forums to get this far.

Thanks in advance, Matt


	VB:
	
 SaveAsCell() 
     
    ActiveWorkbook.Save 
    Cells.Select 
    Selection.Copy 
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ 
    False, Transpose:=False 
    Sheets("Cat Nos").Visible = True 
    Sheets("Supplier Info").Visible = True 
    Sheets("Codes").Visible = True 
    Sheets("Buyers").Visible = True 
    Sheets(Array("Data Sheet", "Sup No Lookup", "Buyer lookup", "Cat Nos", _ 
    "Supplier Info", "Codes", "Buyers")).Select 
    Sheets("Buyers").Activate 
    Application.CutCopyMode = False 
    ActiveWindow.SelectedSheets.Delete 
     
     
    Dim strName As String 
     
    On Error Goto InvalidName 
    strName = Sheet2.Range("F14") 
    ActiveWorkbook.SaveAs strName 
     
    Exit Sub 
    InvalidName: MsgBox "The text: " & strName & _ 
    " is not a valid file name.", vbCritical, "Ozgrid.com" 
End Sub 

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


hi all this is how I'm saving a workbook from a macro

Sub Auto()
Application.OnTime TimeValue("7:00:00"), "Macro2", TimeValue("7:00:02")

Sub Macro2()
Application.DisplayAlerts = False
Excel.ThisWorkbook.SaveAs Filename:="C:Plc" & file_name, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False

this works but because of timing (the added TimeValue("7:00:02") ) sometimes it will save it twice or three times. the problem is after it is saved the first time most cells are deleted, so a basically empty workbook overwrites a good one. I can't change the timing because it sometimes misses it altogether. So I would like to check to see if the file name has been used and if so not save and continue without an error

thank you
Tom

Hi

I have the following code attached to a userform

Code:
Sub closing()
Unload Me

ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub
This code is fine unless the file is opened as "Read Only" (which could happen)

Is there anyway of identifying that the file is read only and stopping the "Save-as" box from appearing?....ie the code just closes the spreadsheet and does not save.

Please help, this is driving me mad.