Exporting HTML and XML Files

If you want to experiment with exporting Access data to HTML or XML files, try the Export Job Data to HTML or XML File form. If you select the "Export HTML or XML Data" option on the main menu (see Figure 10.10) and click the button to its left, the Export Job Data to HTML or

XML File (frmExportHTMLXMLData) form will open (as shown in Figure 10.26). The form has From Date and To Date textboxes for specifying a date range; clicking the "Inspect New Jobs to Export" button loads the subform with the records from the selected date range.

FIGURE 10.26

Inspecting the filtered job records to export to an HTML or XML file.

lo frmExportHTMLXMLData

Export Job Data to HTML or XML File

Export Date Range: Jobs to Inspect:

From Date: 7/19/2006

From Date: 7/19/2006

Product Number •

Job Date/Time

Factory Name

Production Line

Batch Quantity • ±

F RB-1013929-A

7/19/2006 5:43:00 PM

Nutley

143

3579.28

FRB-1013930-A

7/20/2006 2:33:00 PM

Amberson

149

386.57

FRB-1013930-B

7/21/2006 10:43:00 AM

Nutley

156

873.39

F RB-1013903-A

7/22/2006 9:22:00 AM

Simms

149

223.22

FRB-1013931-A

7/22/2006 9:37:00 AM

Simms

120

3865.12

F RB-1013932-A

7/23/2006 9:44:00 AM

Nutley

143

984.25

F RB-101393 3-A

7/24/2006 10:13:00 AM

Oakdale

120

349.1

F RB-1013905-A

7/24/20064:43:00 PM

Nutley

143

221

F RB-1013907-A

7/25/2006 8:22:00 AM

Oakdale

120

224.44

F RB-1013935-A

7/25/2006 2:43:00 PM

Simms

201

739.28

F RB-1013908-A

7/26/2006 8:01:00 AM

Simms

149

998.1

FRB-1013936-B

7/26/2006 3:13:00 PM

Nutley

143

450.01

F RB-1013909-A

7/27/2006 8:29:00 AM

Nutley

120

542.88

F RB-1013938-A

7/27/20064:28:00 PM

Amberson

225

3587.92 w

Re

cord: M |l of 38 | ►

H y \ { 1 ¡Search

|

Export File Type @ HTML © XML

Inspea New Jobs to Export ;

Export File Type @ HTML © XML

Inspea New Jobs to Export ;

Clear Jobs to Export Export Jobs to HTML File

Clicking the "Export Jobs to HTML File" button (or "Export Jobs to XML File"; the caption changes with the selection in the "Export File Type" option group) starts the export. The HTML export is done with the TransferText method with the acExportHTML value for the TransferType argument; the XML export is done with the ExportXML method of the Access Application object.

Figure 10.27 shows an exported HTML file opened in Internet Explorer 7. Unfortunately, it is completely unformatted and thus probably won't be very useful.

An exported HTML file opened in Internet Explorer.

fi 1i:..iri!.'.r!"i'.^n'Ji!fc tut ¡iru'r'-T'.'.rn.,.ni'. - virnrlaws Enteinet trtplorer | ■ ~— ~

( ,, f ■ @ E:\Oocuments\Eiporteii DacumpntsUobs.iitiii - | *f | X trW Seartil P

*

Links t Customize Links , Add-irts Art Authors & Books B eta , Bookstores ; Computer Hardware

'if l^j3"! fcVtocflmentAta... & ErUïôciimeiite™ X | - ti " Ä * l'nr.i' ' ¿'jf : ook - "

21 FKB-1013929-A2006-07-19T17:43:00 Nutley 143 3579.28 22 FEB-1013930-A2006-07-20T14:33:00 Amber son 149 386.57 23 FEB-1013930-B 2006-07-21T1043:00 Nutley 156 873 39 24 FRE-1013903-A 2006-07-22T09:22:00 Simms 149 223.2225 FEE-1013931-A 2006-07-22109:37:00 Simms 1203865.12 26 FEB-1013932-A 2006-07-23T09:44:00 Nutley 143 984.25 27 FEE-1013933-A 2006-07-24T10:13J)0 Oaltdule 120 349.1 28 FEB-1013905-A2006-07-24T16:43:00 Nutley 143 221 29 FRB-1013907-A 2006-07-25T08i22:00 Oalcdale 120224.44 30 FEB-1013935-A2006-07-25T14:43:00 Simms 201 739.28 31 FEE-1013908-A2006-07-26T08:01:00 Slum 149998.1 32 FEE-1013936 B 2006-07-26T15:13:00 Nutley 143 450 01 33 FRB-1013909-A 2006-07-27T08J29:00 Nutley 120 542 88 34 FEB- 1013938-A 2006-07-27T16J8ÎD Ambetson 225 3587 92 35 FRB-1013941-A 2006-0728117:28:00 Nutley 225 385 22 36 FRB-1013943-A 2006-07-29T01«>00 Ambetson 201 873.24 37 FSB-1013899-A2OO6-O7-29T17:45iO Nutley 201 2233 6638 FRB-1010144-D2006-07-30T09:12.™ Simms 143 448.11 39 FEB-1013946-A2006-07-30T11:1900 Nutley 156 1103.38 40 FEB-1013916-A 2006-07-30T13:13J)0 Nutley 149 3847 2 9 41 FRB-1012712-C 2006-07-31T20s23:00 Simms 156 1144 22 42 FEB-1013948-A 2006-08-01T1439» Nutley 120 234 22 43 FRB-1013947-A 2006-08-01T14:43«> Ambetson 225 837.22 44 FEB-2250916-A 2006-08-02T14:44:00 Siums 201 1254.9 45 FRB-1013955-A2006-08-02T16:23:00Amberson201 3744.28 46FEB 2250089 B 2006-08-03T18.23:00 Ambetson 225 788.3 81 FEB-1012712-C2006-07-31T2023:00 Simms 156 1144.22 82 FRB-1Q13948-A 2006-08-01T143M» Nutley 120 234.22 83 FRB-1013947-A 2006-08-01T14:43.D0 Ambetson 225 837 22 84 FRB-2250916-A 2006-08-02T14:44:00 Simms201 1254 9 85 FRB-1013955-A 2006-08-02T16:23:00 Ambetson 2013744 28 86 FRB-2250089-B 2006-08-03118:23:00 Ambetson 225 788.3 89 FEB-1012712-C2006-07-31T20--23:00 Simms 156 1144.22 90 FEB 1013948 A 2006-08-01T14:3MX> Nutley 120 2342291 FRB-1013947-A2006-08-01T14:43:00Ambetson225 837.22 92 FEB-2250916-A 2006-08-02T14:44:00 Simms 201 1254.9 93 FEB- 1013955-A 2006-08-02T16J3i)0 Ambetson 201 3744.28 94 FEB-2250089-B 2006-08-03T18:23:00 Ambetson 225 788.3

