Thanks the above reply, my code run well
Code: Select all
#NoEnv
#SingleInstance,Force
dmb := ComObjCreate("Access.Application") ;Create new instance of Microsoft dmbess.
Ac :="F:\华鹤煤化\生产管理部\工艺管理\技术委员会会议纪要\技术委员会台账.accdb"
FileName := "F:\华鹤煤化\生产管理部\工艺管理\技术委员会会议纪要\技术委员会台账3.xlsx"
dmb.OpenCurrentDatabase(Ac)
xl := ComObjCreate("Excel.Application")
;xl.DisplayAlerts := False
wrkbk :=xl.Workbooks.Open(FileName) ;需要粘贴的目标工作薄
Global dmb,xl,wrkbk
Global Number := 1
;-------------------------------------------------------------------------------------------
AccessCopy(Query,FileName,Sheet){
Clipboard := ""
dmb.DoCmd.OpenQuery(Query)
dmb.DoCmd.RunCommand(109) ; select all records
dmb.DoCmd.RunCommand(190) ; copy
wrkbk.WorkSheets(Sheet).UsedRange.Clear
;MsgBox %Clipboard%
wrkbk.WorkSheets(Sheet).Range("A1").PasteSpecial(-4163) ; or Xl.Range("A1").PasteSpecial(-4163)
wrkbk.WorkSheets(Sheet).Activate
xl.ActiveSheet.UsedRange.Select
;xl.ActiveSheet.UsedRange.Select
xl.Selection.Font.Name := "宋体"
xl.Selection.Font.Size := 14
xl.Selection.Columns.AutoFit
xl.Selection.Rows.AutoFit
myTable := "_表" Number
;MsgBox,%myTable%
xl.ActiveSheet.ListObjects.Add(1, xl.Selection,, 1).Name := myTable
;xl.Selection..Range("myTable[[#All]]").Select
xl.ActiveSheet.ListObjects(myTable).TableStyle := "TableStyleLight1"
;xl.Selection.Range("A1").Select
Number += 1
}
Query := "cxGYGB"
Sheet := "公用工程部"
FileName := "F:\华鹤煤化\生产管理部\工艺管理\技术委员会会议纪要\技术委员会台账3.xlsx"
AccessCopy(Query,FileName,Sheet)
Query := "cxQHB"
Sheet := "气化部"
AccessCopy(Query,FileName,Sheet)
Query := "cxASCB"
Sheet := "氨生产部"
AccessCopy(Query,FileName,Sheet)
uery := "cxNSSB"
Sheet := "尿素生产部"
AccessCopy(Query,FileName,Sheet)
Query := "cxSCBX"
Sheet := "生产保运中心"
AccessCopy(Query,FileName,Sheet)
Query := "cxFXHX"
Sheet := "分析化验中心"
AccessCopy(Query,FileName,Sheet)
Query := "cxCCWX"
Sheet := "仓储物流中心"
AccessCopy(Query,FileName,Sheet)
Query := "cxJAB"
Sheet := "健安部"
AccessCopy(Query,FileName,Sheet)
;-------------------------------------------------------------------------------------------------
wrkbk.Close(1)
xl.Quit()
xl := ""
dmb.CloseCurrentDatabase
dmb.quit()
dmb := ""
msgbox,,,done,3
ExitApp