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

Close Excel Workbook using VBA Macros
How to close an Excel workbook using VBA and macros, including how to save the file before you close it or discard ...
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 ...
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 ...
Macro to get Data from Another Workbook in Excel
Macro to get data from a workbook, closed or open, over a network or locally on your computer. This is a versatile ...
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 ...
List all Conditional Formatting Formulas in Excel
List all conditional formatting formulas in a worksheet in Excel. This allows you to quickly view and manage all of ...

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







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


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.


I have a few files sent out to field users. These users, for some reason, really love to change my .xlsm excel files to .xlsx when they save. Is there any VBA code that will help with this? The closest I could come up with is using the BeforeSave workbook event, but this still allows the user to make any changes they want to extension once the Save As dialog box appears.

Example of what I want:

User clicks save as, types in a new file name and selects .xls
Macro assigns the previous file extension to the file then saves it.


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!


I have an .xlsm file that I saved last night at 4:30pm today any time i try to save the file it auto crashes.

I cant save it on the network or locally, I can't save it as a dif file type it all causes the crash.

Other files are fine its just this one.

I have tried moving all sheets to a dif file still crashes..

Any ideas?? I will lose days of work if I can't get this file to work!


Hello,

I have a text string "fn_name" which reads:

fn_name = "C:\Folder1\Folder2\testfile.xlsx"

The file extension of this string could also be .xls , .xlsm, .xlsb etc...

I need VBA code to detect If the file extension is ".xlsx" :

- Save As the file as testfile.xlsm in the same directory path
- Change the file string to "C:\Folder1\Folder2\testfile.xlsm"

many thanks
Andy


Hi Folks,

I am dynamically generating and naming an xl file based on some conditions. The use the following code-

Dim newFile As Long
newFile = FreeFile()
If FileName "" Then
Open "C:\imacro_files\" & FileName & ".xls" For Binary Access Write Lock Write As newFile
Close newFile

Application.Workbooks.Open FileName:="C:\vba_files\" & FileName & ".xls"

The problem here is if I try to save and open it as xlsx it gives an error like- Excel cannot open the file because the file format or file extension is not valid. Make sure file extension matches the format of the file.

Next, if I go ahead with using .xls and then format a column to have zeros appended to it the formatting just doesnt work. I get an error like- The file is Text (tab delimited file) and so it cant support the formatting. Now I not sure how do I handle this as I absolutely need to format the column. I tried using all kinds of functions and also storing it as a string but nothing works. All formatting is just removed and at the same time it doesn't even let me open it if in .xlsx format (as stated initially).

So, basically it would help if I could get either problem solved-
1. Be able to save and open the file as .xlsx
2. Be able to format the text tab delimited file or prevent the file from getting saved as text tab delimited
3. Any other workaround?

PS: The funny thing is though I get an error about the file being text tab delimited it still shows the format as .xls (Microsoft Office 97/2003)
Also, it might help to know that in the orgin file I am importing data from an xml file. From this origin file data is then transferred to this new dynamic file.
xml -> xlsm --> xls
Any inputs will be much appreciated.

Thanks
Prajakta


I've got a macro that I've pieced together from different sources on the web designed to combine a lot of excel workbooks with single worksheets into one workbook with many worksheets. My problem is, the extension used in the VBA is .xls, causing me to have to go in manually each time the extension is something else, say, .xlsx, and change the code before I can put the files together.

Does anyone know how to make this tweak so that I can have multiple file extensions automatically checked? Maybe even a list of them could be added to the first sheet under Accepted File Extensions so that someone else who was using it could add theirs if it wasn't already in the list without having to open up the VB editor?

I've attached the file. Thanks

******EDIT******

It doesn't seem to work at all when I go from .xls to .xlsx, although .dat and .rep both work. Not sure what causes this either.


When I prepare a file with macro's (to be used by other people), I save this file as a template with macro's with the extension *.xltm (template with macro).
Therefore no one can overwrite my file.

These other people can open this template (e.g. double-click, but not file-open).

But ............. when these people save this file, Excel automatically saves it as a normal Excel-file with the extension *.xlsx.
Ok, a message is seen after pressing <enter> or clicking Save, but does normal users know, what they have to do then?

