Caution

If you close Excel between recording the first and second macro, Excel inserts a new module,called "Module2,"for the newly recorded macro.

I've edited the code in Figure 1.15 with two comments to show where I remember turning the relative recording on and then off.

Figure 1.15

Macro recorded with Relative References on during part of the recording process.

sub iBportlavQiceaEelatived

Keyboard Shortcut : Ctrl»)

HocHjDaJca. OpenTexc Filename:» "C:\ invetee.t*t", OfSflia at*rtflû»:-I, DataType ¡-KlPellnltea, TextPualilier ¡-xlEoUbleduate _ i CcwecutiveE'eliBiteei-FalM, Tab:-True., 3eaiiCOlOB:-False, Çaïasas-True , Space;«**!«, Oth« s"iAlst, rieJ«iï&iii = "*ïti*vii.i<iiy[i# i)r Ai*sy[i, I), _ Array (J, 1), Array(4, 1], Array<S, 1]. Array (£, 1], Array (7, 1]), Trail lmjItinuaNuntoers _

' Turned on relative reconnuj here Select ico-E«i [»lie»»] .select JbKlvaCcll-OC£**t|i, 0( -iMcna»[**!"] .»Kk ArtiveCeii.FaraulafclCl ■ "'Total" ActiveCell.O*i«tlO, .Select

SelectlûQ.FûenMiaSiCi - "-3UK(Il[-itJC;lt|-iJC) " Select lea- AutoFill Iíe9tiB«lon!»Acllveçell.EaIlfle(nA¿:Cl,'] j Type:» xiruittinK

' Turned ott relative recording here Act iveCe 11 - huiQel"M:Cl") .Select AttlVeCell.ROiiB("li l-J .ïntlreHov ,i!elect AççiveCell.lîtsvate Selectnsn.iont.»ciJ<i ■ Tfw Sora ("1:1"] .Select Selection, rent, to J<> » Tfue Cells.Select

SelectiOB.Colunna.Aut oFit

To test the macro, close Invoice.txt without saving, then run the macro with Ctrl+j. Everything looks good—I get the same results.

The next test is to see whether the program works on the next day when you might have more rows. Figure 1.16 shows the data for June 7th.

Figure 1.16

Will the macro with Relative References work with this data?

I nvo1«P4te. invol «Nurtjer, sal ei-fiepNunL-cr. çgitemfirNunber. pt o<*iti Mvwt, servi ««Revenue. prtriu«c«t

06/07/70« . 1718ïû, S4 i. c:3 390.1*8100.0,8508Ï

06/07/20«. liiSil, 5M. C 2 J a. Ht>600.0,2811>8

06/07/20« , 123B32,S21,Ci519,86200.0,49967

0G/07/70«. mm, S4 5 . C3 24Î. flûttOÔ. 0. 338277

06/07/20«, liiSifl, S Î4 , C//96, «MOD, 0,19i 298

06/07/20«, 1238Ï f, 354 , CÎ143, 92 S4O0, 0,4 f i M S Û6/07/J0M , 111 Rta, S31, c;7Hfi8,14 8700,0, 757ÛG 06/07/20«, SJ i. C3 310. H<90?00.0,468333

06/07/20« ,123 B40.3 54, C2 959,986000,0,528980 06/07/?00>1,8-11, Sîl, C 8Ï«1, <M4Û0, Û, 1Ï1SÛ O6/O//2O04.12 ¿642,54 i. CLB42. ¿«00. î *OÛÛ. 206* 06/07/2004,123B43,354,C4107, 599700,0, 276718 06/07/70« , ] 71 84 4, V?1, Ci ?Û 5,744*00, 0, ]4 3 393 06/07/20«.12J84 i, 54 Î. cm s. éiooo, o, i >102 06/07/20« , 123 B4 6,3 54, d7J0,212600,0,117787 06/07/70« ,173B4 7, S7l. c!6>9î. «74 700. 0.4787il 06/07/20«.12 ¿B48,541. C2 CC5. Ï2 7/00. 0,1 /O'jiS 06/07/20« , 123 SI 9,3 54, (.4 096,30700,0,180 5 6

I open MacroToImportInvoices.xls and run the new macro with Ctrl+J. This time, everything looks pretty good. The totals are where they are supposed to be. Look at Figure 1.17—see anything out of the ordinary?

Figure 1.17

The result of running the Relative macro.

0 0

Post a comment