vba - Copying multiple ranges from excel to word and controlling linespacing -


i manage copy range excel newly opened word document , control line spacing (thanks copy range excel word - set paragraph spacing zero).

however, not manage control linespacing when copy multiple ranges multiple bookmarks in opened , existing word file (document.docx). code can found below @ end of post.

this code works excel file multiple sheets. 1 sheet configuration sheet. contains name of excel sheet containing table (in range "name") , links bookmark name in word (in range bookmarkexcel")".

i suppose problem piece of code:

set wdtable = mydoc.tables(mydoc.tables.count) wdtable.range.paragraphformat.spaceafter = 0

i tried sorts of variations (e.g. replacing mydoc.tables.count rep, 1, ...) didn't manage control linespacing. did wrong?

edit: i found cause: document contains tables (before , after ones copy , paste) causes code line spacing not work. thus, how can adapt code such works documents contain tables?

sub exceltablestoword()  dim tbl             range dim wordapp         word.application dim mydoc           word.document dim wordtable       word.table  sheets("configuration").select n = activesheet.usedrange.rows.count  set listtables = range("name") set listexcelbookmarks = range("bookmarkexcel")   set wordapp = getobject(class:="word.application") wordapp.visible = true set mydoc = wordapp.documents("document.docx")  rep = 2 n          sheetname = listtables.cells(rep, 1).value          on error resume next         set existing = sheets(sheetname)         existing.select 'added          lastcolumn = activesheet.usedrange.columns.count         lastrow = activesheet.usedrange.rows.count      if listexcelbookmarks.cells(rep, 1).value <> ""          set tbl = range(cells(1, 1), cells(lastrow, lastcolumn))         tbl.copy          mydoc.bookmarks(listexcelbookmarks.cells(rep, 1).value).range.pasteexceltable _             linkedtoexcel:=false, _             wordformatting:=false, _             rtf:=false          dim wdtable table          set wdtable = mydoc.tables(mydoc.tables.count)         wdtable.range.paragraphformat.spaceafter = 0      end if next rep end sub

count tables current bookmark , add 1 newly added table index

here's code above , other (hopefully) useful refactoring:

option explicit  sub exceltablestoword()     dim wordapp             word.application     dim mydoc               word.document     dim wdtable table      dim rep                 long     dim listtables          range     dim listexcelbookmarks  range     dim ws                  worksheet     dim tabname             string      set wordapp = getobject(class:="word.application")     wordapp.visible = true     set mydoc = wordapp.documents("document.docx")      worksheets("configuration")         set listtables = .range("name")         set listexcelbookmarks = .range("bookmarkexcel")     end      rep = 2 listexcelbookmarks.rows.count  '<--| loop through bookmarks range, skipping first row         if listexcelbookmarks.cells(rep, 1).value <> ""             tabname = listtables.cells(rep, 1).value             if getsheet(tabname, ws) '<-- getsheet() returns 'true' if worksheet named after 'tabname' found , sets 'ws' it. otherwise returns 'false'                 ws.usedrange.copy                 mydoc                     .bookmarks(tabname).range.pasteexceltable _                                                                         linkedtoexcel:=false, _                                                                         wordformatting:=false, _                                                                         rtf:=false                     set wdtable = .tables(.range(.range.start, .bookmarks(tabname).range.end).tables.count + 1) '<--| add 1 tables before current bookmark newly added 1 right after                     wdtable.range.paragraphformat.spaceafter = 0                 end             end if         end if     next rep end sub  function getsheet(shtname string, ws worksheet) boolean     on error resume next     set ws = worksheets(shtname)     getsheet = not ws nothing end function

Comments

Post a Comment

Popular posts from this blog

aws api gateway - SerializationException in posting new Records via Dynamodb Proxy Service in API -

i getting "__type": "com.amazon.coral.service#serializationexception" as reply in postman & in test console in api gateway trying post record directly dynamodb using api proxy services.. referring aws article - https://aws.amazon.com/blogs/compute/using-amazon-api-gateway-as-a-proxy-for-dynamodb/ here's mapping { "tablename": "tablenamegoeshere", "item": { "id" : "$context.requestid" "eventname" : "$input.path('$.eventname')", "timestamp" : $input.path('$.timestamp'), "answers": "$util.parsejson($input.path('$.answers'))" } } update: did asked ... , worked if try add array of json objects gives me above same error - here's trying now. please - couldnt find on google well #set($inputroot = $input.path('$')) { "tablename": "answer", "item": {
Read more

asp.net - Problems sending emails from forum -

i have been redesigning site has message board, has been written guy looked after site. it has few problems , written in .asp , uses .mdb files database. you can see whole thing here . feel free post if helps, test thing have set using original files , new database. the basic problem when posts message, supposed email sent them, have posted it. when replies, originator supposed message, replier has been posted. i’d have thought @ point in process there should script/file in postmessage.asp? the postmessage.asp page below. <% option explicit %> <!-- #include virtual="common/adovbs.inc" --> <html> <head> <meta http-equiv="content-type" content="text/html; charset=windows-1252"> <meta name="generator" content="microsoft frontpage 4.0"> <meta name="progid" content="frontpage.editor.document"> <title>new page 2</title> <base target="_self"
Read more