Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



Save Workbook With Multiple Sheets As Csv File And Xlsx File

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hi,

I have a xlsm workbook with 3 worksheets. I've recorded a macro that formats the data in these sheets. I want the file to then be saved as a csv file (which IT picks up to load into an application) and an xlsx file for my records.

How can I save the data in the 3 worksheets as seperate csv files?
How do I save the xlsx file with 3 worksheets in it?
All the column headers are the same. The number of rows having data differs in each sheets.

Thanks,

View Answers     

Similar Excel Tutorials

Export an Excel File to a CSV File
How to export an Excel file to a CSV file (comma separated values file).  This allows you to turn any Excel spread ...
Change the Default Number of Worksheets Created in a New Excel Workbook
Learn how to change the number of worksheets that are in new Excel workbooks. Excel workbooks always have 3 worksh ...
Make Your Macros Available in All Workbooks in Excel
In Excel, you can make it so all of your macros can be easily accessed by any Excel workbook. To do this we need t ...
Make Users Enable Macros in Order to View a Workbook in Excel
Tutorial showing you how to make a user enable macros in a workbook in order to view the workbook. This is very im ...

Helpful Excel Macros

Excel Macro to Save a Specific Worksheet as a New File
- This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a
Save the Current Worksheet as a New Excel Workbook File
- This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a
Save the Current Worksheet as a New File in the Current Folder
- This Excel Macro saves the currently visible worksheet into the SAME folder as the current file. It is different from
Name Worksheets Based on Cell Contents
- This macro allows you to have your worksheets named by whatever is in a particular cell within a worksheet. This means
Prevent Saving a Workbook under a Different File Name
- This Excel macro prevents the user from saving an excel file or workbook under a different file name. The file can only

Similar Topics







Thanks in advance for any help

i have a attached a file that will look for a csv file and consolidate all identical text from column A into different worksheets , then it saves to xlsx file.

at present i have to save my xlsx files to csv to run the macro (but unfortunatel all formulas dissapear in the csv format).

i want to be able to search for xlsx files, then let the macro run and save also as xlsx file..

is it possible someone could help me please

Paul

I have to download files from a database and save them in .xls format (the only format permitted to export from the database)

Anyone have a macro to:

Given an open excel wb saved to my hard drive (or network drive) in non .xlsx format. (most likely .xls)

Switch the file to .xlsx format without loosing the open file. (re-open ok).

Caveats:
-Don't want two files
-Dnn't want to have the file name changed (except for the extension).
-Don't want to remain in compatibility mode.
-Don't want to wait a long time.
-Don't want to have possibility of file corruption.

I just want to take my non .xlsx file and then, turn into a .xlsx file. (if I save the file as .xlsx I am still in compatibility mode until I re-open the file. I also will have two files---very annoying).

Thanks in advance


I have an excel file that is distributed within my organization. That file contains certain worksheets that the end-users do not need to see or make changes to, so in the VBA project, I change those files to "VeryHidden". Then I protect the workbook's VBA code by going to Tools->VBAProject- Project Properties-> Protection tab and adding a password. This worked great as it allowed all the other sheets to function as intended, but didn't allow the users to access the sheets that were hidden.

However, we recently upgraded to Excel 2007, and the password protection is stripped whenever the file is saved with the extension .xlsx. I can save it as a "macro-enabled" file with the extension .xlsm, but all that needs to be done to remove the password is to save it with the .xlsx extension again. Is there something that can be done in Excel 2007 to protect VBA code, or am I doing something completely wrong?

This is my first post here, so please let me know if I've done something wrong!




Hi all,

I have a current workbookCopy of ASP Test RC File.xlsx with 3 worksheets.

Worksheet RC is a summary of all companies rates (Columns G:J). I've shortened this to keep the file small but in reality there are actually another 140 columns after Column G.

What I am looking to do is create an individual file for each Company i.e. each column from G onwards yet keep Columns A:F as these will be the same for all.

Furthermore, I would like each new file contain the Instructions and Cover Page tabs (Sheet 1 & 2).

Each new file should be renamed according to the name listed in G5, G6, G7, etc...

Is there a way to do this with VBA to save me manually having to create 140+ plus files and copying and pasting sheets 1 & 2 into each file?

Any assistance would be greatly appreciated.

Kind regards,

Hi all, I've actually got a proper coding question, for a change.

I'm using a data handling application which exports data to Excel, producing an Excel 2003 worksheet. Unfortunately this means that it gives an error if you try to export more than 256 columns of data (which I do every so often).

I've found a way to make the other appliction split the columns over multiple Excel sheets and I have a macro which merges all of the individual sheets back onto the front sheet.

However, before I can run my macro I need to:

