AutoHotkey Community

Posted: **06 Nov 2019, 23:42**

I have a task to Paste the Excel table into Word,the code is

#SingleInstance,Force
ExcelToWord(ExcelPath,Sheet,oRange,Bookmark){
appExcel := ComObjCreate("Excel.Application")
xl :=appExcel.Workbooks.Open(ExcelPath)
xl.Sheets(Sheet).Range(oRange).Copy
WordPath := "F:\华鹤煤化\生产管理部\生产管理部台帐\华鹤公司工艺月报.docx"
wdApp := ComObjCreate("Word.Application")
wdApp.Visible := False
wdApp.DisplayAlerts := -1
oWord := wdApp.Documents.Open(WordPath)
ActiveDocument.Bookmarks(Bookmark).Range.PasteExcelTable
xl.Close(0)
appExcel.Quit()
appExcel := ""
oWord.Save
oWord.Close()
oWord.quit()
oWord := ""
}
ExcelPath := "F:\华鹤煤化\生产管理部\生产管理部台帐\2019年台账\2019产量能耗统计.xlsx"
Sheet := "产量"
oRange := "A2:D10"
Bookmark := "产量完成情况表格"
ExcelToWord(ExcelPath,Sheet,oRange,Bookmark)

But the code run with no result and show the error "quit is already". Who can help me to handle this.

Posted: **07 Nov 2019, 01:07**

Replace the line

ActiveDocument.Bookmarks(Bookmark).Range.PasteExcelTable

with:

wdApp.ActiveDocument.Bookmarks(Bookmark).Range.PasteExcelTable(0,1,1)

Posted: **07 Nov 2019, 01:15**

Code: Select all

wdApp.ActiveDocument.Bookmarks(Bookmark).Range.PasteExcelTable

the code run wrong.

Posted: **07 Nov 2019, 01:25**

Code: Select all

wdApp.ActiveDocument.Bookmarks(Bookmark).Range.PasteExcelTable(0,1,1)

Note the (0,1,1) at the end. When there is an error, please report the exact error code.

Posted: **07 Nov 2019, 05:32**

Thanks,the code runs with no error, but I set the table Font and Style dosn't work:

Code: Select all

#SingleInstance,Force
ExcelToWord(ExcelPath,Sheet,oRange,Bookmark){
	appExcel := ComObjCreate("Excel.Application")
	xl :=appExcel.Workbooks.Open(ExcelPath)
	xl.Sheets(Sheet).Range(oRange).Copy
	WordPath := "D:\华鹤公司工艺月报.docx"
	wdApp := ComObjCreate("Word.Application")
	wdApp.Visible := False
	wdApp.DisplayAlerts := -1
	oWord := wdApp.Documents.Open(WordPath) 
	Table := wdApp.ActiveDocument.Bookmarks(Bookmark).Range
	tb := Table.PasteExcelTable(0,0,1)
	tb.Style := "无格式表格1"
	tb.Font.Name := "宋体"
	tb.Font.Size := "四号"
	tb.Rows.Alignment := 1
	xl.Close(0)
	appExcel.Quit()
	appExcel := ""
	oWord.Save
	oWord.Application.quit
	oWord := ""
}
ExcelPath := "D:\AccessExport\43日均产量.xlsx"
Sheet := "日均产量"
oRange := "A1:C8"
Bookmark := "产量完成情况表格"
ExcelToWord(ExcelPath,Sheet,oRange,Bookmark)

ExitApp

can you help me find the reason.

Posted: **07 Nov 2019, 10:56**

Your new code includes many errors. You can use wdApp.ActiveDocument.Bookmarks(Bookmark).Range.PasteExcelTable(0,1,1) or wdApp.ActiveDocument.Bookmarks(Bookmark).Range.PasteExcelTable(0,0,1). When you use ...(0,1,1) the formatting of the Word document will be preserved. When you use ...(0,0,1) the formatting of the Excel range will be applied to the table in the Word document. If you want to apply specific formatting to the range to be copied, I recommend formatting the Excel range first rather than formatting the table in the Word document.
This is how to format ranges in Excel (use this before the "copy" command):

Code: Select all

Xl.activesheet.Range(oRange).style := "Normal"
Xl.activesheet.Range(oRange).font.size := 20
Xl.activesheet.Range(oRange).font.name := "Arial"
Xl.activesheet.Range(oRange).HorizontalAlignment := -4108

Delete this, it will not work:

Code: Select all

Table := wdApp.ActiveDocument.Bookmarks(Bookmark).Range
tb := Table.PasteExcelTable(0,0,1)
tb.Style := "无格式表格1"
tb.Font.Name := "宋体"
tb.Font.Size := "四号"
tb.Rows.Alignment := 1

replace it with:

Code: Select all

wdApp.ActiveDocument.Bookmarks(Bookmark).Range.PasteExcelTable(0,0,1)         ;note: nothing can be added to this!

Posted: **07 Nov 2019, 21:00**

Thanks your reply.
But the PasteExcelTable is left Alignment in word.
How to make the table/Range Horizontal Alignment Center in word?

Posted: **07 Nov 2019, 23:49**

To center the last added table in the Word document you can use:

Code: Select all

count := wdApp.ActiveDocument.Tables.Count
wdApp.selection.tables(count).Rows.Alignment := 1               ;Center:1; Left:0; Right:2

To center all tables in the document:

Code: Select all

count := wdApp.ActiveDocument.Tables.Count
loop, % count
	wdApp.selection.tables(a_index).Rows.Alignment := 1               ;Center:1; Left:0; Right:2

AutoHotkey Community

Excel Table paste into Word

Excel Table paste into Word

Re: Excel Table paste into Word

Re: Excel Table paste into Word

Re: Excel Table paste into Word

Re: Excel Table paste into Word

Re: Excel Table paste into Word

Re: Excel Table paste into Word

Re: Excel Table paste into Word