출처 :: http://cafe.naver.com/xlsvba/21087
'================================================================================================
' Procedure : GetRecordsetThisworkbook
' Description : Microsoft.ACE.OLEDB.12.0를 이용한 특정시트를 query
' Author : Evinious
' Parameter : xSql : 쿼리문
' shtName : 출력 시트명
' targetRange : 출력시트내 시작셀
' MS-SQL에서 EXCEL파일을 쿼리하는 용도로 많이 쓰임
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.4.0', N'AllowInProcess', 1
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.4.0', N'DynamicParameters', 1
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO
-- excel 2007 이상
INSERT INTO DB내_대상_테이블명
SELECT *
FROM OPENROWSET
('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml;HDR=YES;Database=C:\test\TEST.xlsx;'
,'SELECT * FROM [TblMenu$]')
GO
-- excel 2003 이하
INSERT INTO DB내_대상_테이블명
SELECT *
FROM OPENROWSET
('Microsoft.ACE.OLEDB.12.0',
'Excel 8.0;HDR=YES;Database=C:\test\TEST.xls;'
,'SELECT * FROM [TblMenu$]')
GO
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 | '================================================================================================ Public Sub GetRecordsetThisworkbook(ByVal xSql As String, _ ByVal shtName As String, _ ByRef targetRange As Range) Dim oCn As Object Dim oRs As Object Dim wb As Workbook Set oCn = CreateObject("ADODB.Connection") With oCn .Provider = "Microsoft.ACE.OLEDB.12.0;" .ConnectionString = "Data Source=" & ThisWorkbook.FullName & _ ";Extended Properties=""Excel 12.0 Xml;"";" .ConnectionTimeout = 10 .Open End With Set oRs = CreateObject("ADODB.recordset") oRs.Open xSql, oCn, 1 ThisWorkbook.Sheets(shtName).Cells.ClearContents targetRange.CopyFromRecordset oRs ' Debug.Print oRs.RecordCount oRs.Close oCn.Close Set oRs = Nothing Set oCn = Nothing '## 여러개의 workbook 존재시 For Each wb In Application.Workbooks Debug.Print wb.Name If wb.Name = ThisWorkbook.Name And wb.ReadOnly Then Debug.Print wb.Name End If Next End Sub Sub Test() Dim xSql As String xSql = "SELECT LEFT(UBT, 5) AS UBT, SUM(IDT) AS IDT " & vbCrLf & _ " FROM [RAW$] " & vbCrLf & _ " WHERE IDT > 20000 GROUP BY LEFT(UBT, 5)" GetRecordsetThisworkbook xSql, "ret", ThisWorkbook.Worksheets("ret").Range("A1") End Sub |
이 포스팅은 쿠팡 파트너스 활동으로, 일정액의 커미션을 제공받고 있습니다.
