----------
20190612

Excel 轉出為 CSV 格式的功能很難用.
加入 export marco:



----------

這篇可以加入 export macro 的功能可參考: 

ref:
https://www.linkedin.com/pulse/20140725234224-54506939-excel-to-csv-export-instead-of-save-as/

Excel to CSV - Export instead of Save As

Sometimes you need a good old CSV file even though your data comes from an Excel file. Here is how to export it with the click of a button without losing all your formulas and additional content.
You have built a complicated analysis in Excel. It uses multiple sheets, formulas, maybe some conditional formatting, pulls from external data source, uses charts, and other things that make Excel great. But some of the results needs to continue down a workflow in a CSV file or other form of flat text file (tab delimited, fixed width, ...). Maybe you need to import data into some archaic software system, maybe to upload to a web site (manual Positive Pay file upload to a bank?)... How do you do that?
The Bad Option: Save your fancy Excel file as CSV
If your output data is on a separate sheet, you could use the "Save As" function in the file menu and select CSV as the file type. You will get warning messages that certain things will get lost (all of the fancy things you built), but it will work.
Why is this a bad option? Because a lot can go wrong:
You could forget to save the file in the Excel format and lose all you work
You might need a different delimiter than a comma
Saving this way is disruptive to a workflow where you have calculations in a workbook that produces a true output
The process is pretty manual and will quickly become time consuming if you have to export the data frequently.
You need to design your whole workbook in such a way that the output data is all by itself on a sheet or you will need to manually edit the CSV file and remove comments and other things around your data of interest
The Good Option: Exporting the output data with a macro
All concerns above can be remedied with a simple macro. You can place an "Export" button wherever it fits best into your spreadsheet, assign the macro and export exactly the data you want into an external file (it could be CSV, fixed width, tab delimited or anything else).
Let's assume we need to get the following data into a flat file:
 
The requirements are that the fields are separated by a semi-colon, we don't want the title or column headers in the output file, and the allowance amount should always have two decimals.
We can quickly generate this output string in an additional column by simply concatenating the individual fields with &";"& and also forcing the allowance formatting with the TEXT formula:
 
Now we need to tell our macro where our output data is located. The best way is to use a named range for the cell one row above where the output data starts (F3 in our example). Let's use the name macroSwitch_OutputTextBelow (be descriptive in the names - it will make it a lot easier later to understand the spreadsheet):
 
Next open the Excel Visual Basic for Applications Developer Console (press ALT-F11), insert a new module, and paste the following code into the macro window:
Sub ExportRangetoFile()
' -------------------------------------------------------------------------------------------------------------------
'
' General function: exports strings contained in a range (1 column wide, varying height) into a text file
'
' -------------------------------------------------------------------------------------------------------------------
Const ForAppending = 8
Const rStartCell = "macroSwitch_OutputTextBelow"
Const strFileNamePrefix = "Your output file "
Dim fs, f
Dim saveFile As String
Dim WorkRng As Range
Dim iHeightOfDataRange

On Error Resume Next

' figure out how many rows to select and export:
' start at range "macroSwitch_OutputTextBelow"
' look down to last filled in row to count number of rows with actual check data
' then use that height as height for range to be exported
iHeightOfDataRange = Range(rStartCell).End(xlDown).Row - Range(rStartCell).Row
Set WorkRng = Application.Selection
Set WorkRng = Range(rStartCell).Offset(1, 0).Resize(iHeightOfDataRange, 1)
Application.ScreenUpdating = False
Application.DisplayAlerts = False

' create file to export to
Set fs = CreateObject("Scripting.FileSystemObject")
saveFile = Application.GetSaveAsFilename(InitialFileName:=strFileNamePrefix & Format(Now(), "yyyy-mm-dd"), _
filefilter:="Comma Separated Text (*.CSV), *.CSV")
Set f = fs.createTextFile(saveFile, ForAppending, TristateFalse)

' and write export range line by line
For Each Row In WorkRng.Rows
f.writeline Row
Next Row
f.Close

Application.CutCopyMode = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True

' if needed:
' alert user to check content for file manually
MsgBox "Make sure the file looks correct"
' open the file for user review
Shell "notepad.exe " & saveFile, vbNormalFocus

End Sub
Lastly, add a button to the main spreadsheet (from the Developer tab - might need to be activated in Excel's options) and assign the macro:
 
Now you simply click the button every time you need the export file and get the following:

 
Final thoughts
The macro does not contain a lot of error checking and it will overwrite an existing CSV file with the same name without warning. The macro assumes one consecutive block of rows and gaps (blank rows) will throw it off. You might want to tweak the pre-selected type from CSV to something else and change the time stamp in the file name. You can force different formatting on the output data. You could customize the warning message or remove it. You could lock cells so nobody changes the formatting of the output cells...
But even without any further customization - this macro provides a powerful building block for your Excel files. You can now keep all your formulas and analyses in a full-blown Excel file and easily export exactly what you need in the format you need.


Thanks for the inspiration Florian.  As an alternative, this might serve well:
Sub Save_as_CSV()
    ActiveSheet.Copy
    ActiveWorkbook.SaveAs Filename:= "C:\Users\bug_bunny\data.csv" _
        , FileFormat:=xlCSV, CreateBackup:=False