1. Save the exported workbook as an Excel 2007 xlsx file.
2. Close the workbook
3. Re-open the xlsx file.

If I don't do this then my macro fails when the front sheet column count reaches 256.

So, what I'm wondering is:

1. Is there a programmatic way to tell Excel that it's no longer dealing with a 2003 format file and it can safely add columns above 256 or

2. Is there a way to tell if the target workbook is a 2003 file which has just been saved as a 2007 file (in which case my macro can close and re-open it) or if it is a genuine 2007 xlsx file?

Any advice greatly appreciated.

Andrew


I am trying to merge 30 million rows of records from about 10 different xlsx files into any format that could be imported into ACL (ie: csv delimited text file, tab delimited text file etc). All of these xlsx files already have a consistent set of headers and data type in each of the fields, but a workbook may contain a number of different worksheets.

Is there an easy way to complete this merging exercise with either with a macro or a another program?




Hi all,

I have the following situation:
I've built a file for other users without macros (or other VBA) and saved as .xlsx
Now I want to protect it from users writing VBA code (to get to the protected cells of the file).
Of course they would have to save the file as .xlsm to be able to use the VBA, but I'd like to prevent them from even getting to the VBA project viewer.

I know I can do this in a .xlsm file with a password, but it doesn't seem to work for my .xlsx file. When I set a password, close the file and open it again, you can get to the (empty) code without Excel asking for the password. Is this because it is not a .xlsm file?

I have an .xlsm file that contains this (part) code below to save as another filename I have attempted to save as another .xlsm file but new file is always corrupted when created.

When I try to save new file as an .xlsx file I am prompted to check compatability - the script stops at "ActiveWorkbook.Save"
- how do I avoid this check appearing?

Sub CreateSummary()
'
' Sheets("Header").Select
Range("A1").Select
ActiveWorkbook.Save
ActiveWorkbook.SaveAs Filename:= _
"C:\Sales Summary.xlsx" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
.etc
.
.



Hi All,