If I'm making a template with macro's, I want Excel to have this file saved as a file with macro's.
Whatever did I make a file with macro's for?

Is this a bug or is it done on purpose?

Thnx in advance for any reaction.


I am having a problem with the formatting once a workbook is saved as a pdf file. when i view the individual sheets within the workbook in print preview, they look fine (fit to one page), but as soon as I print or save to pdf, the individual sheets lose all formatting (everything seems to expand - spaces are larger, fewer words on the sheet, logo is twice the size). I have even tried using the save as xps file. I have both typed code and also recorded a macro to see if it made any difference.

Any suggestions?

HELP!!




I am working with a large XLSX file 100K+ rows and I need to split it into smaller XLS files of 10K rows. Searching this forum, I found the code below for a macro that almost does what I need it to do, but I need it tweeked so that it will save it as an XLS file and I also want to change the naming structure to File1, File2, ect. Can anyone help with this?



Please Login or Register  to view this content.



Hello,

I am trying to solve the following problem:

1. I have a number of csv-files, each has 5 columns of data.
input1.csv
input2.csv
......
inputN.csv

2. I want to
- do one operation on 3rd column of each file (IMABS(col(C)),
- place result into 6th column (first free column in my case) and then
- copy this new column into first free column of another excel file Output.xlsx

pasting from the 2nd row in each column of Output.xlsx (as the 1st rows will have titles in them)


To summarise: I want 6th column of each input.csv file in SourceFolder copied into separate column of output.xlsx file

I should be ok with writing code for copying and pasting within one file, but what I don't know is how:
1) to open multiple files from a specific folder (Sourcefolder)
2) Copy and Paste data from one file into another file


Any help with this issue is greatly appreciated.
Thanks


So I am trying to design a workbook that has two worksheets... one with instructions and a button for users to click to "Save as .CSV File", another for the data that will go into that CSV file.

Here are the Macro requirements:
1. The user will be prompted for the File & Location to save the .CSV file
2. If they click 'Cancel', no changes will be made (and unlike my current code, it won't ask them to debug).
3. Confirmation of the filename is not necessary even though it's currently included in my Macro
4. The file will automatically "reopen" so that they only see the new .CSV file without the original Instruction tab.

Here's what I've got so far. Any help would be greatly appreciated! =)

Code:

Sub ConvertCSV()

    ' selects the Manual_Template Worksheet.
    Sheets("Manual_Template").Select

    ' prompts the user for the filename to save the CSV file with.
    fileSaveName = Application.GetSaveAsFilename( _
        fileFilter:="Comma Seperated Values (*.csv), *.csv")

    ' displays pop-up box informing user of Save-to location
    If fileSaveName <> False Then
        MsgBox "File will be saved as: " & fileSaveName
    End If
      
        
    ' saves the worksheet with the previously specified location and filename
    ActiveWorkbook.SaveAs Filename:=fileSaveName, FileFormat:=xlCSVWindows
    
    ' deletes the Instructions worksheet for cleanup
    Application.DisplayAlerts = False
    Sheets("Instructions").Delete
    Application.DisplayAlerts = True

End Sub





Hi all... Need a bit of help. What do I need to add to the code below in order to specify that my output (saveas) file is saved to the same path as the parent macro file? Here's the code I have for my SaveAs:


' Automatic New File Creation and Save
Dim bk As Workbook
Dim myFileName As String
With Worksheets("Input")

myFileName = .Range("TD").Value & "_" & .Range("IBMsn").Value
End With

Sheets("Output").Select
ActiveSheet.Copy ' creates new workbook
Set bk = ActiveWorkbook
bk.SaveAs Filename:=myFileName, FileFormat:=xlTextPrinter
bk.Close Savechanges:=False


MsgBox ("File creation complete! The flat file has been saved to the same directory as this macro file.")


I start by opening an existing XLSX file in XYZ directory. The macro copies some information from that existing file (e.g. File1.xlsx) to a New workbook. I make various changes that all work correctly. However, my problem is that I want the New workbook to SaveAs in the same directory that was used for the original workbook when it was opened. I do not want to specify a directory path name because the macro will be used on different files in different directories. The format I need is the file name "TSValues" and then append the date and xlsx. I've tried different things but it would always end up in the My Documents folders. Here's the code I used more recently:

