HandsOn From Access to Excel Loading an XML File into an Excel Workbook

1. In the Visual Basic Editor window, choose Insert | Module to add a new standard module to the current VBA project.

2. In the module's Code window, enter the OpenAdoFile procedure as shown below.

Sub OpenAdoFile()

Dim rst As ADODB.Recordset Dim objExcel As Excel.Application Dim wkb As Excel.Workbook Dim wks As Excel.Worksheet Dim StartRange As Excel.Range Dim h as Integer

Set rst = New ADODB.Recordset

' open your XML file and load it rst.Open "C:\Learn_XML\Products_AttribCentric.xml", "Provider=MSPersist" ' display the number of records

MsgBox "There are " & rst.RecordCount & " records " & _ "in this file."

Set objExcel = New Excel.Application

' create a new Excel workbook Set wkb = objExcel.Workbooks.Add

' set a reference to the ActiveSheet Set wks = wkb.ActiveSheet

' make Excel application window visible objExcel.Visible = True

' copy field names as headings to the first row of the worksheet For h = 1 To rst.Fields.Count wks.Cells(1, h).Value = rst.Fields(h - 1).Name

Next

' specify the cell range to receive the data (A2) Set StartRange = wks.Cells(2, 1)

' copy the records from the recordset beginning in cell A2 StartRange.CopyFromRecordset rst

' autofit the columns to make the data fit wks.Range("A1").CurrentRegion.Select wks.Columns.AutoFit

' save the workbook wkb.SaveAs "C:\Learn_XML\ExcelReport.xls"

Set objExcel = Nothing

Part V

Set rst = Nothing End Sub

The above procedure is well commented, so we will skip its analysis and proceed to the next step.

3. Run the OpenAdoFile procedure.

When the procedure is complete the Excel application window should be visible with the ExcelReport.xls workbook file displaying products retrieved from the XML file (see Figure 29-26).

4. Close the Excel workbook and exit Excel.

IE] Microsoft Excel - ExralRep-ort

IBM

Rfl Ffjf view [rsnr Formst Toa« Dita Mniwj Help

lyjs a cue:.'. u i u vJj

- ff X

i SI: A ¿ di Û -- A - ± £ - ?.. i Ú « I:/"1

- 1J H ! Il S ; S^l t ••;:, is : ; ' ^ -,

A: g

Ai - A PioductID

A 1 E 1 t 1 D 1 E

F 1 m H ! J

PríCJc:!D PtaductNarne

SuppliedD Catagorylü QüänäityPerUrit

UnitPrice

UnitsInE'ock Ur tsOnOrder Raordaftiavel Discomsited

1 Chai

1

1 10 boxes X 21 bags

18

39

0

10

FALSE

2 Cheng

1

1 24 -12 ci boulas

19

17

40

25

FALSE

3 Anieead Syrup

1

2 12 - 550 m! botlles

10

13

70

25

FALSE

4 Chef Anton's Csjur Seasor ng

2

2 48-Go¿js*s

22

53

0

0

FALSE

5 Chef Anton's Gumbo Mi:<

2

2 £ boxes

21.35

0

0

0

TRUE

6 Grandma's Baysenberry Spread

3

2 12 - B oz js>-&

25

120

0

25

FALSE

7 Uncle Bob's Organic Oriad Reare

3

7 12-1 Ibpkgs.

3D

15

0

10

FALSE

Ö Worthwoods CranbetTy Sau;e

3

2 12 -12 oí jara

<0

G

0

0

FALSE

9 W shi Kobe Niku

>1

6 10 - 900 g pkgs.

97

29

0

0

TRUE

10 Ikurs

A

0 12-200 mijars

31

31

0

0

FALSE

11 Queso Cabrees

5

'1 I kg pkg.

21

22

30

30

FALSE

12 Queso Vlanchego La =astc-3

5

4 I0- 900 g pkgs

33

00

0

0

FALSE

13 Konbu

G

8 2 kg box

6

24

0

5

FALSE

14 Tcfij

G

7 40 - "!Ö0 g pkqs

23.25

35

0

0

FALSE

15 Genen Shouj'U

G

7 74 - 250 rp! hntllBS

155

39

0

5

FALSE

16 P^flrva

7

3 3? - 900 g boi.es

17,45

29

0

m

FALSE

17 AJir.n MuHnn

7

G 3P -1 kü lins

33

0

0

0

TRI.r

1H Oamnrmn Tignrs

7

B 16 kp pl¡ g

R35

4?

0

n

FALSE

19 TnaL-nu Dlocolale Bismils

H

3 IG bn ics e 13 piccün

S3

¿5

5

FALSE

20 Sir Rpdncyt Mairraladu

S

3 33 giS liuilus

B1

4Q

D

FALSE

31 Sir RúdnByt Scanuu

E

3 24 pkys i - pit'wu

in

3

40

5

FALSE

32 Gualafe KriaLkelirdd

y

6 24 - 5DÜ g ¡ikgü

21

1D4

25

FALSE

33 luir ar'ud

y

à 13 - 2iU g akye

3

lai

2&

hALSE

24 Guaraná Fantástica

10

1 12 - 3S5 mi cans

4.5

Q

U

IftUfc

25 NuhiüCa Muß-Nougat-Cieme

11

3 2D - 45Ü g ylaitsi

14

?e

0

30

FALSE

26 Gümbäif Liunnniibärcrhan

11

3 1CÜ - 2SQ ç bags

31.23

15

0

0

FALSE

27 Schoggi Schokolade

11

3 ICO - 10Q c ptacae

43.9

49

0

30

FALSE

23 Rósele Sauetkrsjl

12

7 25 - S2S g ';sns

456

0

0

TRUE

Thür Rostbratwurst

12

6 bags X 30 sausgs.

123.79

0

0

0

TRUE

K) Nord-Ost Mfltjaehering

13

8 10- 200 g glsasîs

25.®

10

0

15

FALSE

31 Cargonzoia Telino

14

4 12 -100 g okgs

125

0

70

20

FALSE

32 Masjaipone Faäic ■

14

4 24 - 200 g okgs.

32

9

40

25

FALSE

33 Geitest

16

4 SCO g

2.5

112

0

20

FALSE

4 .

Figure 29-26: An ADO recordset persisted to an XML file is now opened in Excel.

0 0

Post a comment