[fc Computer Protected Mode Off *ti 100% -

The code for clearing old data and inspecting the jobs to export is similar to the code for other export types; only the event procedure for the "Export Jobs to HTML/XML File" button is listed as follows; it uses a Select Case statement to export the data to either an HTML file (using the TransferText method) or an XML file, using the ExportXML method of the Access Application object:

Private Sub cmdExportJobs_Click()

On Error GoTo ErrorHandler

Dim intFileType As Integer

Dim strQuery As String

Dim strTitle As String

Dim strPrompt As String

Dim strOutputPath As String

Dim strFileName As String

Dim strFileNameAndPath As String intFileType = Nz(Me![fraFileType].Value, 1) strQuery = "qryFilteredJobs"

strOutputPath = GetOutputDocsPath() Select Case intFileType Case 1

HTML

strFileName = "Jobs.htm"

strFileNameAndPath = strOutputPath & strFileName DoCmd.TransferText transfertype:=acExportHTML, _ TableName:=strQuery, _ FileName:=strFileNameAndPath, _ hasfieldnames:=True

Case 2

strFileName = "Jobs.xml"

strFileNameAndPath = strOutputPath & strFileName ExportXML objecttype:=acExportQuery, _ DataSource:=strQuery, _ datatarget:=strFileNameAndPath

End Select strTitle = "Exported jobs"

strPrompt = "Exported filtered jobs to " & strFileNameAndPath MsgBox strPrompt, vblnformation + vbOKOnly, strTitle

ErrorHandlerExit: Exit Sub

ErrorHandler:

& "; Description: " & Err.Description Resume ErrorHandlerExit

End Sub

If you open an XML file in IE 7, running on Windows Vista, you'll see a yellow bar with a security warning. If you click the bar you can select to allow blocked content, as shown in Figure 10.28.

A security warning when opening an XML file in Windows Vista.

& E:\Documents\Job Fites to Import\Jobs.xml - Windows Internet Explor EADocumentsXJob Files to Import\Jobs,xml r&ii^i

Links Customize Links Add-ins Art . Authors fli Books Hi I 'S E:\Doctjments\Job Files to ImportVJobs.xml

<?xml version="1.0" encoding="UTF-8" ?> - <dataroot xmlns:od="um:schemas-microsoft-com:offi

. Bookstores Computer Hardware

© To help protect your security, Internet Explorer has restricted this webpane frrvr could access your computer. Click here for options...

Allow Blocked Content,,, What's the Risk?

More information

04T15:35:0Q">

- <qryFilteredJobs>

<ProductNumber>FRB-1013929-A</ProductNumber> <Jo bD a teTime >200&- 07-19T17:43:00 </JobD a teTim e > <F actoryN a m e >Nutley </F acto ryN a m e > <P rod uc tionLi n e >143 <^Prod u c tionLi n e > <BatchQuantity>3579.28</BatchQuantity> </qryF i I tere dJo b s >

- <qryFilteredJobs>

<ProductNumber>FRB-1013930-A</ProductNumber> <Jo bD a teTime >2006-07-20T14:33:00 </JobD a teTi m e > <F actoryN a m e >Am b erso n </FactoryN am e > <P rod uc tionLi n e >149 -s^Produc tionLi ne> <BatchQuantity>386.57<^BatchQuantity> </qryF i I tere dJo b s >

- <qryFilteredJobs>

<ProductNumber>FRB-1013930-B</ProductNumber> <Jo bD a teTime >2006-07- 21T10:43:00 </JobD a teTi m e > <FactoryName>Niitley</FactoryName> <P rod uc tionLi n e >15 6</Prod u c tionLi n e > <BatchQuantity>873.39</BatchQuantity> </qryFilteredJobs>

- <qryFilteredJobs>

<P rod u c tN u m b er >FRB-1013903- A </Pro ductNumber> <JobDateTime>2006-07-22T09:22:00</JobDateTime>

If you select to allow blocked content, you'll get another security warning, shown in Figure 10.29.

Finally, the XML file displays (see Figure 10.30), but as source code, not a properly formatted document, so it (like the HTML file) is not very useful.

FIGURE 10.29

Another Vista security warning.

FIGURE 10.30

An XML file opened in Internet Explorer.

fê E:\Dociiments\Job Files to Import\Jobs.xml - Windows Internet Explorer

h^-ii-E ll«r>l

t-TV'-'i) 1E:\Documents\Job Files to Import\Jobs.xml

