MsgBox “Error No: ” & Err.Number & “ Description: “ Set appWord = CreateObject(“Word.Application”) ‘Word is not running open Word with CreateObject StrSaveName = strDocType & ” ” & CStr(i) & _ĭebug.Print “Save name not used: ” & strSaveName ‘Create new save name with incremented number StrSaveNamePath = strDocsPath & strSaveNameĭebug.Print “Proposed save name and path: ” _ĭebug.Print “Save name already used: ” & strSaveName StrSaveName = strDocType & ” on ” & strShortDate & “.doc” StrDocType = doc.BuiltInDocumentProperties(2) ‘and append an incremented number to save name if found ‘Check for existence of previously saved document in documents folder, StrAddress = strAddress & vbCrLf & strCountry MsgBox “Can’t create label - no postal code!” MsgBox “Can’t create label - no street address!” ‘Write info from customer item to variables Set rst = dbs.OpenRecordset(strQuery, dbOpenDynaset) StrPrompt = “There are no customers in ” & strCountry _ If CreateAndTestQuery(strQuery, strSQL) = 0 Then StrSQL = “SELECT * FROM ” & strRecordSource & _ StrTemplatePath = GetProperty(strName:=”TemplatesPath”, strDefault:=””)
StrCountry = GetProperty(strName:=”Country”, strDefault:=””)
Set doc = docs.Add(strTemplateNameAndPath) StrPrompt = “Can’t find ” & strTemplate & ” in ” _ Set fil = fso.GetFile(strTemplateNameAndPath) ‘Check for existence of template in template folder, StrTemplateNameAndPath = strTemplatePath & strTemplate StrTemplatePath = GetProperty(strName:=”TemplatesPath”, _ StrDocsPath = GetProperty(strName:=”DocumentsPath”, _ Set appWord = GetObject(, “Word.Application”) ‘the error handler defaults to CreateObject ‘Set Word application variable if Word is not running, StrPrompt = “Please select a labels document” ‘Creates a Labels document, with one address per cellĭim fso As New Scripting.FileSystemObject
The cmdMerge_Click event procedure on frmWordLabelsTypeText creates a single Labels document from the selected template and fills its cells with name and address data, using a saved query: An Avery 5160 Labels document filled with customer address data
After selecting a Labels document and a country, clicking the Merge button creates a new Labels document and fills its cells with address data from tblCustomers, usin g the TypeText method to insert data from one record into one cell of the table, as shown in Figure B:įigure B. The frmWordLabelsTypeText form (shown in Figure A) uses an option group to select one of four countries for the mailing (each uses a saved query that filters for that country), and offers you a choice of two Avery mailing labels documents. A form for creating labels using TypeText The tblCustomers table has data on customers in many different countries if you frequently send out mailings to just to customers in certain countries, you can create filters for the countries that you send mailings to most frequently, and use the queries to send out mailings.įigure A. The sample database, Merging Data from a Recordset to Word (AA 168).mdb, has tables of data from the old Northwind sample database.
#Marging excel to word for labels how to
This article describes how to merge data from a recordset based on a query to Word documents. And you can create such a query on-the-fly, in VBA code.
#Marging excel to word for labels code
While the ItemsSelected collection of a listbox (which is used in Code Sample #24) is a very handy way to select records for a merge, sometimes it is more convenient to merge from a query, already set up to include all the records you want to merge. How to merge data from a recordset based on a query to Word documents.Īn AW reader wrote to me asking if the document properties merge code in my Code Sample #24 could be modified to merge data to Word from a query, instead of selections in a listbox.
0 kommentar(er)
