Replace "Client" by the actual header of the columnĪlso like I mentioned in the comment below the question, why are you creating and destroying objects in the loop? You can instantiate the Word Application out of the For loop.Something like SQLStatement:="SELECT * FROM `Sheet1$` WHERE Client = " & Range("A" & rw + 1).Value & "'" Obviously this is untested as I do not know your header names and values SQLStatement:="SELECT * FROM `Sheet1$` WHERE SomeField = 'SomeUniqueValue'" = wdFormLettersĬonnection:="Data Source=" & strWorkbookName & " Mode=Read", _ StrWorkbookName = ThisWorkbook.Path & "\" & ThisWorkbook.Name Set wdocSource = wd.Documents.Open("C:\Users\Kamlesh\Desktop\master\Regen-booking.docx") Set wd = CreateObject("Word.Application")Ĭonst wdFormLetters = 0, wdOpenFormatAuto = 0Ĭonst wdSendToNewDocument = 0, wdDefaultFirstRecord = 1, wdDefaultLastRecord = -16 Newname = "Offer Letter - " & client & ".docx" I tried SQLStatement:="SELECT rw.row* FROMSheet1$ " But it does not workĬlient = Sheets("Sheet1").Cells(rw.Row + 1, 1).Value So can someone please tell me how to select the info from only the row where the iteration has reached. So what happens is that, each client's document includes data of other clients (excel rows) as well.Ģ) The usual automation error unless I keep the source word document open. So far the code works fine, but two problems I need to solve:ġ) SQLStatement:="SELECT * FROMSheet1$ " ends up mail merging info from all the rows in sheet during each iteration of the for loop (the loop iterates through each row). That is why I have to mailmerge each row info seperately. I am totally new to VBA and I am writing a code to mail merge data from each row in an excel sheet to a certain word document and save that document with name corresponding to the first cell value from each row.Įach row contains the information of a client.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |