如何格式化excel VBA中的日期列 [英] How to format date column in excel VBA

问题描述

I have an Excel WorkSheet.I am going to populate Recordset from Sql Data using vba Macro.It works fine but I would like to know how to format Date Column as "dd/MM/yyyy" from (E5:E10).I have tried below code but date format is not working.Please advsie how to do that....

我尝试过的事情：

What I have tried:

Private Sub CmdShowDetails_Click()

Dim ConKha As New ADODB.Connection
Dim TmpRst As New ADODB.Recordset
ConKha.Open "Provider=SQLOLEDB.1;Password=Pass@123;Persist Security Info=True;User ID=sa;Initial Catalog=GloveERP;Data Source=Arindsql"
ConKha.CommandTimeout = 600
SqlStr = "Select 'Outward' SupplyType,'Job Work' SubType,'Others' DocType,A.GatePassNo DocNo,Convert(Char(10),A.GatePassDate,103) DocDate,'K H EXPORTS INDIA PRIVATE LIMITED GLOVE DIVISION' From_OtherPartyName,'33AAACR1714R1ZA' From_GSTIN,'142/1 Trunk Road' From_Address1 ,'Perumugai' From_Address2,'Perumugai' From_Place,'632009' From_PinCode,'TAMIL NADU' From_State,"
SqlStr = SqlStr & "Replace(Replace(Replace(Replace(Replace(REPLACE(REPLACE( REPLACE( REPLACE( B.CompanyName, '!', ' ' ), '#', ' ' ), '$', ' ' ), '&', ' '), '.', ' '),',',' '),';',' '),':',' '),'*',' ') "
SqlStr = SqlStr & " To_OtherPartyName,B.GSTNo TO_GST,B.Address1 + ' ' + B.Address2 To_Address1 ,B.Address3 + ' ' +  B.Address4 To_Address2,'' To_Place,'632009' To_PinCode,'Tamil Nadu' To_State,C.ItemDescriptions Product,C.Purpose [Description],C.HSNCode,D.UoMName Unit,C.Quantity, (C.Quantity * C.Rate) + ((C.Quantity * C.Rate) * (C.GSTPercentage/100))  AssesableValue,'9+9+0+0' TaxRate, 0 CGSTAmount, 0 SGSTAmount,0 IGSTAmount,0 CESSAmount,'Road' TransMode,10 Distance,"
SqlStr = SqlStr & " '' TransName,'' TransId,'' TransDocNo,Convert(Char(10),A.GatePassDate,103) TransDate,A.VehicleNo,'' ErrorList from Material_Trn_GatePassHeader A Join Common_Ref_Company B On (B.CompanyId = A.SupplierId) Join Material_Trn_GatePassDetails C On (C.GatePassHeaderId = A.GatePassHeaderId) Join Common_Ref_UoM D On (D.UoMID = C.UOMId) Where (A.GatePassNo Between '" & TxtFromGPNo.Text & "' And '" & TxtToGPNo.Text & "')"
TmpRst.Open SqlStr, ConKha, adOpenForwardOnly

Worksheets("eWayBill").Range("A4").CopyFromRecordset TmpRst

Worksheets("eWayBill").Range("E4:E10").NumberFormat = "dd/mm/yyyy"

TmpRst.Close
ConKha.Close

推荐答案

'，''），'&'，''），'。'，''），'，'，''）， ';'，''），'：'，''），'*'，''）
SqlStr = SqlStr&To_OtherPartyName，B.GSTNo TO_GST，B.Address1 +''+ B.地址2 To_Address1，B.Addre ss3 +''+ B.Address4 To_Address2，''To_Place，'632009'To_PinCode，'Tamil Nadu'To_State，C.ItemDescriptions Product，C.Purpose [Description]，C.HSNCode，D.UoMName Unit，C.Quantity， （C.Quantity * C.Rate）+（（C.Quantity * C.Rate）*（C.GSTPercentage / 100））AssesableValue，'9 + 9 + 0 + 0'TaxRate，0 CGSTAmount，0 SGSTAmount，0 IGSTAmount ，0 CESSAmount，'Road'TransMode，10 Distance，
SqlStr = SqlStr& ''TransName，''TransId'，'TransDocNo，Convert（Char（10），A.GatePassDate，103）TransDate，A.VehicleNo，''来自Material_Trn_GatePassHeader的错误列表A加入Common_Ref_Company B On（B.CompanyId = A.SupplierId ）加入Material_Trn_GatePassDetails C On（C.GatePassHeaderId = A.GatePassHeaderId）Join Common_Ref_UoM D On（D.UoMID = C.UOMId）Where（A.GatePassNo Between'& TxtFromGPNo.Text&'And'& TxtToGPNo .Text&'）
TmpRst.Open SqlStr，ConKha，adOpenForwardOnly

