Hello Forum Users,
in the course of a student job I was given a word vba task. I started with NO knowledge on the topic at all. I am pretty sure that there is a whole lot to improve and that I could learn a lot from you.
About the task:
A SQL database creates a large html document. (I have nothing to do with that)
I was told to create a word vba macro within access to create a neatly styled word and PDF file based on this html file (and a .css stylesheet).
What I did:
- I created a Word template with all the styles I need.
- I created a .css file to make one aspect of each style unique
(I used the backgorund color, varying from white to light gray.)
- I created the vba macro.
It actually works pretty fine (to my suprise). The only thing is that it takes 6 minutes to create a 300 pages long document. Since this document will grow larger in the future (maybe even a lot larger) I need to make it work faster.
What the macro does:
- Initialization (ca. 15s)
+ Open the templete
+ Insert content from html file (Stylesheet information is applied in the word document as well and used to determine which style needs to be applied for each element in the document)
- Table styling (ca. 60s)
+ Call table sub (For Each t In .ActiveDocument.Tables)
--> applying one of two table styles depending on t.Rows.Alignment
--> changing t.Columns(x).wdPreferredWidthType and t.Columns(x).wdPreferredWidth for all tables of one style
- Image styling (ca. 30s)
+ Call image sub (For Each pic In .ActiveDocument.InlineShapes)
--> applying the image style
--> pic.LinkFormat.SavePictureWithDocument = True
- Paragraph styling (ca. 200s <-- ouch!)
+ applying one of 18 styles based on background color of each paragraph (the unique background color is used to determine which one)
example:
For Each p In .ActiveDocument.Paragraphs dX = p.Range.ParagraphFormat.Shading.BackgroundPatternColor p.Range.ParagraphFormat.Shading.BackgroundPatternColor = wdColorAutomatic Call ParaStyler(dX, p, objWord) Next ----------------------------------------- Private Sub ParaStyler(dX, p, objWord) With objWord Select Case dX Case 16777215 p.Range.Style = .ActiveDocument.Styles("MyStyle1") Case 16646143 p.Range.Style = .ActiveDocument.Styles("MyStyle2") End Select End With End Sub
- TOC/TOF/TOT creation (ca. 10s)
--> Self-explaining
- Finalization (ca. 20 s)
--> Chaning minor things here and there
- Saving
--> Save Word File
--> Save PDF File
__________________________________
__________________________________
Thanks for reading so far! I want to make this work faster and to learn about vba. I would have no problem to change the entire concept (even if i am a little proud to have come that far).
What are your very first thought after you have read this? What would you have done differently? Which mistakes are obvious to you?
THANK YOU VERY MUCH!
Greetings from Cologne, Germany.