Excel Table paste into Word

Get help with using AutoHotkey and its commands and hotkeys
daiweisc
Posts: 31
Joined: 19 Oct 2019, 04:27

Excel Table paste into Word

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.
User avatar
flyingDman
Posts: 841
Joined: 29 Sep 2013, 19:01

Re: Excel Table paste into Word

07 Nov 2019, 01:07

Replace the line

ActiveDocument.Bookmarks(Bookmark).Range.PasteExcelTable

with:

wdApp.ActiveDocument.Bookmarks(Bookmark).Range.PasteExcelTable(0,1,1)
daiweisc
Posts: 31
Joined: 19 Oct 2019, 04:27

Re: Excel Table paste into Word

07 Nov 2019, 01:15

Code: Select all

wdApp.ActiveDocument.Bookmarks(Bookmark).Range.PasteExcelTable
the code run wrong.
User avatar
flyingDman
Posts: 841
Joined: 29 Sep 2013, 19:01

Re: Excel Table paste into Word

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.
daiweisc
Posts: 31
Joined: 19 Oct 2019, 04:27

Re: Excel Table paste into Word

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.
User avatar
flyingDman
Posts: 841
Joined: 29 Sep 2013, 19:01

Re: Excel Table paste into Word

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!                
daiweisc
Posts: 31
Joined: 19 Oct 2019, 04:27

Re: Excel Table paste into Word

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?
User avatar
flyingDman
Posts: 841
Joined: 29 Sep 2013, 19:01

Re: Excel Table paste into Word

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 

Return to “Ask For Help”

Who is online

Users browsing this forum: Bing [Bot], Google [Bot], itsnotaron, mikeyww, tatagi and 24 guests