edp.kharind Ответов: 1

Как отформатировать столбец даты в 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....


Что я уже пробовал:

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

CHill60

Насколько я вижу, с вашим кодом все в порядке ... если только содержимое этих ячеек не является числовым.
Так что отладьте свой код ... каково содержимое этих ячеек, прежде чем пытаться отформатировать их в виде дат?

Richard Deeming

Ваш код уязвим для SQL-инъекция[^]. НИКОГДА используйте конкатенацию строк для построения SQL-запроса. ВСЕГДА используйте параметризованный запрос.

Все, что вы хотели знать о SQL-инъекции (но боялись спросить) | Трой Хант[^]
Как я могу объяснить SQL-инъекцию без технического жаргона? | Обмен Стеками Информационной Безопасности[^]
Шпаргалка по параметризации запросов | OWASP[^]

RedDk

Как сказал выше CH &RD, есть определенные вещи, которые cpians, отвечающие на вопросы здесь, могут сказать из кода, показанного здесь плакатом. Я вижу, но честно никогда не соглашусь с максимой, как заявил РД, инъекционные проблемы ... из-за использования конкатенации. Но больше всего на свете, и это от пользователя электронных таблиц stallwart Excel, вы используете Excel, чтобы сделать то, что он никогда не собирался делать. А именно экспортировать форматированный вид типа данных.

Закройте Excel и забудьте о VBA. Приобретите себе Visual Studio и научитесь программировать на C#, C++ или VB.NET-да. мой Совет.

1 Ответов

Рейтинг:
2

Maciej Los

Не преобразуйте тип данных даты в строку! Это может вызвать несколько проблем.

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

В зависимости от версии MS SQL server вы можете использовать: Формат (Transact-SQL) | Microsoft Docs[^] функция, которая возвращает форматированное значение. Примечание: эта функция доступна в MS SQL Server 2012 и выше.

Я бы предложил вернуть дату как дату. Попробуйте отформатировать ячейки непосредственно перед копированием данных из набора записей:
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 ячейки, изменить его формат и снова вставить значение даты:
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

RedDk

Я никогда не могу понять, как cpians, отвечающие на вопросы, помеченные языковой группой, такой как плакаты, похоже, могут экстраполировать идиоматически продвинутое "знание" на ум плакатов. Я вижу "ADO" и "Recordset" и сразу же "получаю" утверждение VBA. Как я уже говорил бесчисленное количество раз раньше, возможно, я сказал Это себе и только думаю, что я так отреагировал на эту инъекцию табуляции, то, что хочет плакат, может быть сделано без TSQL (восклицательный знак)

Maciej Los

Что ж... Существует несколько типов Аскеров (в порядке ненависти к любви):
1. Те, кто хочет остаться в глубоком убеждении, что их путь намного лучше, чем кто-то другой пытается дать, поэтому они просят снова, снова и снова (глупые аскеры),
2. Те, кто хочет получить точный ответ или "рабочее решение" (ленивые аскеры),
3. Те, кто хочет получить более подробную информацию, даже если в данный момент они действительно не знают, как использовать эти знания (разумные аскеры)
Я никогда не знаю, кто спрашивает и какова подоплека просьбы. Я могу только догадываться.

RedDk

Ты достанешь меня, мл...
1. Грязь
2. Мрази
3. Мире
Я просто рад, что тебе не пришлось спрашивать меня, что я имел в виду под "табом";-)

Maciej Los

Я боялась спросить...
:смеяться: