VBA + DDE Automated Way to Populate Table into Formatted Excel - - PowerPoint PPT Presentation

▶
vba dde
SMART_READER_LITE
LIVE PREVIEW

VBA + DDE Automated Way to Populate Table into Formatted Excel - - PowerPoint PPT Presentation

Nov 22, 2022 783 likes •1.04k views

VBA + DDE Automated Way to Populate Table into Formatted Excel Skill Enrichment::2018 Ou Zhang Todays Topics Encounter problems One Solution Automated Solution VBA Macros Dynamic Data Exchange ( DDE ) in SAS SAS Macros

slide-1
SLIDE 1

Skill Enrichment::2018 Ou Zhang

VBA + DDE

Automated Way to Populate Table into Formatted Excel

slide-2
SLIDE 2

Today’s Topics

  • Encounter problems
  • One Solution
  • Automated Solution

‒ VBA Macros ‒ Dynamic Data Exchange (DDE) in SAS

  • SAS Macros & Demo
  • Easter Egg
  • Takeaways
  • Q & A

2

slide-3
SLIDE 3

Encounter Problems

  • TELPAS standard setting tech report Appendix G

(Source PDF file) (Appendix G table format)

3

slide-4
SLIDE 4

One Solution

One solution:

1. Read-in data 2. Round value and transpose 3. Export to separate EXCEL tabs by domain and grade respectively 4. Manually format the excel tables respectively

  • Realistic situation:

3 (Domain) x 4 (grade band) x 3 (round) = 42 tables

4

slide-5
SLIDE 5

Automated Solution Summary

  • Step 1: Create a single EXCEL table shell
  • Step 2: Use VBA Macros to duplicate the same pre-

formatted excel shell as needed

  • Step 3: Use SAS DDE to paste summary statistics to pre-

formatted excel table files

5

slide-6
SLIDE 6

Automated Solution

Step 1: Create a single EXCEL table shell

6

slide-7
SLIDE 7

Automated Solution

Step 2: VBA Macros

  • Use VBA Macros to duplicate the same pre-formatted excel shell as

needed.

7

slide-8
SLIDE 8

Automated Solution

Step 2: VBA Macros (cont.)

8

slide-9
SLIDE 9

Automated Solution

Step 2: VBA Macros (cont.)

9

slide-10
SLIDE 10

Automated Solution

Step 2: VBA Macros (cont.)

10

slide-11
SLIDE 11

Automated Solution

Step 2: VBA Macros (cont.)

  • Advantage of using VBA Macros
  • 1. Keep all the formats and fonts
  • 2. Keep all the formulas inside the excel sheet
  • 3. Keep all the dependent graphs within the excel sheet

11

slide-12
SLIDE 12

Automated Solution

Step 3: Dynamic Data Exchange (DDE) in SAS

  • Dynamic Data Exchange (DDE) is a method of dynamically exchanging

information between Windows applications (SAS → EXCEL)

12

temp dataset name tab name table range EXCEL .exe EXCEL table shell (.xlsx) Output variables

slide-13
SLIDE 13

Automated Solution

Step 3: DDE in SAS (cont.)

13

slide-14
SLIDE 14

SAS Macros & Demo

14

4 Macros are developed: 1. Transpose/ modify data by Round 2. DDE Module 3. DDE to paste summary statistics to table (apply macro 1, 2) 4. Apply to multiple subjects and grade bands (apply macro 3)

slide-15
SLIDE 15

SAS Macros & Demo

15

Macro 1: Transpose/ modify data by Round

slide-16
SLIDE 16

SAS Macros & Demo

16

Macro 2: DDE Module

slide-17
SLIDE 17

SAS Macros & Demo (cont.)

17

Macro 3: DDE to paste summary statistics to table (apply macro 1,2)

slide-18
SLIDE 18

SAS Macros & Demo (cont.)

18

Macro 4: Apply to multiple subjects and grade bands (apply macro 3)

slide-19
SLIDE 19

SAS Macros & Demo (cont.)

19

DEMO

slide-20
SLIDE 20

Easter Egg

20

  • Did you know you can run VBA macros from SAS?
slide-21
SLIDE 21

Takeaways

  • VBA Macro is not difficult to understand and can be used

in a good way

  • DDE is powerful and doesn’t change table format at all
  • VBA + DDE can make our table mass-production a little

easier

21

slide-22
SLIDE 22

Acknowledgement

  • Special thanks of gratitude to my colleagues

Kuzey Bilir, Shannon Wilder for their suggestion and advice to this presentation!

22

slide-23
SLIDE 23

Q & A

Thank you!

Questions? Slides + Code:

\\ICDWPCOREDFS01.peroot.com\File_Services\PRS\ADMINISTRATIVE\San Antonio\Meetings_RS_RA\2018\2.Skills_Enrcihment\Presentation_or_Discussion_Materials\October20 18\DDE+VBA

23

slide-24
SLIDE 24