*f 1 X 1 1 Live Search

P-l

Links jfi, Customize Links Add-ins Art Authors

8t Books

Beta Bookstores

Computer Hardware

"A 1 E:\Documents\Job Files to ImpDrtVlobs.xmi

■[■1

Ô T El ' #

» Page - Tools » W

<?xml ysrsion="1.0" encoding=nUTF-8" ?> - <dataroot xmlns:od="urn:schemas-microsoft-com:ofTicedata" generated="2Q06-08-04T15:35:00" >

- <qryFilteredJobs>

<ProductNumber>FRB-1013929-A</ProductNumber> <JobDateTime>2006-07-19T17:43:00<yjobDateTime> <FactoryName>Nutley</FactoryName> <ProductionLine>143</ProductionLine> <BatchQuantity>3579.28</BatchQuantity> <^qryFilteredJobs >

- <qryFilteredJobs>

<ProductNumber>FRB-1013930-A</ProductNumber> <JobDateTime>2006-07-2QT14:33:00<yjobDateTime> <FactoryName>Amberson^/FactoryName> <ProductionLine>149</ProductionLine> <BatchQuantity>386.57</BatchQuantity> </qryFilteredJobs >

- <qryFilteredJobs>

<ProductNumber>FRB-1013930-B</ProductNumber> <JobDateTime>2006-07-21T10:43:00</JobDateTime> <FactoryName>Nutley</FactoryName> <ProductionLine>156</ProductionLine> <BatchQuantity>873.39<^BatchQuantity> </qryFilteredJobs >

- <qryFilteredJobs>

<JobID>24<jGobID>

<ProductNumber>FRB-1013903-A</ProductNumber> <JobDateTime >2006-07-22X09:22:00 </JobDateTime> <FactoryName>Srmms</FactoryName> <ProductionLine>149</ProductionLine>

Done Computer | Protected Mode: Off ^100% -

You can also open an XML file in Excel. After selecting it, you get an Open XML dialog with three options, as shown in Figure 10.31. To see what the formatted XML data looks like, select the "As an XML table" option.

FIGURE 10.31

Three options for opening an XML file in Excel.

Please select how you would like to open this file: © Use the XML Source task pane

If you accept the default option of "As an XML table," you'll get the message shown in Figure 10.32.

FIGURE 10.32

Creating an XML schema when opening an XML file in Excel.

FIGURE 10.32

After accepting this message, the XML file finally opens in Excel, as shown in 10.33, with an extra column called "generated" indicating the time the file was created.

FIGURE 10.33

An XML file opened in Excel.

FIGURE 10.33

An XML file opened in Excel.

If you want to export data from Access to Excel, I recommend using the worksheet or |Wk»*>ikirtiirJ.-snfflsiS comma-delimited format instead of XML; they are much easier to work with, and support older versions of Excel that can't open XML files.

You can also use the Save method of an ADO recordset with the adPersistXML named constant as the value of its PersistFormat argument, to produce an XML file, but a file produced using this method also opens as source code.

0 0

Post a comment