Importing HTML and XML Files

If you do need to import data into Access from an HTML or XML file (or just want to experiment with these options) you can use the Import HTML or XML Job Data form (frmlmportHTMLXMLData), which opens if you select the Import HTML or XML Data option on the main menu and click the button to its left (see Figure 10.11).

Clicking the "Inspect New Jobs from HTML File" button displays the imported records in the subform, as shown in Figure 10.24.

Inspecting data imported from an HTML file.

Ê frmlmportHTMLXMLData

[Jtf Import HTML or XML Job Data

Product Number -

Job Date/Time

Factory Name

Production Line

• Batch Quantity >

FRB-1013929-A

2006-07-19T17:43:00

Nutley

143

3579.28

FRB-1013930-A

2006-07-20T14:33:00

Amberson

149

386.57

s

F RB-1013930-B

2006-07-21T10:43:00

156

873.39

FRB-1013903-A

2006-07-22T09:22:00

Si mm s

149

223.22

LJ

FRB-1013931-A

2006-07-22T09:37:00

Si mm s

120

3865.12

FRB-1013932-A

2006-07-23T09:44:00

Nutley

143

984.25

FRB-1013933-A

2006-07-24T10:13:00

Oakdale

120

349.1

FRB-1013905-A

2006-07-24T16:43:00

Nutley

143

221

FRB-1013935-A

2006-07-25T14:43:00

Si mm s

201

739.28

FRB-1013908-A

2006-07-26T08:01:00

Si mm s

149

998.1

FRB-1013936-B

2006-07-26T 15:13:00

Nutley

143

450.01

F RB-1013909-A

2006-07-27T08:29:00

Nutley

120

542.88

FRB-1013938-A

2006-07-27T16:28:00

Amberson

225

3587.92

Record: H 11 of 38 ►

m ► 1 1 ¡Search

Source File j D:\Documents\jobFilestoimport\CurrentJobs.html

Save New Jobs to Table

If you select the XML option, data from the XML file is displayed in the subform, as shown in Figure 10.25.

FIGURE 10.25

Inspecting data imported from an XML file.

S frmlmportHTMLXMLData

Import HTML or XML Job Data

Product Number *

Job Date/Time

Factory Name

Production Line

Batch Quantity -

*

FRB-1013929-A

2006-07-19T17:43:00

Nutley

143

3579.28

FRB-1013930-A

2006-07-20T14:33:00

Amberson

149

386.57

=

FRB-1013930-B

2006-07-21T10:43:00

Nutley

156

873.39

FRB-1013903-A

2006-07-22T09:22:00

Simms

149

223.22

FRB-1013931-A

2006-07-22T09 ; 3 7:00

Simms

120

3865.12

FRB-1013932-A

2006-07-23T09:44:00

Nutley

143

984.25

FRB-101393 3-A

2006-07-24T10:13:00

Oakdale

120

349.1

FRB-1013905-A

2006-07-24T16:43:00

Nutley

143

221

FRB-1013907-A

2006-07-25T08:22:00

Oakdale

120

224.44.

FRB-1013935-A

2006-07-25T14:43:00

Simms

201

739.28

FRB-1013908-A

2006-07-26T08:01:00

Simms

149

998.1

FRB-1013936-B

2006-07-26T 15:13:00

Nutley

143

450.01

FRB-1013909-A

2006-07-27T08:29:00

Nutley

120

542.88

FRB-1013938-A

2006-07-27T16:28:00

Amberson

225

3587.92

■r

Record: i< [l_of 38 ►

M> 1 1 Search

Source File | D:\Documents\JobFilestolmport\FilteredJobs.xml

Import File Type © HTML ij&i XML

Save New Jobs to Table

I' : ' l"r T" r ^'-■'■i^ When you import data from an XML file, there is no option for specifying the name of pfi^K^tK^UiSiSBBaSS- the Access table; it will have the same name as the original data source, possibly with an appended number.

The code for the "Inspect New Jobs From HTML File" button is listed below: Private Sub cmdInspectJobs_Click() On Error GoTo ErrorHandler

Dim strText As String

Dim strTitle As String

Dim strPrompt As String

Dim txtData As Access.TextBox

Dim strAppFile As String

Dim strTable As String

Dim strSpec As String

Dim strHTMLXMLFileAndPath As String

Dim strHTMLXMLFile As String

Dim strHTMLXMLPath As String

Set txtData = Me![txtSelectedAppFile]

strTable = "tblNewJobs"

strHTMLXMLFileAndPath = Nz(txtData.Value) If strHTMLXMLFileAndPath = "" Then strTitle = "No application file selected" strPrompt = "Please select an application file" MsgBox prompt:=strPrompt, Buttons:=vbExclamation _

+ vbOKOnly, Title:=strTitle GoTo ErrorHandlerExit Else

Parse out the file name; it is needed later in the procedure. strHTMLXMLFile = SplitDBName(strHTMLXMLFileAndPath)

Trim off the file extension.

strHTMLXMLFile = Mid(strHTMLXMLFile, 1, InStr(1, strHTMLXMLFile, ".") @@hy 1)

Debug.Print "Trimmed file name: " & strHTMLXMLFile End If intFileType = Nz(Me![fraFileType].Value, 1) Select Case intFileType

Case 1

HTML

DoCmd.TransferText transfertype:=acImportHTML, _ TableName:=strTable, _ FileName:=strHTMLXMLFileAndPath, _ hasfieldnames:=True

Assign the appropriate form as the subform's source object. Me![subNewJobs].SourceObject = "fsubNewJobs"

Case 2

ImportXML DataSource:=strHTMLXMLFileAndPath, _

importoptions:=acStructureAndData DoCmd.SetWarnings False

There is no argument for specifying the name of the table that is created when an XML file is imported; it comes in as the name stored in the XML file (usually the XML file name), possibly with a number added on.

DoCmd.Rename newname:=strTable, objecttype:=acTable, _ oldname:=strHTMLXMLFile

Assign the appropriate form as the subform's source object. Me![subNewJobs].SourceObject = "fsubNewJobs"

End Select

ErrorHandlerExit: Exit Sub

ErrorHandler:

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

End Sub

0 0

Post a comment