如何格式化excel VBA中的日期列

本文介绍了如何格式化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