Merging Microsoft Access data with Quark Publications |
|||
| by Chris Rowlett and Richard Moore 21st Century Work Place, Inc. The Design-Syndicate Dallas, TX 75254 214.435.4703 http://www.tfwpa.com/ |
|||
| More about: Microsoft Access, Quark, Excel Import and Export
|
|||
| The purpose of this project was to take an existing Microsoft Access database and export certain information out of it so that the information could then be imported into an advertisement created with Quark. The end result would be a huge time savings for the client. Where the client normally spent several days creating the advertisement, this feature dropped that to only a couple of hours. | |||
| Exporting from Access is fairly easy. You simply export to a text file. However, the format of that export so that Quark can read and understand the file is complicated. Quark uses a file format with Express Tags. These tags are similar in concept to HTML tags, but different. Below is an example of the express tags. | |||
| @1-FEATURE_address_line_1=[S"","1-FEATURE_address_line_1"]<*R*h"NO HYPHEN"*kn0*kt0*ra0*rb0*d0*p(0,0,0,+0.657,0,0,g,"U.S. English")*t(99.672,2,"1 "111.8,2,"1 ")Ps100t0h100z8k0b0cKf"Avenir-Heavy"> @1-FEATURE_description_line_1=[S"","1-FEATURE_description_line_1"]<*R*h"NO HYPHEN"*kn0*kt0*ra0*rb0*d0*p(0,0,0,0,0,0,g,"U.S. English")*t(99.672,2,"1 "111.8,2,"1 ")Ps100t0h100z8k0b0cKf"Goudy"> @1-FEATURE_agent_name_tel=[S"","1-FEATURE_agent_name_tel"]<*R*h"NO HYPHEN"*kn0*kt0*ra0*rb0*d0*p(0,0,0,+0.657,0,0,g,"U.S. English")PKs100t0h100z8k0b0cKf"Avenir-Heavy"> @1-FEATURE_address_line_1:BLUE DOT RANCH<\n>Two hours north of Far Far Away @1-FEATURE_description_line_1:1,800 acres, details at www.bluedot.com $5,000 @1-FEATURE_agent_name_tel:Dan 972-123-4567 |
|||
| The green text above is considered the Style sheet portion of the express tag coding. The style sheet tells Quark what font, color, size, etc that the text data should be. The blue text above is the data, wrapped up with the style sheet. | |||
| Here is some sample export code that actually takes the Access recordset data and exports the data to a text file with the Style sheet code and the data code. Make note of how the Style sheet coding is exported. That is really complicated due to the way that the Chr(34) character is inserted. Also, this project has linked text boxes and the Chr(11) at the end of each text area was important to force the text to jump to the next linked text box. | |||
| vHead = "" vHead = vHead & "<v2.05><e0>" & vbCr vHead = vHead & "@Normal=<Ps100t0h100z12k0b0cKf" & Chr(34) & "Helvetica" & Chr(34) & ">" & vbCr vHead = vHead & "@Normal=[S" & Chr(34) & Chr(34) & "," & Chr(34) & "Normal" & Chr(34) & "," & Chr(34) & "Normal" & Chr(34) & "]<*L*h" & Chr(34) & "Standard" & Chr(34) & "*kn0*kt0*ra0*rb0*d0*p(0,0,0,0,0,0,g," & Chr(34) & "U.S. English" & Chr(34) & ")>" & vbCr vHead = vHead & "@1-address_line_1=[S" & Chr(34) & Chr(34) & "," & Chr(34) & "1-address_line_1" & Chr(34) & "]<*L*h" & Chr(34) & "NO HYPHEN" & Chr(34) & "*kn0*kt0*ra0*rb0*d0*p(0,0,0,+0.657,0,0,g," & Chr(34) & "U.S. English" & Chr(34) & ")*t(99.672,2," & Chr(34) & "1 " & Chr(34) & "111.8,2," & Chr(34) & "1 " & Chr(34) & ")Ps100t0h100z8k0b0cKf" & Chr(34) & "Avenir-Heavy" & Chr(34) & ">" & vbCr vHead = vHead & "@1-address_line_2=[S" & Chr(34) & Chr(34) & "," & Chr(34) & "1-address_line_2" & Chr(34) & "]<*L*h" & Chr(34) & "NO HYPHEN" & Chr(34) & "*kn0*kt0*ra0*rb0*d0*p(0,0,0,+0.266,0,3.6,g," & Chr(34) & "U.S. English" & Chr(34) & ")*t(99.672,2," & Chr(34) & "1 " & Chr(34) & "111.8,2," & Chr(34) & "1 " & Chr(34) & ")Ps100t0h100z8k0b0cKf" & Chr(34) & "Avenir-Heavy" & Chr(34) & ">" & vbCr vHead = vHead & "@1-description_line_1=[S" & Chr(34) & Chr(34) & "," & Chr(34) & "1-description_line_1" & Chr(34) & "]<*L*h" & Chr(34) & "NO HYPHEN" & Chr(34) & "*kn0*kt0*ra0*rb0*d0*p(0,0,0,0,0,0,g," & Chr(34) & "U.S. English" & Chr(34) & ")*t(99.672,2," & Chr(34) & "1 " & Chr(34) & "111.8,2," & Chr(34) & "1 " & Chr(34) & ")Ps100t0h100z8k0b0cKf" & Chr(34) & "Goudy" & Chr(34) & ">" & vbCr vHead = vHead & "@1-listing_price=[S" & Chr(34) & Chr(34) & "," & Chr(34) & "1-listing_price" & Chr(34) & "]<*L*h" & Chr(34) & "NO HYPHEN" & Chr(34) & "*kn0*kt0*ra0*rb0*d0*p(0,0,0,0,0,3.6,g," & Chr(34) & "U.S. English" & Chr(34) & ")*t(99.672,2," & Chr(34) & "1 " & Chr(34) & "111.8,2," & Chr(34) & "1 " & Chr(34) & ")Ps100t0h100z8k0b0cKf" & Chr(34) & "Goudy" & Chr(34) & ">" & vbCr vHead = vHead & "@1-agent_name_tel=[S" & Chr(34) & Chr(34) & "," & Chr(34) & "1-agent_name_tel" & Chr(34) & "]<*L*h" & Chr(34) & "NO HYPHEN" & Chr(34) & "*kn0*kt0*ra0*rb0*d0*p(0,0,0,+0.657,0,3.6,g," & Chr(34) & "U.S. English" & Chr(34) & ")*t(144,2," & Chr(34) & "1 " & Chr(34) & ")PKs100t0h100z8k0b0cKf" & Chr(34) & "Avenir-Heavy" & Chr(34) & ">" & vbCr vHead = vHead & "@1-FEATURE_address_line_1=[S" & Chr(34) & Chr(34) & "," & Chr(34) & "1-FEATURE_address_line_1" & Chr(34) & "]<*R*h" & Chr(34) & "NO HYPHEN" & Chr(34) & "*kn0*kt0*ra0*rb0*d0*p(0,0,0,+0.657,0,0,g," & Chr(34) & "U.S. English" & Chr(34) & ")*t(99.672,2," & Chr(34) & "1 " & Chr(34) & "111.8,2," & Chr(34) & "1 " & Chr(34) & ")Ps100t0h100z8k0b0cKf" & Chr(34) & "Avenir-Heavy" & Chr(34) & ">" '@1-FEATURE_address_line_1=[S"","1-FEATURE_address_line_1"]<*R*h"NO HYPHEN"*kn0*kt0*ra0*rb0*d0*p(0,0,0,+0.657,0,0,g,"U.S. English")*t(99.672,2,"1 "111.8,2,"1 ")Ps100t0h100z8k0b0cKf"Avenir-Heavy"> vHead = vHead & "@1-FEATURE_description_line_1=[S" & Chr(34) & Chr(34) & "," & Chr(34) & "1-FEATURE_description_line_1" & Chr(34) & "]<*R*h" & Chr(34) & "NO HYPHEN" & Chr(34) & "*kn0*kt0*ra0*rb0*d0*p(0,0,0,0,0,0,g," & Chr(34) & "U.S. English" & Chr(34) & ")*t(99.672,2," & Chr(34) & "1 " & Chr(34) & "111.8,2," & Chr(34) & "1 " & Chr(34) & ")Ps100t0h100z8k0b0cKf" & Chr(34) & "Goudy" & Chr(34) & ">" '@1-FEATURE_description_line_1=[S"","1-FEATURE_description_line_1"]<*R*h"NO HYPHEN"*kn0*kt0*ra0*rb0*d0*p(0,0,0,0,0,0,g,"U.S. English")*t(99.672,2,"1 "111.8,2,"1 ")Ps100t0h100z8k0b0cKf"Goudy"> vHead = vHead & "@1-FEATURE_agent_name_tel=[S" & Chr(34) & Chr(34) & "," & Chr(34) & "1-FEATURE_agent_name_tel" & Chr(34) & "]<*R*h" & Chr(34) & "NO HYPHEN" & Chr(34) & "*kn0*kt0*ra0*rb0*d0*p(0,0,0,+0.657,0,0,g," & Chr(34) & "U.S. English" & Chr(34) & ")PKs100t0h100z8k0b0cKf" & Chr(34) & "Avenir-Heavy" & Chr(34) & ">" '@1-FEATURE_agent_name_tel=[S"","1-FEATURE_agent_name_tel"]<*R*h"NO HYPHEN"*kn0*kt0*ra0*rb0*d0*p(0,0,0,+0.657,0,0,g,"U.S. English")PKs100t0h100z8k0b0cKf"Avenir-Heavy"> ' If bFileIsOpen = True Then Close #1 bFileIsOpen = False End If Open "S:\Adfiles\DMNAd_LeftSide.txt" For Output As #1 bFileIsOpen = True Print #1, vHead; '========================Get the TEXT information========= strSQL = "SELECT * FROM tblDesignDMNAd_Working WHERE ( [useforbiggestphoto] = true ); " Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset) If Not rst.BOF And Not rst.EOF Then w = "@1-FEATURE_address_line_1:" & UCase(rst![StreetAddress]) & vbCr Print #1, w; 'rst![StreetAddress] w = "@1-FEATURE_address_line_1:" & rst![AreaTitle] & vbCr Print #1, w; 'rst![AreaTitle] w = "@1-FEATURE_description_line_1:" & rst![DMNAdDesc] & vbCr Print #1, w; 'rst![DMNAdDesc] w = "@1-FEATURE_description_line_1:" & Format(rst![CurrentListPrice], "$##,###,##0") & vbCr Print #1, w; w = "@1-FEATURE_agent_name_tel:" & rst![AgentNameL1] & vbCr Print #1, w; If IsNull(rst![AgentNameL2]) = False Then w = "@1-FEATURE_agent_name_tel:" & rst![AgentNameL2] & vbCr Print #1, w; Else w = "@1-FEATURE_agent_name_tel:" & " " & vbCr Print #1, w; End If Print #1, Chr(11); Else 'There is no big picture w = "@1-FEATURE_address_line_1:" & " " & vbCr Print #1, w; w = "@1-FEATURE_address_line_1:" & " " & vbCr Print #1, w; w = "@1-FEATURE_description_line_1:" & " " & vbCr Print #1, w; w = "@1-FEATURE_description_line_1:" & " " & vbCr Print #1, w; w = "@1-FEATURE_agent_name_tel:" & " " & vbCr Print #1, w; w = "@1-FEATURE_agent_name_tel:" & " " & vbCr Print #1, w; Print #1, Chr(11); End If ' |
|||
| After the exported file is completed, then the Quark operator can import the file into a text area. Assuming that all of the correct fonts are installed on that computer, the text should import with all of the correct styling, etc. | |||
|
keywords: importing data into Quark from access Other pages of interest Microsoft Access Email Integration Medical Insurance Audit Form System Legal Process Server Management and Tracking Purchase Order System For Home Builder Real Estate Business Management
|
|||