VB:

SaveAsName = ThisWorkbook.Path 
ThisWorkbook.SaveAs "TSValues" & Format(Now, "yyyymmdd") & ".xlsx" 


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



I appreciate any help.
Thanks,
Bob

I have a workbook that is used in a mixed Excel 2002, 2003, 2007 and probably 2010 environment. About a month ago, all the VBA code 'disappeared' from the workbook. I presume that one of the users with 2007 did a 'save as' and accepted the default .xlsx format and then later on a 2003 user saved it back to .xls format since that was the extension when I was called about the problem.

I need a way to force the workbook to be saved only as .xls (format 56). I have some questions about this:

I would like to allow all users to be able to save, not 'save as' the file as they work on it in case some disaster happens. If a 2007 or 2010 user opens an .xls file (format 56), and saves it, is the file saved with it's original file format (.xls), or as a file of the default file format of 2007/2010 ? I think that the answer is .xls.

I would like to allow certain users password protected access to 'save as' across all platforms. What is the best way to accomplish this ? I was thinking that I could put password prompting/checking code in Workbook.BeforeSave and check that SaveAsUI is true and be done but I am not sure that this would be sufficient. Any ideas about this ?

Lastly, I am not sure about how to use Application.DefaultSaveFormat. I have played around with this by putting this in the beginning of my code, but when I go to 'save as' the suggested default format is not the one that I specified with Application.DefaultSaveFormat. Does Application.DefaultSaveFormat only apply to 'save' and not 'save as' since the user can always override the suggested format in 'save as' ?

I am not proud. If anyone knows of code that will accomplish what I want, i.e force the saving of a workbook with a certain format, please point me in that direction.

Thanks


I asked this in the SAS Discussion Forums, but no replies. I am hoping that someone here can help me solve this problem.

I am using SAS 9.2 and Excel 2007.

I have a SAS program that works well. It uses PROC EXPORT to create an Excel file. Then calls Excel to open and I have a workbook that has some macros that format the sheet. This all works well. At this point I have SAS running. Excel is open with the workbook that I have Exported and the workbook that contains the macros. The macros run and I can see that the workbook that was exported is being updated correctly. What happens then is I save this exported Excel workbook that has been updated to the original name that I used in the PROC EXPORT. I am using DDE commands.

/***************************************************/
/* Export the new file to Excel 2007 */
/***************************************************/
PROC EXPORT data = lhost.chdaily2
( keep = Category Cumulative d: )
outfile = "&mydir.\chdaily.xlsx"
DBMS = EXCEL REPLACE;
SHEET="Sheet1";
run;

.....
The above works fine.

data _null_;
file cmdexcel;
put ¯file; *open the file containing the macro;
put &chekfile; *open the daily checkin file;
put '[RUN("chdaily_test_.xlsm!Macro_Daily_Checkin")]';
*run the macro;
run;

....
This works fine.


/* Save the changes made */
%let excelout = &mydir.\chdaily.xlsx;
data _null_;
file cmdexcel;
put '[error(false)]';
put "[save.as(%bquote("&excelout",51))]";
run;

At this point it for some reason it makes a backup file in the directory that it is sitting.

Can anyone tell me why? And if I can stop it from doing the backup.
Also, the line above that says - put '[error(false)]' - how would I write this for the excel vba - Application.DisplayAlerts = false - ?

Thanks, Nancy


Hi, I have excel 2003 and i use macro to copy data from a file that ends in .xlsx. the data file is coming from a department other than mine and i used macro to automatically copy the data from the .xlsx file to my excel file. However i cannot open .xlsx file using macro, and am wondering if I need to use some special code to do; i.e.,
Workbooks.Open Filename:=MRfile, UpdateLinks:=0, WriteResPassword:="4701", converter:=true?

I never used converter before and please let me know how to approach this to automatically open .xlsx file. Thank you for your help


DLookup or VLookup ?????

Ok, let me try and work out this question in a way all of you will understand.
Using office 2007

I have 2 Xlsx files and I want to put the values from one column of the second file into a blank column in the first file
File names:

