Excel 2007 将工作表另存为 CSV 与在 VBA 中将工作表另存为 csv 不同
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8669112/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel 2007 Save worksheet as CSV differs to save worksheet as csv in VBA
提问by Sniff The Glove
I have a problem working with some worksheets within a workbook from a 3rd party website source.
我在处理来自 3rd 方网站源的工作簿中的某些工作表时遇到问题。
- the spreadsheet is available at the 3rd party source website
- the worksheet I am having problems with is the
ECworksheet in theall-euro-data-2009-2010.xlsin that zipfile
- 电子表格可在第3 方源网站上获得
- 我遇到问题的
EC工作表是该all-euro-data-2009-2010.xlszipfile 中的工作表
It's a list of football match data.
这是足球比赛数据的列表。
Manual Save as CSV
Now when this "EC" spreadsheet is in focus and I go to SAVE AS and choose .CSV, then spreadsheet is saved as a CSV and the data is perfect. ie the dates in column "B" and they are as they should be in DD/MM/YYYY format.
Manual Save as CSV
现在，当这个“EC”电子表格成为焦点并且我转到另存为并选择 .CSV 时，电子表格将保存为 CSV 并且数据是完美的。即“B”列中的日期，它们应该采用 DD/MM/YYYY 格式。
EG , taken from last row of the EC worksheet EC,24/04/2010,Tamworth,Ebbsfleet
EG ，取自 EC 工作表 EC,24/04/2010,Tamworth,Ebbsfleet 的最后一行
This is correct, if I go to the menu and choose to "SAVE AS" and chose CSV and accept all the prompts to keep it in CSV format blah blah the CSV file is save as EC,24/04/2010,Tamworth,Ebbsfleet
这是正确的，如果我转到菜单并选择“另存为”并选择 CSV 并接受所有提示以将其保留为 CSV 格式等等，CSV 文件保存为 EC,24/04/2010,Tamworth,Ebbsfleet
This is correct as the dates are in UK dd/mm/yyyy
这是正确的，因为日期在英国 dd/mm/yyyy
VBA Save as CSV
Now to make things a hell of a lot easier for myself as I need to save each worksheet off as a csv file so I am using the VBA code below to auto save each worksheet in the current directory named as worksheetname.csv ( eg EC.csv) each time the workbook is closed however this is changes the dates in column B to Americam format MM/DD/YYYY.
VBA Save as CSV
现在让我自己更容易一些，因为我需要将每个工作表保存为 csv 文件，所以我使用下面的 VBA 代码将每个工作表自动保存在名为 worksheetname.csv 的当前目录中（例如 EC. csv) 每次工作簿关闭时，但这会将 B 列中的日期更改为美国格式 MM/DD/YYYY。
However using VBA to save as a CSV the data ends up like EC,4/24/2010,Tamworth,Ebbsfleet
然而，使用 VBA 将数据保存为 CSV 数据最终像 EC,4/24/2010,Tamworth,Ebbsfleet
Notice the change from UK dd/mm/yyyy to now the American format mm/dd/yyyy
请注意从英国 dd/mm/yyyy 更改为现在的美国格式 mm/dd/yyyy
How can I make the change in the VBA to keep the dates in the format shown in the actual spreadsheet ie dd/mm/yyyy when saving in vba to CSV?
在将 vba 保存为 CSV 时，如何在 VBA 中进行更改以将日期保留为实际电子表格中显示的格式，即 dd/mm/yyyy？
I need this CSV option to work in VBA as these and many other similar spreadsheets are saved as CSV files for importation EVERY DAY.
我需要这个 CSV 选项在 VBA 中工作，因为这些和许多其他类似的电子表格都保存为 CSV 文件，以便每天导入。
My settings
My settings
I am using Excel 2007, on a Win 7 (64bit) PC here in the UK.
我在英国的 Win 7（64 位）PC 上使用 Excel 2007。
I have also tried add the Local:=True to the ws.SaveAs but this made no difference. ie the dates still saved as EC,4/24/2010,Tamworth,Ebbsfleet when saving from VBA
我也尝试将 Local:=True 添加到 ws.SaveAs 但这没有区别。即从 VBA 保存时，日期仍保存为 EC,4/24/2010,Tamworth,Ebbsfleet
Thanks
谢谢
Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Alerts_Suppress
ThisWorkbook.Save
Save_All_WorkSheets_As_CSV
Alerts_Enable
ActiveWorkbook.Close False
Application.Quit
End Sub
Sub Save_All_WorkSheets_As_CSV()
Dim Name As String
Dim Current_Directory As String
Dim ws As Worksheet
Dim wb As Workbook
Set wb = ActiveWorkbook
Dim i As Integer
i = 0
Current_Directory = ActiveWorkbook.Path & "\"
For Each ws In wb.Worksheets
Name = Current_Directory & LCase(ws.Name) + ".csv"
ws.SaveAs Name, xlCSV
i = i + 1
Next
End Sub
Sub Alerts_Suppress()
Application.DisplayAlerts = False
End Sub
Sub Alerts_Enable()
Application.DisplayAlerts = True
End Sub
回答by T I
This seems to be an inherent 'problem' with vba and I believe the only solution is to format your date field to a text field prior to exporting, obviously change Selectionto the actual range
这似乎是 vba 固有的“问题”，我相信唯一的解决方案是在导出之前将日期字段格式化为文本字段，显然更改Selection为实际范围
Sub convertToStr()
Dim c As Range
For Each c In Selection
c.Value = "'" + CStr(c.Value)
Next c
End Sub
回答by chris neilsen
I have been able to replicate the issue as described. My regional setting are NZ English. Setup is Excel 2010 32 bit, Win7 64bit
我已经能够像描述的那样复制这个问题。我的区域设置是新西兰英语。设置为 Excel 2010 32 位，Win7 64 位
I have used csvfiles with Excel many times over the years and usually found Excel native csv handling very fickle. I tend to write my own csv handlers.
多年来，我多次使用csvExcel 文件，通常发现 Excel 原生 csv 处理非常善变。我倾向于编写自己的 csv 处理程序。
Here's an example. You will need to modify it to suit your needs. Eg handle what you want and do not want ""'d and include or exclude empty cells etc
这是一个例子。您将需要对其进行修改以满足您的需要。例如处理你想要的和不想要""的，包括或排除空单元格等
Sub SaveAsCsv(ws As Worksheet, Name As String)
Dim fso As FileSystemObject
Dim fl As TextStream
Dim dat As Variant
Dim rw As Long, col As Long
Dim ln As String
Dim Quote As String
On Error GoTo EH
Set fso = New FileSystemObject
Set fl = fso.CreateTextFile(Name, True)
dat = ws.UsedRange
For rw = 1 To UBound(dat, 1)
ln = ""
For col = 1 To UBound(dat, 2)
' Modify this section to suit your needs
If dat(rw, col) <> "" Then
If IsNumeric(dat(rw, col)) Or _
IsDate(dat(rw, col)) Then
Quote = ""
Else
Quote = """"
End If
ln = ln & Quote & dat(rw, col) & Quote & ","
End If
Next col
fl.WriteLine Left(ln, Len(ln) - 1)
Next rw
EH:
On Error Resume Next
If Not fl Is Nothing Then fl.Close
Set fl = Nothing
Set fso = Nothing
End Sub
It includes an early bound reference to Microsoft Scripting Runtime, so set the reference or change to late bound if you want.
它包含对 的早期绑定引用Microsoft Scripting Runtime，因此如果需要，请将引用或更改设置为后期绑定。
To use it in your code, relace ws.SaveAs Name, xlCSVwith SaveAsCsv ws, Name
要在您的代码中使用它，请ws.SaveAs Name, xlCSV使用SaveAsCsv ws, Name
回答by Edgar Badawy
This is the case if you are using the SaveAs from the Excel menu but not when using VBA. Changing the regional setting has no effect to VBA flipping date to American format. But adding the below statement fixed it for me as suggested above.
如果您使用 Excel 菜单中的 SaveAs 而不是使用 VBA，则属于这种情况。更改区域设置对 VBA 将日期翻转为美国格式没有影响。但是添加以下语句为我修复了上面的建议。
ws.Columns("B:B").NumberFormat = "dd/mm/yyyy"
回答by Slai
When you face similar problems, Record Macro and compare the generated code to yours. Note that it uses Workbook.SaveAsinstead
当您遇到类似问题时，记录宏并将生成的代码与您的代码进行比较。请注意，它使用Workbook.SaveAs代替
For Each ws In wb.Worksheets
ws.Activate
wb.SaveAs Current_Directory & LCase(ws.Name) & ".csv", xlCSV ' check this line with Record Macro
Next
回答by Tony Dallimore
A month or so ago I got shot down when I made an aside about Excel sometimes converting UK dates that could bevalid US dates to US dates. So 1/4/11 (1 April 11) would become 4/1/11 (4 January 11) but 13/4/11 (13 April 11) would be unchanged. I could not duplicate the problem so had to admit that I must have made a mistake.
大约一个月前，当我对 Excel 有时将可能是有效的美国日期的英国日期转换为美国日期时，我被拒绝了。因此 1/4/11（11 年 4 月 1 日）将变为 4/1/11（11 年 1 月 4 日）但 13/4/11（11 年 4 月 13 日）将保持不变。我无法复制这个问题，所以不得不承认我一定犯了一个错误。
I encountered this problem eight or nine years ago and since then I have always passed dates in the format "1Apr11" to avoid the problem. Your question suggested I had been correct but something other than Excel was at the root of the problem.
八九年前我遇到过这个问题，从那以后我总是以“1Apr11”格式传递日期以避免这个问题。你的问题表明我是对的，但问题的根源是 Excel 以外的东西。
I set up a worksheet with numbers, dates and strings. It saved as a CSV file just as I would wish.
我设置了一个包含数字、日期和字符串的工作表。正如我所希望的那样，它保存为 CSV 文件。
I went to Control Panel and changed my location to "United States". I saved the worksheet again and this time the dates in the CSV file were "Americanised" as explained above.
我转到控制面板并将我的位置更改为“美国”。我再次保存了工作表，这次 CSV 文件中的日期是“美国化”的，如上所述。
I restored my location to "United Kingdom". My third CSV file was again correct.
我将我的位置恢复为“英国”。我的第三个 CSV 文件再次正确。
You claim that your Control Panel settings are correct. I think you need to check again and look for anything else that could be wrong since I now know how to switch this effect on and off.
您声称您的控制面板设置是正确的。我认为您需要再次检查并寻找其他任何可能出错的地方，因为我现在知道如何打开和关闭此效果。
