我从SQL Server获取数据,现在想将其复制到Excel工作表中。
我尝试设置“ start_date”的格式,但它仅将格式应用于第一列,所有其他列均以存储在SQL Server中的形式显示。
Sub ReadMbdataFromSQL()
Dim Server_Name,Database_Name,User_ID,Password,SQLStr As String
Set Cn = CreateObject("ADODB.Connection")
Set RS = New ADODB.Recordset
Server_Name = ""
Database_Name = ""
User_ID = ""
Password = ""
SQLStr = "SELECT lot,po,start_date,input_sponge_type,input_sponge_type FROM PalladiumNitrateMB WHERE start_date >= '" & Format(Range("start"),"mm-dd-yyyy") & "' AND start_date < '" & Format(Range("end"),"mm-dd-yyyy") & "' ORDER BY lot ASC"
Set Cn = New ADODB.Connection
Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
";Uid=" & User_ID & ";Pwd=" & Password & ";"
RS.Open SQLStr,Cn,adOpenKeyset,adLockBatchOptimistic
With Worksheets("PdNitrateMassBalance").Range("A5:N600")
RS("start_date") = Format(RS("start_date"),"dd/mm/yyyy")
.ClearContents
.CopyFromRecordset RS
End With
RS.Close
Set RS = Nothing
Cn.Close
Set Cn = Nothing
End Sub