sql - Selecting only one row from a excel sheet (as part of mail merge) -

-

i totally new vba , writing code mail merge data each row in excel sheet word document , save document name corresponding first cell value each row.

each row contains information of client. why have mailmerge each row info seperately.

so far code works fine, 2 problems need solve:

1) sqlstatement:="select * fromsheet1$" ends mail merging info rows in sheet during each iteration of loop (the loop iterates through each row). happens that, each client's document includes data of other clients (excel rows) well.

2) usual automation error unless keep source word document open.

so can please tell me how select info row iteration has reached.

i triedsqlstatement:="select rw.row* fromsheet1$" not work

any good. full code is:

sub runmerge()  'booking document begins here  dim wd object dim wdocsource object dim activedoc dim strworkbookname string dim x integer dim cdir string dim client string  dim sh worksheet dim rw range dim rowcount integer  set sh = activesheet each rw in sh.rows     if sh.cells(rw.row, 1).value = ""         exit     end if    cdir = "c:\users\kamlesh\desktop\" client = sheets("sheet1").cells(rw.row + 1, 1).value dim newname string newname = "offer letter - " & client & ".docx"   on error resume next set wd = getobject(, "word.application") if wd nothing     set wd = createobject("word.application") end if on error goto 0 const wdformletters = 0, wdopenformatauto = 0 const wdsendtonewdocument = 0, wddefaultfirstrecord = 1, wddefaultlastrecord = -16  set wdocsource = wd.documents.open("c:\users\kamlesh\desktop\master\regen-booking.docx")  strworkbookname = thisworkbook.path & "\" & thisworkbook.name  wdocsource.mailmerge.maindocumenttype = wdformletters  wdocsource.mailmerge.opendatasource _         name:=strworkbookname, _         addtorecentfiles:=false, _         revert:=false, _         format:=wdopenformatauto, _         connection:="data source=" & strworkbookname & ";mode=read", _         sqlstatement:="select * `sheet1$`"  wdocsource.mailmerge     .destination = wdsendtonewdocument     .suppressblanklines = true     .datasource         .firstrecord = wddefaultfirstrecord         .lastrecord = wddefaultlastrecord     end     .execute pause:=false end  wd.visible = true wd.activedocument.saveas cdir + newname 'wdocsource.close savechanges:=false 'wd.quit set wdocsource = nothing set wd = nothing   next rw  end sub

my excel sheet looks this

enter image description here

try this. untested not know header names , values

sqlstatement:="select * `sheet1$` somefield = 'someuniquevalue'"

something like

sqlstatement:="select * `sheet1$` client = " & range("a" & rw + 1).value & "'"
  1. replace "a" actual column
  2. replace "client" actual header of column

also mentioned in comment below question, why creating , destroying objects in loop? can instantiate word application out of for loop. , can destroy out of for loop.

is trying? (untested)

change ssql = "select * fromsheet1$where [client name] = '" & .range("a" & i).value & "'" in below code per requirements.

const wdformletters = 0, wdopenformatauto = 0 const wdsendtonewdocument = 0, wddefaultfirstrecord = 1, wddefaultlastrecord = -16  sub runmerge()     dim wd object, wdocsource object     dim sh worksheet     dim lrow long, long     dim cdir string, client string, newname string     dim ssql string      cdir = "c:\users\kamlesh\desktop\"      on error resume next     set wd = getobject(, "word.application")     if wd nothing         set wd = createobject("word.application")     end if     on error goto 0      set wdocsource = wd.documents.open(cdir & "\master\regen-booking.docx")     set sh = activesheet     strworkbookname = thisworkbook.path & "\" & thisworkbook.name      sh         lrow = .range("a" & .rows.count).end(xlup).row          = 2 lrow             if len(trim(.range("a" & i).value)) <> 0                 client = .cells(i, 1).value                 newname = "offer letter - " & client & ".docx"                  wdocsource.mailmerge.maindocumenttype = wdformletters                  '~~> sample string                 ssql = "select * `sheet1$` [client name] = '" & .range("a" & i).value & "'"                  wdocsource.mailmerge.opendatasource name:=strworkbookname, _                 addtorecentfiles:=false, revert:=false, format:=wdopenformatauto, _                 connection:="data source=" & strworkbookname & ";mode=read", _                 sqlstatement:=ssql                  wdocsource.mailmerge                     .destination = wdsendtonewdocument                     .suppressblanklines = true                     .datasource                         .firstrecord = wddefaultfirstrecord                         .lastrecord = wddefaultlastrecord                     end                     .execute pause:=false                 end                  wd.activedocument.saveas cdir & newname                 wd.activedocument.close savechanges:=false             end if         next     end      wdocsource.close savechanges:=false     wd.quit      set wdocsource = nothing     set wd = nothing end sub

Comments

Post a Comment

Popular posts from this blog

Spring Boot + JPA + Hibernate: Unable to locate persister -

-
i trying work entity inside request method of spring boot application. entity in project , pom.xml has dependencies it. why error message?: org.hibernate.unknownentitytypeexception: unable locate persister: com.ric.bill.model.bs.par package com.ric.web; import java.util.concurrent.atomic.atomiclong; import javax.persistence.entitymanager; import javax.persistence.persistencecontext; import net.sf.ehcache.cachemanager; import net.sf.ehcache.management.cache; import net.sf.ehcache.management.cachestatistics; import org.hibernate.stat.statistics; import org.springframework.beans.factory.annotation.autowired; import org.springframework.boot.autoconfigure.domain.entityscan; import org.springframework.cache.annotation.enablecaching; import org.springframework.transaction.annotation.enabletransactionmanagement; import org.springframework.transaction.annotation.propagation; import org.springframework.transaction.annotation.transactional; import org.springframework.web.bind.annotati...
Read more

go - Golang: panic: runtime error: invalid memory address or nil pointer dereference using bufio.Scanner -

-
i implementing go program uses bufio.scanner , bufio.writer have packaged code follows package main import ( "fmt" "player/command" "strings" ) func main() { //enter code here. read input stdin. print output stdout commands.scanner.scan() { //scan new line , send comand variable check command exist or not input := strings.split(strings.trim(commands.scanner.text(), " "), " ") command := input[0] if command == "" { fmt.printf("$ %s:", commands.pwd) continue } if !commands.commands[command] { commands.throwerror("cannot recognize input.") } else { commands.execute(command, input[1:], nil) } fmt.printf("$ %s:", commands.pwd) } } i using init.go file in main package follows package main import ( "flag" "player/sourc...
Read more

c - double free or corruption (fasttop) -

-
this code reads null terminated string file (file containing null terminated strings & should provide index wanted string). error: *** error in `./main': double free or corruption (fasttop): 0x090a4a80 *** aborted code: char *tmp_realloc=null; char *sstring=null; int i=1; /*set file pointer point string*/ fseek(fh,index,seek_set); sstring=(char*)malloc(sizeof(char)); if(sstring == null) return null; tmp_realloc=sstring; while( (*(sstring+i-1)=(char)fgetc((file*)fh)) != 0x0 ) { /*reallocate more memory*/ tmp_realloc=(char*)realloc((char*)sstring,++i); /*if not same address copy old new & set old point new*/ if(tmp_realloc != sstring){ strcpy((char*)tmp_realloc,(char*)sstring); free(sstring); sstring=tmp_realloc; } } could tell me pointer causing issue (& reason) ? remove part /*if not same address copy old new & set old point new*/ if(tmp_realloc != sstring){ strc...
Read more