Page 1 of 1
Excel COM add Table Topic is solved
Posted: 22 Jan 2019, 02:33
by euras
Can someone convert this marco into AHK code? I want to add Table, but don't know how to convert this marco :/
Code: Select all
Columns("A:D").Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A:$D"), , xlYes).Name = _
"Table4"
Columns("A:D").Select
ActiveSheet.ListObjects("Table4").TableStyle = "TableStyleMedium2"
I have tried to convert to this code, but I get an error on Add part. It says that parameters are incorrect.
Code: Select all
XL.Columns("A:D").Select
XL.ActiveSheet.ListObjects.Add(1,"$A:$D",, 1, "Table4")
XL.Columns("A:D").Select
XL.ActiveSheet.ListObjects("Table4").TableStyle := "TableStyleMedium2"
I'm trying to pass parameters from here:
https://docs.microsoft.com/en-us/office/vba/api/excel.listobjects.add
SOLVED:
the code is:
Code: Select all
XL.Range("A:D").Select
XL.ActiveSheet.ListObjects.Add(1, XL.Range("$A:$D"),_, 1).Name := "MyTestTable2"
XL.Range("MyTestTable2[[#All]]").Select
XL.ActiveSheet.ListObjects("MyTestTable2").TableStyle := "TableStyleMedium2"
XL.Range("A1").Select
Re: Excel COM add Table
Posted: 22 Jan 2019, 07:26
by Tigerlily
euras wrote: ↑22 Jan 2019, 02:33
SOLVED:
the code is:
Code: Select all
XL.Range("A:D").Select
XL.ActiveSheet.ListObjects.Add(1, XL.Range("$A:$D"),_, 1).Name := "MyTestTable2"
XL.Range("MyTestTable2[[#All]]").Select
XL.ActiveSheet.ListObjects("MyTestTable2").TableStyle := "TableStyleMedium2"
XL.Range("A1").Select
Ah yes, I struggled with this for some time myself.
Just a heads up, you don't need to select the area before turning it into a table to turn it into a table.
There are many cases where you don't need the
_ there, sometimes Excel needs a random variable.. you could write somethign random there besides the _, like
alksdjflasjerlkaj3o4ijlks
If you do plan on using Selecting a variable range, you could also use
Xl.Selection in place of
Xl.Range(variablerange).
Re: Excel COM add Table
Posted: 04 Dec 2019, 22:22
by daiweisc
According the above code, My codes can not run well:
Code: Select all
#SingleInstance,Force
objExcel := ComObjCreate("Excel.Application")
objExcel.Application.Visible := False
objExcel.DisplayAlerts := False
Global objExcel
ExcelStyles(WorkBook,Range1){
FilePath := "D:\AccessExport\" WorkBook ".xlsx"
objWorkBook := objExcel.Workbooks.Open(FilePath)
SheetName := SubStr(WorkBook,3)
objExcel.Sheets(SheetName).Range(Range1).Select
objExcel.Selection.Font.Name := "等线"
objExcel.Selection.Font.Size := 14
objExcel.Selection.Columns.AutoFit
objExcel.Selection.HorizontalAlignment := -4108
objExcel.Selection.VerticalAlignment := -4108
objExcel.ActiveSheet.ListObjects.Add(1, Range(Range1),, 1).Name := "MyTable"
objExcel.Range("MyTable[#All]").Select
objExcel.ActiveSheet.ListObjects("MyTable").TableStyle := "TableStyleLight1"
objWorkBook.Close(1)
}
WorkBook := "11iqr1BL2"
Range1 := "A1:K8"
ExcelStyles(WorkBook,Range1)
objExcel.Quit()
objExcel := ""
ExitApp
It is confuse with me,Can you help me?
The tips show me the wronge:
Code: Select all
objExcel.ActiveSheet.ListObjects.Add(1, Range(Range1),, 1).Name := "MyTable"
Re: Excel COM add Table
Posted: 23 Apr 2020, 08:01
by DRocks
Thanks for this it works well and I could even simplify it like so:
Code: Select all
;xl.app.ActiveSheet.UsedRange.Select
xl.app.ActiveSheet.ListObjects.Add(1, xl.app.ActiveSheet.UsedRange,_, 1).Name := "myTable"
;xl.app.Range("MyTestTable2[[#All]]").Select
xl.app.ActiveSheet.ListObjects("myTable").TableStyle := "TableStyleMedium1"
;xl.app.Range("A1").Select
Re: Excel COM add Table
Posted: 12 Jun 2020, 02:08
by daiweisc
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