Excel COM add Table Topic is solved

[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

[Tigerlily]

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).

[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"

[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

[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