Excel 2007将工作表另存为CSV与在VBA中将工作表另存为CSV有所不同 [英] Excel 2007 Save worksheet as CSV differs to save worksheet as csv in VBA

问题描述

我在处理第三方网站来源的工作簿中的某些工作表时遇到问题.

I have a problem working with some worksheets within a workbook from a 3rd party website source.

• 该电子表格位于第三方来源网站
• 我遇到问题的工作表是该zipfile中all-euro-data-2009-2010.xls中的EC工作表

• the spreadsheet is available at the 3rd party source website
• the worksheet I am having problems with is the EC worksheet in the all-euro-data-2009-2010.xls in that zipfile

这是足球比赛数据的列表.

It's a list of football match data.

Manual Save as CSV
现在，当此"EC"电子表格成为焦点时，我转到另存为"并选择.CSV，则电子表格将另存为CSV，并且数据是完美的.即"B"列中的日期，并且应采用DD/MM/YYYY格式.

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.

EG，取自EC工作表的最后一行 EC，24/04/2010，Tamworth，Ebbsfleet

EG , taken from last row of the EC worksheet EC,24/04/2010,Tamworth,Ebbsfleet

这是正确的，如果我转到菜单并选择另存为"并选择CSV并接受所有提示以将其保持为CSV格式等等，那么CSV文件另存为 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

这是正确的，因为日期是英国的dd/mm/yyyy

This is correct as the dates are in UK dd/mm/yyyy

VBA Save as CSV
现在让我自己变得更容易了，因为我需要将每个工作表另存为一个csv文件，因此我正在使用下面的VBA代码将每个工作表自动保存在名为worksheetname.csv的当前目录中(例如EC. csv)，每次关闭工作簿时都会将B列中的日期更改为Americam格式MM/DD/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将数据另存为CSV时，最终会像 EC，4/24/2010，Tamworth，Ebbsfleet

However using VBA to save as a CSV the data ends up like EC,4/24/2010,Tamworth,Ebbsfleet

请注意从英国的dd/mm/yyyy更改为现在的美国格式mm/dd/yyyy

Notice the change from UK dd/mm/yyyy to now the American format mm/dd/yyyy

在将vba保存为CSV时，如何更改VBA以使日期保持为实际电子表格中显示的格式(即dd/mm/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?

我需要此CSV选项才能在VBA中使用，因为这些以及许多其他类似的电子表格都保存为CSV文件，以便每天导入.

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.

My settings

My settings

我正在英国的Win 7(64位)PC上使用Excel 2007.

I am using Excel 2007, on a Win 7 (64bit) PC here in the UK.

我也尝试将Local:= True添加到ws.SaveAs，但这没什么区别.即从VBA保存时日期仍保存为EC，4/24/2010，Tamworth，Ebbsfleet

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

谢谢

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

推荐答案

这似乎是vba固有的问题"，我相信唯一的解决方案是在导出之前将日期字段格式化为文本字段，显然会发生变化Selection到实际范围

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 Selection to the actual range

Sub convertToStr()
  Dim c As Range

  For Each c In Selection
    c.Value = "'" + CStr(c.Value)
  Next c

End Sub

这篇关于Excel 2007将工作表另存为CSV与在VBA中将工作表另存为CSV有所不同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！