工作表（eWayBill）。范围（A4）。CopyFromRecordset TmpRst

工作表（eWayBill）。范围（E4：E10）。NumberFormat =dd / mm / yyyy

TmpRst.Close
ConKha.Close

', ' ' ), '&', ' '), '.', ' '),',',' '),';',' '),':',' '),'*',' ') " SqlStr = SqlStr & " To_OtherPartyName,B.GSTNo TO_GST,B.Address1 + ' ' + B.Address2 To_Address1 ,B.Address3 + ' ' + B.Address4 To_Address2,'' To_Place,'632009' To_PinCode,'Tamil Nadu' To_State,C.ItemDescriptions Product,C.Purpose [Description],C.HSNCode,D.UoMName Unit,C.Quantity, (C.Quantity * C.Rate) + ((C.Quantity * C.Rate) * (C.GSTPercentage/100)) AssesableValue,'9+9+0+0' TaxRate, 0 CGSTAmount, 0 SGSTAmount,0 IGSTAmount,0 CESSAmount,'Road' TransMode,10 Distance," SqlStr = SqlStr & " '' TransName,'' TransId,'' TransDocNo,Convert(Char(10),A.GatePassDate,103) TransDate,A.VehicleNo,'' ErrorList from Material_Trn_GatePassHeader A Join Common_Ref_Company B On (B.CompanyId = A.SupplierId) Join Material_Trn_GatePassDetails C On (C.GatePassHeaderId = A.GatePassHeaderId) Join Common_Ref_UoM D On (D.UoMID = C.UOMId) Where (A.GatePassNo Between '" & TxtFromGPNo.Text & "' And '" & TxtToGPNo.Text & "')" TmpRst.Open SqlStr, ConKha, adOpenForwardOnly Worksheets("eWayBill").Range("A4").CopyFromRecordset TmpRst Worksheets("eWayBill").Range("E4:E10").NumberFormat = "dd/mm/yyyy" TmpRst.Close ConKha.Close

不要将日期数据类型转换为字符串！这可能会导致一些问题。

Do not convert date data type into string! This may cause several issues.

Convert(Char(10),A.GatePassDate,103) DocDate

根据MS SQL服务器版本，您可以使用： FORMAT（Transact-SQL）| Microsoft Docs [ ^ ]返回格式化值的函数。注意：此功能在MS SQL Server 2012及更高版本中可用。



我建议将日期作为日期返回。尝试在从记录集复制数据之前格式化单元格：

Depending on MS SQL server version, you may use: FORMAT (Transact-SQL) | Microsoft Docs[^] function which returns formatted value. Note: this function is available in MS SQL Server 2012 and higher.

I'd suggest to return date as date. Try to format cells just before you copy data from recordset:

With ThisWorkbook.Worksheets("eWayBill")
    .Range("E4:E10000").NumberFormat = "dd/MM/yyyy" '"dd/mm/yyyy"
    .Range("A4").CopyFromRecordset TmpRst
Ed With

注意： mm 与 MM 不同，因为 mm 表示分钟， MM 表示月份。





如果它不起作用，你应该循环throguh单元格，更改其格式并再次插入日期值：

Note: mm is not the same as MM, because mm is representing minutes and MM is representing months.


In case when it won't work, you should loop throguh the cells, change its format and insert date value again:

Dim i As Long, wsh As Worksheet

Set wsh = ThisWorkbook.Worksheets("eWayBill") 
i=4
Do While wsh.Range("E" & i)<>""
    wsh.Range("E" & i).NumberFormat="dd/MM/yyyy"
    wsh.Range("E" & i) = wsh.Range("E" & i)
    i = i +1
Loop

这篇关于如何格式化excel VBA中的日期列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！