¤½¥q¥Îªù¸T¨t²Î·í¦Ò¶Ô¡A¸ê®Æ»Ýn¤H¤u¤ñ¹ï ³Â·Ð¡A»ÝnÀ°§U
- ©«¤l
- 1018
- ¥DÃD
- 15
- ºëµØ
- 0
- ¿n¤À
- 1058
- ÂI¦W
- 0
- §@·~¨t²Î
- win7 32bit
- ³nÅ骩¥»
- Office 2016 64-bit
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ®ç¶é
- µù¥U®É¶¡
- 2012-5-9
- ³Ì«áµn¿ý
- 2022-9-28
|
¥»©«³Ì«á¥Ñ stillfish00 ©ó 2013-5-21 20:25 ½s¿è
ÁÙ¦³¥t¤@ºØ...±q¥~³¡¸ê®ÆªºMicrosoft Query¹ï¤u§@ªí¬d¸ß
¤£¹Lnµy·LÀ´SQL»yªk¡A©³¤U¬O¿ý»s«áק諸VBA µ{¦¡
Sub ExcelQuery()
Dim sSQL, driverID As Long
driverID = 1046 'Excel2010:1046 ; Excel2003:790
sSQL = Array("SELECT `Sheet4$`.û¤u½s¸¹, `Sheet4$`.©m¦W, `Sheet4$`.¨ê¥d¥d¸¹, `Sheet4$`.³¡ªù, `Sheet4$`.¾ºÙ, `Sheet4$`.¨ê¥d¤é´Á, " _
, "Min(`Sheet4$`.¨ê¥d®É¶¡) AS ¤W¯Z®É¶¡, Max(`Sheet4$`.¨ê¥d®É¶¡) AS ¤U¯Z®É¶¡" _
, " FROM `" & ThisWorkbook.FullName & "`.`Sheet4$` `Sheet4$`" _
, " GROUP BY `Sheet4$`.û¤u½s¸¹, `Sheet4$`.©m¦W, `Sheet4$`.¨ê¥d¥d¸¹, `Sheet4$`.³¡ªù, `Sheet4$`.¾ºÙ, `Sheet4$`.¨ê¥d¤é´Á" _
)
ActiveWorkbook.Worksheets.Add
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
"ODBC;DSN=Excel Files;DBQ=" & ThisWorkbook.FullName, _
";DefaultDir=" & ThisWorkbook.Path, _
";DriverId=" & driverID, _
";MaxBufferSize=2048;PageTimeout=5;") _
, Destination:=Range("$A$1")).QueryTable
.CommandText = Array(sSQL)
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "MS_QUERY_³s½u"
.Refresh BackgroundQuery:=False
'¤£«O«ù³s½u
.Delete
End With
End Sub |
|
|
|
|
|
|