시트단위로 값을 가지고 오는것이 흠이기는 하지만 나름 꽤 쓸만한 기능중 하나라고 봅니다.
'// Excel파일을 열지않고 시트로 불러옴 Sub ET(FullPath As String) Sheets("QueryTables").Cells.Delete Shift:=xlUp Dim Source As String Source = Source & "OLEDB;" Source = Source & "Provider=Microsoft.ACE.OLEDB.12.0;" ' Source = Source & "Password="""";" ' Source = Source & "User ID=Admin;" Source = Source & "Data Source=" & FullPath & ";" ' Source = Source & "Mode=Share Deny Write;" ' Source = Source & "Extended Properties=""HDR=YES;"";" ' Source = Source & "Jet OLEDB:System database="""";" ' Source = Source & "Jet OLEDB:Registry Path="""";" ' Source = Source & "Jet OLEDB:Database Password="""";" Source = Source & "Jet OLEDB:Engine Type=37;" ' Source = Source & "Jet OLEDB:Database Locking Mode=0;" ' Source = Source & "Jet OLEDB:Global Partial Bulk Ops=2;" ' Source = Source & "Jet OLEDB:Global Bulk Transactions=1;" ' Source = Source & "Jet OLEDB:New Database Password="""";" ' Source = Source & "Jet OLEDB:Create System Database=False;" ' Source = Source & "Jet OLEDB:Encrypt Database=False;" ' Source = Source & "Jet OLEDB:Don't Copy Locale on Compact=False;" ' Source = Source & "Jet OLEDB:Compact Without Replica Repair=False;" ' Source = Source & "Jet OLEDB:SFP=False;" ' Source = Source & "Jet OLEDB:Support Complex Data=False" With Sheets("QueryTables").ListObjects.Add(SourceType:=0, Source:=Source, Destination:=Sheets("QueryTables").Range("$A$1")).QueryTable .CommandType = xlCmdTable '// .CommandText = Array("Sheet1$") '// ' .RowNumbers = False ' .FillAdjacentFormulas = False ' .PreserveFormatting = True ' .RefreshOnFileOpen = False ' .BackgroundQuery = True ' .RefreshStyle = xlInsertDeleteCells ' .SavePassword = False ' .SaveData = True ' .AdjustColumnWidth = True ' .RefreshPeriod = 0 ' .PreserveColumnInfo = True .SourceDataFile = FullPath '// ' .ListObject.DisplayName = "표" .Refresh BackgroundQuery:=False '// End With End Sub |
'Excel & vba' 카테고리의 다른 글
[vba] Page Up/Page Down 버튼을 이용한 시트이동 단축키 (0) | 2016.01.08 |
---|
이 포스팅은 쿠팡 파트너스 활동으로, 일정액의 커미션을 제공받고 있습니다.