Email:      Pass:    Pass?
Close Window   
TE
Free Excel Tips in Your Email!
Join Over 20,000 Happy Subscribers!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:


Advertisements


Free Excel Forum

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 Video Tutorials

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!


I have a report that dumps out an .htm file which I open in excel and run a macro on and then have it prompt to do a save as, with the following chunk of code that I've modified from other posts on this forum:

Dim file_name As Variant

strFileDirectory = "C:\Users\Michael\Documents\"
ChDir (strFileDirectory)
file_name = Application.GetSaveAsFilename(FileFilter:="Microsoft Excel workbook (*.xlsx), *.xlsx")
If file_name False Then
ActiveWorkbook.SaveAs Filename:=file_name
End If

It is prompting me correctly enough it would seem to save the file with the correct .xlsx extension, but not only is it saving the .xlsx file but it is creating a folder (of the same name) housing additional files (htm, css, xml). I just want a regular 'ol xlsx file. Any ideas how I can improve the code to get that?

Thanks in advance.


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?


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 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


I have created one spreadsheet in which i get imp data from other sheets (in the same spreadsheet) automatically by selecting dropdown list, using IF formula. and its working very nicely. Now if i want to pull imp data from worksheet 01.xlsx, 02.xlsx, 03.xlsx from diff folder (drive D) to worksheet A.xlsx (saved on my desktop). by entering only name of the file (01 or 02 or 03)
if i enter 01 in the particular cell in file A.xlsx, i would have to get table from cell A1:C10 of file 01.xlsx
=[01.xlsx]Sheet1!A1:C10
if i enter 02 then the data from particular file should be pulled out.
=[02.xlsx]Sheet1!A1:C10

so the formula remain same, only file name should be change. is it possible?

which formula should i use for it. please help me in this. it will be really very helpful to everyone.
Thank you!!


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


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.



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


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.


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


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'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 was wondering whether it's possible to connect one file to just a range of cells in another.

Essentially I have 2 files: A.xlsx and file B.xlsx.

A.xslx contains 1 sheet with some calculations and 3 tables. One of the three tables is a costs table that I need to put into B.xlsx, so that B.xlsx will update if I change or add additional data to my costs table in A.xlsx.

However, in B.xlsx, I would like to have the costs data from A.xlsx as part of another table like so: (I have ensured the column number and headers match)

---------------------
Table headers
---------------------
B.xlsx data
...
...
A.xlsx costs table
...
...
B.xlsx data
...
...
---------------------
Totals row
---------------------

(Because these data are part of a report, I must present them in one big table.)

My problem is that when I import the data from A.xlsx, I only have the option to import the whole file when I only need my costs table. Every time I delete the extra imported data it comes back again when I refresh the remaining data, since the connection in B.xslx is with the whole of A.xlsx.

Can I just connect a range of A.xlsx with B.xlsx? Or even better just a particular table in A.xlsx?

Thanks very much


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


Hi

Does anyone know how to look in a location and open the file which has the highest version number?

In the folder location StrFldr i have three folders called:
Input_Reference_Table_V1.xlsx, HDE_PPIII_Input_Reference_Table_V2.xlsx,
HDE_PPIII_Input_Reference_Table_V3.xlsx

I wanted to know whether it is possible in VBA to say go to StrFldr and open the file called Input_Reference_Table_*.xlsx which has the higest version number.

I also need to save the file but with a new version number. Does anyone know how to save a file using the filename but incrementing the number by 1?

Thanks....i hope this makes sense

Jessicaseymour


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.


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


Hi. I am wondering if anyone can help me with a macro to import data from multiple (around 500) files that all reside in the same folder, into a single master file.

I would want the following to take place.
1. open first data file (A.xlsx) and copy the survey data (A2:D20).
2. paste the copied data into the Master file (Master.xlsx).
3. close the data file.
4. repeat, opening the remaining files and pasting the corresponding data into the next open rows.

One thing to mention is that each file may have a different number of records, so file A may have 10 lines (A2:D12), and file B may have only 5 (A2:D7).

I am sure this is a pretty simple macro, but I am new to VBA and am lost. Any help is much appreciated. Thanks!


To make this simple .....I have two files
#File 1 . xlsx (This file is password protected)
#File 2 . xlsx (Linked with File 1 . xlsx)

Second file (File 2 . xlsx) takes cell values from first file . When opening this file, its asking for password of File 1 . xlsx
How can i disable this ? I cannot remove password from File 1 . xlsx


I have roughtly 1400 cvs files with comma delimited data that I would
like to import individually into a .xlsx file and then save the result so the data in each cvs file is now in a .xlsx file with either the same file name (different extension obviously) or a unique file name.


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 It 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 saveandenumerate()       
ActiveWorkbook.SaveAs FileName:= _
"C:\POSTING\DISCONNECTED # " & Format(Date, "MM-DD-YY") & ".XLSX", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
End sub


Beforehand thank you for any help