用于将excel数据转换为csv文件的vba编码 [英] vba coding for converting excel data into csv file

问题描述

我写了下面的代码，它将创建一个csv文件并存储指定月份的日期。问题是我希望日期格式为mm / dd / yyyy但我得到的日期是2013年6月21日，而不是06/21/2013。能告诉我如何实现这一目标吗？以下是我的代码。让我知道我错在哪里：

Hi,

I have written below code which will create a csv file and store the dates for a month specified. The problem is i want the date format in mm/dd/yyyy but i am getting the date as 6/21/2013 instead of 06/21/2013. Can you please let me know how i can achieve this. Below is my code. Let me know where i am going wrong:

cMonth = Month(Now)
cYear = Year(Now)
cDays = Days(cMonth, cYear)
rowCount = ws.UsedRange.Rows.Count
Set newwkbk = Workbooks.Add
k = 0
For i = 6 To (rowCount - 4) Step 1
    emp_cd = ws.Cells(i, 1).Value
    shift_cd = ws.Cells(i, 2).Value
    dateValue = ws.Cells(i, 3).Value
    If dateValue <> "" Then
        tempArray = Split(dateValue, "to")
        For j = tempArray(0) To tempArray(1) Step 1
        If j <= cDays Then
            dtmNewDate = DateSerial(cYear, cMonth, j)
            intDay = weekDay(dtmNewDate)
            If (intDay <> 7 And intDay <> 1) Then
                k = k + 1
                With newwkbk.Worksheets(1)
                    .Cells(k, 1).Value = emp_cd
                    .Cells(k, 3).Value = shift_cd
                    dateFormatted1 = j & "/" & cMonth & "/" & cYear
                     dateFormatted = Format(dateFormatted1, "dd-mm-yyyy")
                     .Cells(k, 2).Value = dateFormatted
                End With
            End If
        End If
        Next j
    End If
Next i

谢谢

Thanks

推荐答案

替换

Replace

dateFormatted1 = j & "/" & cMonth & "/" & cYear
dateFormatted = Format(dateFormatted1, "dd-mm-yyyy")
.Cells(k, 2).Value = dateFormatted

带

With

.Cells(k, 2).NumberFormat = "mm/dd/yyyy" ' Change to dd/mm/yyyy if you prefer day first format
.Cells(k, 2).Value = CDate(cyear & "-" & cmonth & "-" & j) ' yyyy-mm-dd ISO format eliminates ambiguity of month first vs. day first formats

经过测试在Excel 2010上



默认情况下，Excel将日期值格式化为m / d / yyyy。通过设置NumberFormat属性，我们告诉Excel使用指定的格式而不是默认格式。

Tested on Excel 2010

By default, Excel formats a date value as m/d/yyyy. By setting the NumberFormat property, we tell Excel to use the format specified rather than the default format.

这篇关于用于将excel数据转换为csv文件的vba编码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！