Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mail Merge source Access Database using Excel VBA... Pls Anyone
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... Pls Anyone
Are you asking a question? I saw this posting yesterday and thought you were just showing results of your macro! The title of you request said you wanted to order by date so here is an example of a SQL to retrieve a record set in order SELECT Submissions.Task_ID, Submissions.`Client Name`, Submissions.`Effective Date`, Submissions.`Imp Mgr`, Submissions.`Due Date`, Submissions.`Actual Date`, Submissions.`Date Difference` FROM `C:\temp\submission`.Submissions Submissions ORDER BY Submissions.`Due Date` What I recommend to help you get the syntax correct is to perfrom a database query manually. The Query wizard allows you to manually set up a SQL. Go to worksheet menu Data - Import External Data - Import Data - New Database query. Selct the database. Then in first menu select the column(s) you need. The 2nd menu allows you to add any filtering. The 3rd menu allows you to select the Sort field(s). Then in the 4th menu select Edit Query radio button and press Finish. The query editor will appear. Press the SQL button and copy the SQL. You can edit the SQL as required but it will give you the syntax for the SQL. -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147644 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mail Merge source Access Database using Excel VBA... Pls Anyone
Thanks joel,
I will try as said below, if any problem comes i will get back to you soon. On Oct 25, 3:30*pm, joel wrote: Are you asking a question? *I saw this posting yesterday and thought you were just showing results of your macro! The title of you request said you wanted to order by date so here is an example of a SQL to retrieve a record set in order SELECT Submissions.Task_ID, Submissions.`Client Name`, Submissions.`Effective Date`, Submissions.`Imp Mgr`, Submissions.`Due Date`, Submissions.`Actual Date`, Submissions.`Date Difference` FROM `C:\temp\submission`.Submissions Submissions ORDER BY Submissions.`Due Date` What I recommend to help you get the syntax correct is to perfrom a database query manually. *The Query wizard allows you to manually set up a SQL. Go to worksheet menu Data - Import External Data - Import Data - New Database query. * Selct the database. *Then in first menu select the column(s) you need. The 2nd menu allows you to add any filtering. *The 3rd menu allows you to select the Sort field(s). *Then in the 4th menu select Edit Query radio button and press Finish. *The query editor will appear. *Press the SQL button and copy the SQL. *You can *edit the SQL as required but it will give you the syntax for the SQL. -- joel ------------------------------------------------------------------------ joel's Profile:http://www.thecodecage.com/forumz/member.php?userid=229 View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=147644 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mail Merge source Access Database using Excel VBA... Pls Anyone
Hi Joel,
i am not getting the result. Assume I have 10 results in my listbox ( excel userform ). I have selected only one item from the listbox. When click merge, it merge for the results ( 100 records - which is stored in Access Database) How do i limit to selected records ? On Oct 25, 8:29*pm, "fi.or.jp.de" wrote: Thanks joel, I will try as said below, if any problem comes i will get back to you soon. On Oct 25, 3:30*pm, joel wrote: Are you asking a question? *I saw this posting yesterday and thought you were just showing results of your macro! The title of you request said you wanted to order by date so here is an example of a SQL to retrieve a record set in order SELECT Submissions.Task_ID, Submissions.`Client Name`, Submissions.`Effective Date`, Submissions.`Imp Mgr`, Submissions.`Due Date`, Submissions.`Actual Date`, Submissions.`Date Difference` FROM `C:\temp\submission`.Submissions Submissions ORDER BY Submissions.`Due Date` What I recommend to help you get the syntax correct is to perfrom a database query manually. *The Query wizard allows you to manually set up a SQL. Go to worksheet menu Data - Import External Data - Import Data - New Database query. * Selct the database. *Then in first menu select the column(s) you need.. The 2nd menu allows you to add any filtering. *The 3rd menu allows you to select the Sort field(s). *Then in the 4th menu select Edit Query radio button and press Finish. *The query editor will appear. *Press the SQL button and copy the SQL. *You can *edit the SQL as required but it will give you the syntax for the SQL. -- joel ------------------------------------------------------------------------ joel's Profile:http://www.thecodecage.com/forumz/member.php?userid=229 View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=147644 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mail Merge source Access Database using Excel VBA... Pls Anyone
You need a where clause (A filter) in the SQL. The Query wizard allows allows you to add a filter. I beliee its the 3rd window in the wizard. the Where need to be aon a seperate line like this SELECT Submissions.Task_ID, Submissions.`Client Name`, Submissions.`Effective Date`, Submissions.`Imp Mgr`, Submissions.`Due Date`, Submissions.`Actual Date`, Submissions.`Date Difference` FROM `C:\temp\submission`.Submissions Submissions WHERE (Submissions.`Client Name`='Test') ORDER BY Submissions.`Effective Date` -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147644 Microsoft Office Help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mail Merge source Access Database using Excel VBA | Excel Programming | |||
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 | |||
Mail Merge from Excel Data Source to Word | Excel Programming |