W2B.xlsx
UTAB.xlsx

W2B.xls receives the data in Col 13 M:M
UTAB.xlsx holds the data in Col 10 J:J

Both starts in row 2 ....allowing for the header
If u need the col names they are Points in the first file and Rating in the second file
Both files have a ID col and it resembles like the data below

40700NARR0101
40700NARR0102
40700NARR0103
40700NARR0104
40700NARR0105

--------------------
40700Narr0101
40700Narr0102
40700Narr0103
40700Narr0105

However you will notice that some rows are missing from the second file, such as 40700NARR0104
So in its place I guess a zero would do or delete the row from the first file

Both files reside in G:\Data\

Hoping someone can help here

Graham


I am using a pre-formatted workbook I designed with several local and global named ranges in Excel 2007. I have no problem inserting/deleting rows or columns when I first open the original version of the workbook. However, after I save it (with a new file name), close it, and re-open it. I cannot insert/delete rows or columns, group/ungroup rows, etc. without Excel crashing. By crashing, I mean I get the form that states "Microsoft Excel has encountered a problem and needs to close..."

Here is some of the troubleshooting I've tried:
1. If I insert a new, blank worksheet into the saved, re-opened and troublesome file, I can insert rows no problem. So, my conclusion is it's not the workbook, only the pre-formatted worksheets with local range names.
2. I asked a colleague to try using this file on her computer, and she encounters the same issue as me. So, it's not necessarily my computer.
3. I added a lot of named ranges of various types to a new, blank workbook, saved it, re-opened it, and had no issues inserting rows.
4. The troublesome worksheets are not protected.
5. There are no external links in the workbook.
6. I typed "Option Explicit" at the top of every Worksheet and Module in VBA.
7. I've tried changing to Manual Calc mode and Auto Calc mode.
8. None of the problematic worksheets has any VBA code behind it.
9. I tried inserting a row onto one of the troublesome worksheets via VBA code, and I got the following error message: "Run Time Error '-2147417848 (80010108)': Automation error The object invoked had disconnected from its clients." And then Excel freezes, and I have to close using Task Manager, End Task
10. I've tried saving as .xlsx and .xlsm files

As I said, I am able to insert rows when I first initiate the file. It's only when I save it, close, it and re-open it that I cannot insert rows.

My hunch is it has to do with the range names on the worksheets causing the problems, but I guess it could be anything.

Could it be something is happening to those worksheets during the save action?

Please help.

Thanks.



I have a small 5-column, 100-row set of data in a single worksheet.

When I manually "Save As" into to a CSV file, the CSV file will open into a 5-column worksheet.

When I tried to export the data into a CSV file, the CSV file opens with each row as a single string of text (although it does have symbols to indicate where the column breaks should be).

Can my VBA code be changed to fix this?
Quote:

'To save only the active worksheet as CSV file. File name in cell "A1".
ActiveSheet.Copy
ActiveWorkbook.SaveAs Filename:="C:\Temp\" & Worksheets("CT_Data").Range("A1") & ".csv", _
FileFormat:=xlText

'Remove row 1 with file name, save, close.
Application.Goto Reference:="R1C1"
Selection.EntireRow.Delete
ActiveWorkbook.Save
ActiveWorkbook.Close SaveChanges:=False

Thanks.... RICK


Some of our users on Excel 2007 (including myself) are receiving the error below when trying to password protect a .xlsx file. The error message does not appear when applying a password to a .xls file. After the password is "applied" the .xlsx file becomes corrupted and the error is shown "Excel cannot open the file 'FILENAME.xlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.". Please note that this only affects some Excel 2007 users and only applies to those affected users when placing a password on .xlsx files.

I have checked out the event logs and reinstalled Office 2007 without success. Does anyone know the answer?

Error message when applying a password to a .xlsx file

The document was saved successfully, but Excel cannot re-open it because of a sharing violation. Please close the document and try to open it again.

Steps to Reproduce:

1. Open Excel
2. Office Button | Save
3. Tools | General Options
4. Enter password in Password to open field | Click OK
5. Reenter password | Click OK
6. Click OK to save file
7. Error above is received.