How to format date column in excel VBA

Question

0.00/5 (No votes)

See more:

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

Posted 25-Feb-18 18:30pm

edp.kharind

Updated 28-Feb-18 0:39am

Add a Solution

Comments

CHill60 26-Feb-18 3:43am

There is nothing wrong with your code that I can see ... unless the contents of those cells are not numeric.
So debug your code ... what are the contents of those cells before attempting to format them as dates?

Richard Deeming 28-Feb-18 12:21pm

Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

RedDk 28-Feb-18 13:00pm

As CH & RD have said above me, there are certain things cpians who answer questions here can tell from the code a poster shows here. I see, but honestly never agree with the maxim as stated by RD, injection problems ... because of concatenation use. But more than anything else, and this from a stallwart Excel spreadsheet user, you are using Excel to do something it was never intended to do. Namely export a formatted species of datatype.

Close Excel and forget about VBA. Get yourself Visual Studio and learn how to program in C# or C++ or VB.NET. My advice.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2018-02-28T00:39:00

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

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

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

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