公司用門禁系統當考勤，資料需要人工比對 麻煩，需要幫助
- 帖子
- 1018
- 主題
- 15
- 精華
- 0
- 積分
- 1058
- 點名
- 0
- 作業系統
- win7 32bit
- 軟體版本
- Office 2016 64-bit
- 閱讀權限
- 50
- 性別
- 男
- 來自
- 桃園
- 註冊時間
- 2012-5-9
- 最後登錄
- 2022-9-28
|
本帖最後由 stillfish00 於 2013-5-21 20:25 編輯
還有另一種...從外部資料的Microsoft Query對工作表查詢
不過要稍微懂SQL語法，底下是錄製後修改的VBA 程式
Sub ExcelQuery()
Dim sSQL, driverID As Long
driverID = 1046 'Excel2010:1046 ; Excel2003:790
sSQL = Array("SELECT `Sheet4$`.員工編號, `Sheet4$`.姓名, `Sheet4$`.刷卡卡號, `Sheet4$`.部門, `Sheet4$`.職稱, `Sheet4$`.刷卡日期, " _
, "Min(`Sheet4$`.刷卡時間) AS 上班時間, Max(`Sheet4$`.刷卡時間) AS 下班時間" _
, " FROM `" & ThisWorkbook.FullName & "`.`Sheet4$` `Sheet4$`" _
, " GROUP BY `Sheet4$`.員工編號, `Sheet4$`.姓名, `Sheet4$`.刷卡卡號, `Sheet4$`.部門, `Sheet4$`.職稱, `Sheet4$`.刷卡日期" _
)
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_連線"
.Refresh BackgroundQuery:=False
'不保持連線
.Delete
End With
End Sub |
|
|
|
|
|
|
|
發表於 2013-5-21 20:22
| 