Trần Văn Bình
GTVT
- Tham gia
- 30/7/06
- Bài viết
- 409
- Được thích
- 377
- Nghề nghiệp
- GTVT
Hiện tôi có 02 sheet sheet chưa mã học viện từ C3 đến C & dòng cuối. Tại sheet LocKhoaHoc lấy dữ liệu SQL dán vào cột A, B, C
Có code và file đính kèm
Sub ADOExcelSQLServer_HangGPLX_Dtao()
' Carl SQL Server Connection
'
' FOR THIS CODE TO WORK
' In VBE you need to go Tools References and check Microsoft Active X Data Objects 2.x library
'
Dim Cn As ADODB.Connection
Dim Server_Name As String
Dim Database_Name As String
Dim User_ID As String
Dim Password As String
Dim SQLStr As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim i As Long
Dim Lr As Long, j As Long
Dim Ma_hoc_vien As String
Dim Arr()
With Sheet2
Lr = .Range("C1000").End(xlUp).Row
Arr = .Range("C3
" & Lr)
'where madk in('madk1','madk2',...... )
Ma_hoc_vien = "(Ma_hoc_vien ='" & Arr(1, 1) & "'"
For j = 1 To UBound(Arr) ' Da co arr(1,1) tren do vay j = 3
Ma_hoc_vien = Ma_hoc_vien & ",'" & Arr(j, 1) & "'"
Next
Ma_hoc_vien = Ma_hoc_vien & ")"
End With
Sheet10.Range("a5:I10000").Clear
Server_Name = "113.161.13.207,1708" ' Enter your server name here
Database_Name = "gplx_sogtvt" ' Enter your database name here
User_ID = "gplx" ' enter your user ID here
Password = "gplx123!@#" ' Enter your password here
SQLStr = "SELECT MADK, HO_VA_TEN, HangDaoTao FROM [GPLX_SoGTVT].[dbo].[NguoiLX_HoSo] where IN Ma_hoc_vien" ' Enter your SQL here
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, adOpenStatic
' Dump to spreadsheet
With Worksheets("HangDTao") ' Enter your sheet name and range here
.Range("A4").CurrentRegion.ClearContents
For i = 0 To rs.Fields.Count - 1
.Cells(3, i + 1).Value = rs.Fields(i).Name
Next
.Range("A4").CopyFromRecordset rs
.Columns.AutoFit
End With
' Tidy up
rs.Close
Set rs = Nothing
Cn.Close
Set Cn = Nothing
End Sub
Nhờ anh chị cùng các bạn trên diên đàn giúp
Có code và file đính kèm
Sub ADOExcelSQLServer_HangGPLX_Dtao()
' Carl SQL Server Connection
'
' FOR THIS CODE TO WORK
' In VBE you need to go Tools References and check Microsoft Active X Data Objects 2.x library
'
Dim Cn As ADODB.Connection
Dim Server_Name As String
Dim Database_Name As String
Dim User_ID As String
Dim Password As String
Dim SQLStr As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim i As Long
Dim Lr As Long, j As Long
Dim Ma_hoc_vien As String
Dim Arr()
With Sheet2
Lr = .Range("C1000").End(xlUp).Row
Arr = .Range("C3
" & Lr)'where madk in('madk1','madk2',...... )
Ma_hoc_vien = "(Ma_hoc_vien ='" & Arr(1, 1) & "'"
For j = 1 To UBound(Arr) ' Da co arr(1,1) tren do vay j = 3
Ma_hoc_vien = Ma_hoc_vien & ",'" & Arr(j, 1) & "'"
Next
Ma_hoc_vien = Ma_hoc_vien & ")"
End With
Sheet10.Range("a5:I10000").Clear
Server_Name = "113.161.13.207,1708" ' Enter your server name here
Database_Name = "gplx_sogtvt" ' Enter your database name here
User_ID = "gplx" ' enter your user ID here
Password = "gplx123!@#" ' Enter your password here
SQLStr = "SELECT MADK, HO_VA_TEN, HangDaoTao FROM [GPLX_SoGTVT].[dbo].[NguoiLX_HoSo] where IN Ma_hoc_vien" ' Enter your SQL here
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, adOpenStatic
' Dump to spreadsheet
With Worksheets("HangDTao") ' Enter your sheet name and range here
.Range("A4").CurrentRegion.ClearContents
For i = 0 To rs.Fields.Count - 1
.Cells(3, i + 1).Value = rs.Fields(i).Name
Next
.Range("A4").CopyFromRecordset rs
.Columns.AutoFit
End With
' Tidy up
rs.Close
Set rs = Nothing
Cn.Close
Set Cn = Nothing
End Sub
Nhờ anh chị cùng các bạn trên diên đàn giúp
Lần chỉnh sửa cuối:
