Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mail Merge source Access Database using Excel VBA
Hi All,
I am working on mail merge. I have access database where all the customer information are stored. I have one excel file, I have created userform. This will shows the customer information based on some criteria in listbox. User needs to select the listbox item results, may be 1 result or 10 result ( my database contains more than 500 customer information ) If the user clicks on mail merge ( in excel userform ) this perform some action. like opening word doc ( source document ) & statring merging with selected listbox item. Eg.. user selects 2 listbox items. I need mail merged letters for those clients not for all the clients ( 500 customer available in access database ). I am using the below code, which pulls out all the details not the selected listbox items. With UserForm1.ListBox1 For i = 0 To .ListCount - 1 If .Selected(i) Then litem = .List(i) Dbase = "D:\share.accdb" Ssql = "Select * FROM [" & TableName & "] " Ssql = Ssql & " WHERE ID = " & litem objword.Application.Visible = True Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Dbase & ";" Set rs = New ADODB.Recordset rs.Open Ssql, cn, adOpenStatic, adLockBatchOptimistic objword.MailMerge.Execute End If Next i End With |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mail Merge source Access Database using Excel VBA
your code loomks fine ... are you running it from the userform?
The only uissue might be the resulting ssQL string. Usually, in T-SQL text is enclosed in single quotes eh " WHERE [ID] = 'ABC' " If this is not the case SQL would raise an error - maybe Access doesn't? So try addign the single quote mark Ssql = Ssql & " WHERE ID = '" & litem & "'" also, did you step through it? Put a break point at the start of the loop - on the WITH line, then press F8 to see if it correctly goes through th eIF statements It would be faster too, if you set the connection before the loop. once the connection is open, you can llop your reciordsets as you do. "fi.or.jp.de" wrote: Hi All, I am working on mail merge. I have access database where all the customer information are stored. I have one excel file, I have created userform. This will shows the customer information based on some criteria in listbox. User needs to select the listbox item results, may be 1 result or 10 result ( my database contains more than 500 customer information ) If the user clicks on mail merge ( in excel userform ) this perform some action. like opening word doc ( source document ) & statring merging with selected listbox item. Eg.. user selects 2 listbox items. I need mail merged letters for those clients not for all the clients ( 500 customer available in access database ). I am using the below code, which pulls out all the details not the selected listbox items. With UserForm1.ListBox1 For i = 0 To .ListCount - 1 If .Selected(i) Then litem = .List(i) Dbase = "D:\share.accdb" Ssql = "Select * FROM [" & TableName & "] " Ssql = Ssql & " WHERE ID = " & litem objword.Application.Visible = True Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Dbase & ";" Set rs = New ADODB.Recordset rs.Open Ssql, cn, adOpenStatic, adLockBatchOptimistic objword.MailMerge.Execute End If Next i End With . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advanced Mail merge invoice from excel database | Excel Discussion (Misc queries) | |||
problems using Excel as a mail merge database in Office XP | Excel Worksheet Functions | |||
mail merge with Excel data source | Excel Worksheet Functions | |||
how do i get my mail merge to update the data source at each merge | Excel Discussion (Misc queries) | |||
Mail Merge from Excel Data Source to Word | Excel Programming |