I have code that I am using to copy some sheets from an existing workbook into its own seperate workbook. I have a few problems with it that I was hoping someone could help me clear up. (Forgive me I'm still new to this!) Any help is appreciated. My Problems are as follows:

1. Once the Save As window pops up, the 'Cancel' button doesn't work. The user is forced to save the file.

2. Once the sheets are copied over to the new workbook, the new workbook remains open. I would like it to be saved, but not opened.

3. After the new workbook is created with the copied worksheets, when the user proceeds to close the workbook, it asks the user to save again. I'm guessing the sheets copied to the new file after it was created? I would like the user to not have to do this.


Any help provided is greatly appreciated. Thank you


Private Sub CommandButton2_Click()
Dim wb As Workbook
Dim ws As Worksheet
Dim fName
'Set Default File Path to Save to
ChDir "C:\Documents and Settings\My Documents"
With Application
.DisplayAlerts = False
.ScreenUpdating = False

Set wb = Workbooks.Add

ThisWorkbook.Worksheets(Array("Summary", "Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8")).Copy befo =Worksheets("Sheet1")
For Each ws In ActiveWorkbook.Worksheets
If ws.Range("A1").Value = "" Then
Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = True
End If
Do
fName = Application.GetSaveAsFilename _
(ThisFile, filefilter:="Excel Files, *.xlsx")
Loop Until fName False
ActiveWorkbook.SaveAs Filename:=fName
Next ws
.DisplayAlerts = True
.ScreenUpdating = True
End With
Set wb = Nothing
Set ws = Nothing
End Sub




I get a lot of .xls files from generated reports. I have to go through the Save As dialog every time to save them as .xlsx.

The path is not always the same, nor is the file name. So what I need is a general macro for my background macro workbook that parses the file path, file name, then saves the file as .xlsx with the same name and path. I'm a little stumped on this one! Thanks!



I have received an .csv file and saved this file as an .xlsx file because otherwise my Java program couldn't work with it.
I don't have enough memory to work with the .xlsx file through Java (because the file is 19 MB).
I would like to split the .xlsx file into several .xlsx without having to copy paste all of it.

My Excel file looks like this:

clip_image002.jpg

clip_image002.jpg

splitsen.png

I would like to have a seperate file for each TRAIN_NUM. Is this possible and can somebody tell me how? (I have no experience with macro's.)
Each TRAIN_NUM can have a different amount of rows and the TRAIN_NUMs are not always consecutive.

after running a macro i have a dynamic amount of worksheets within the .xls workbook.

my goal is to save each sheet as its own file with only that sheets within the workbook.

for example if i have 3 tabs (alpha, beta, zeta) on the worksheet....i want to run this code and the end result would be a file saved with only the tabs alpha, a second file save with only one tab called beta and a third file with only zeta.

does that make sense????????......i already have the coding for the fnc of save the file (along with it's cooresponding name) and to the correct location.

thanks
tuk


I have the below that is taking the numeric response report workseet, exporting it and prompting to save as _ .xls. Having the user fill in the name

How can I add in to reference a different sheet and cell to automatlly fill in the save as box?

Also, does anyone know why if I click cancel it saves the file as false.xlsx?

Sub savefile()
'
Dim myFile
Sheets("Numeric Response Report").Select
Sheets("Numeric Response Report").Copy
'look into why when canceling save as it automatically save it as False.xlsx

myFile = Application.GetSaveAsFilename( _
fileFilter:="Microsoft Excel File (*.xls), *.xls")
ActiveWorkbook.SaveAs myFile
MsgBox "File saved as : " & myFile

End Sub


I'm running Excel 2007 on a WinXP (SP2) machine (dual core, 4GB RAM, 8GB free HDD space).

I keep getting a strange error message frequently when I try to save large files (> 2MB) with lots of sheets. Excel seems to save okay (the save progress bar at the bottom finishes), but then a dialog box pops up saying:
Quote:

Your change could not be saved to filename.xls because of a sharing violation. Trying saving to a different file.

Once I hit Ok on that dialog box, it immediately gives another dialog:
Quote:

The file you are trying to open, '56C2F10', is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now? Y/N

I hit Yes, and it then opens another version of the same file and gives me the Save As... dialog box. Once I enter a different name, it saves that file and closes it, leaving me with the original file that I was trying to save (but could not). I then close that original file and open the new/renamed file and it contains all my changes.

As I mentioned, this happens usually with large files (>2MB), usually ones which have many sheets (>10). It has happened with .xls, .xlsx, and .xlsm formats. I am saving only to my local hard drive, and the file is not shared with/on any other user/server/machine.

It is very annoying, since I have to interrupt my work to close the "corrupted" file and open the newly renamed one, not to mention that I end up with multiple copies of the files.

Any insight as to what's causing this problem, and how to fix it?


Hi I have this macro it save to specific location but if the file name exist then macro fails or wants to overwrite existing file I will like to make this macro to add a number
So I t will look like this
DISCONNECTED # 11-09-09.xlsx
DISCONNECTED # 11-09-09 2 .xlsx
DISCONNECTED # 11-09-09 3 .xlsx
DISCONNECTED # 11-09-09 4 .xlsx
DISCONNECTED # 11-09-09 5 .xlsx


Code:

sub save()       
ActiveWorkbook.SaveAs FileName:= _
"C:\POSTING\  DISCONNECTED   # " & Format(Date, "MM-DD-YY") & ".XLSX", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
end sub




Beforehand thank you for any help that you can provide




Hi friends,

I daily get a .csv file and i have to open it and daily need to save it as .xlsx so i just want to know is there any macro to save the .csv file with current date .xlsx and to a specific path, so that i just have to open it and click one button and the .csv file will be saved as current date.xlsx please help

Hi All,

Can anyone help:- I want to be able to save an file created on my computer for use with a device using Windows mobile Ver 6. The files in Windows have the ext .xlsx but I can't find this in the drop down in the save as file type list.

I have files on my mobile device saved as xls, but whenever I change and save them I have to save a copy i.e. Save As.. Filename(1) whereas the xlsx files will just accept the changes and save as they are.

Very grateful for some help with this. Thanks

Alley.


Hello. I created a design table in excel 2007. I saved it as a .xls file. I want to now take that file and make it xlsx so I can have more than 65k rows. I tried opening the .xls file and did a save as .xlsx but the row limit is still 65k. How do I get it back to handle more than 65,000 rows again? Please advise. Thanks.



Hi Guys,

I have the following problem. I need a piece of code that would do the following.

I have many excel files that contain links to other spreadsheets and formulas.
I want to be able to make a macro that would do the following:

copy all the sheets from the active "workbook with links" xlsx create a new filek xls replacing name of workbook "workbook with links" xlsx into workbook "workbook" xls. paste as values + formats all the content of "workbook with links" xlsx into "workbook" xls. as I am going to repeat this procedure every time the data changes in "workbook with links" xlsx the macro has to be able to each time overwrite "workbook" xls file.
Thanks for help


I have an .xls file (compatibility mode) containing about 40 worksheets. My goal is to save each worksheet as a separate workbook. To do this, I turned to David Mcritchie's macro, found he http://www.mvps.org/dmcritchie/excel/saveas.htm

I run this macro in Excel 2007. It works, but I get the compatibility prompt before each save. As you can imagine, this takes forever and basically defeats the time-saving purpose of using the macro to begin with.

In order to get around this, I have experimented with various combinations of which format (xls, xlsx, xlsm) to use for the original file, and also to designate for the ending of the new files, as shown within the VBA code. This includes saving the original file as .xlsm, closing it, opening it up again, and running the macro then, with the macro either in its original form, or modified to save a different type of file for the new workbooks.

But no matter what I do, the macro only gets as far as creating the very first workbook, and then giving an error that I'm trying to paste into a workbook that doesn't have as many rows as the original workbook. Of course, the new workbook is being created by the macro.

I would very much appreciate suggestions, and would be happy to provide more information, or try different solutions. Thanks!




Hi,

I am new to Macros and was trying to generate one.
Tasks: 1) Source data is in Temp_List.xlsm. I need to copy the data in cells A2, B2, C2 of Temp_List.xlsm and paste it in I24:Q25, D24:F25 and I22:Q23 respectively of Pointer-A-DWG1.xlsx file.
2) Then save this above file with filename given in cell D2 of Temp_List.xlsm file at particular location (For example - C:\Documents and Settings\vinbill5\Desktop\Pointer_Dwgs\Delivery_Pointer_Dwgs)
3) Continue similarly to copy data in cells A3, B3, C3 of Temp_List.xlsm and paste it in I24:Q25, D24:F25 and I22:Q23 respectively of previously saved file or Pointer-A-DWG1.xlsx original file.
4) Save this second file with filename given in cell D3 of Temp_List.xlsm file at particular location (For example - C:\Documents and Settings\vinbill5\Desktop\Pointer_Dwgs\Delivery_Pointer_Dwgs)
5) Continue creating new excel workbooks till there is data in rows of column A:A. In the above sample file of Temp_List.xlsm, there are 4 rows having data and hence 4 excel files would be created with proper names.
Image.JPG
I tried creating macro but, each time, it creates file with the same values in 2nd row. Evenif I change the values in 2nd row, it replaces them with the values that I had provided while creating the macro.
Please help me with the above tasks, I need to create almost 2000 files every day and wish to automate this task.

Thanks,
vinbill5

Hi. I'm using Excel 2007. I've a number of files, each containing 3 sheets called Jan, Feb, and Mar. I need a Macro that can save just the Mar sheet from each file as html with the name either being the name of the current xlsx file - or a particular cell value - either way would be fine. Whenever I try it's getting the names wrong or including all 3 sheets in the html file.

Also if I've all the files in one folder e.g. c:\months, is it possible to loop the macro so it runs on all of them at once or would I have to open them all individually. There's about 25 files at the moment but I could have a lot more in the future.

thanks
Declan




Hi all,

I was wondering if anyone knew the solution to this problem.

I have an Excel Macro-Enabled Template (.xltm) for a file that I use to create quotes. I then save the Template as a Macro-Enabled Workbook (.xlsm) to send to person B as file name "Quote-1234.xlsm".

I have a macro in this file to create a final version, intended as a regular Excel Workbook (.xlsx) so as to remove macros. I'd like to have a Save As Dialogue come up, because person B's directory is different to mine (C:Users/me/... as compared to Users/b/...)

I would like to have the same file name "Quote-1234" and extend onto the current name & "FLAT FILE" & dd-mm-yy.xlsx (being todays current date).

The following code works great, except it doens't change the File Name and it saves it as a Macro-Enabled (.xlsm)!!!



Please Login or Register  to view this content.


Any help? Thanks in advance!

I have a macro that I am using to save the file from a cell on a worksheet, but the save has to be in "xlsx" format:

Sub SaveAs()


Dim FName As String
Dim FPath As String


FName = ActiveWorkbook.Sheets("DATA INPUT").Range("C2")
FPath = "C:\Documents and Settings\User\My Documents"

ActiveWorkbook.SaveAs Filename:=FPath & "\" & FName & ".xlsx"

End Sub

It will allow it to be saved as "xls" and "xlsm" but not "xlsx.

Thanks in advanced


I have written an application in Java that used POI to import Excel files, in either Office 2003 format or Office 2007 xlsx (OOXML) format. The issue I have run into is that some files that have the 'xlsx' extension appear to be in 'xlsb' format, this despite the user having specified the file to be saved in 'xlsx' format. I would like to understand why this is happening.

Can anyone tell me whether there are any reasons that would cause Excel 2007 to save the file in the binary format, rather than the OOXML format? I don't if it is any indication, but when trying to open the file in Office 2008 (Mac) I get told that the workbook contains unsupported "OLE DB query tables" content. I am reaching the conclusion that this is the binary format, because the file data does not start with "PK" characters and saving another file in xlsb shows the same general pattern.