[RESOLVED] Easy Yes/No Newbie Questions: Can I do this?

[LadyMtBIker]

Hi

I am new to VB... and before I start "barking up the wrong tree", I want to know if the following can be done when generating a Word document from Excel:

*Convert data from Excel into a Word document formating multi-level headers (e.g. 1.1.1 Level 3 Header)? (Haven't figured out how to do this one yet... only know how to get "1. Subject" and not "1.1.1 Subject")
*Create a header/footer? I believe the answer is yes.
*Add a table of contents with the data in Excel (and, yes, those multi-level headers)? I believe the answer is yes.
*Take cell data that has the path and filename of BMP images which would then be used to insert/link those images into the Word document? Not sure if this can be done... but I don't see why not.

I'm sure the first three can be done... but that last one... I'm not sure about.

Thanks!

[si_the_geek]

Assuming that you are using VBA within Excel, it's all possible

You can even get some help on writing the code to do the things you want by "asking" Word, as you can perform the tasks you want manually while recording a Macro, and it will write VBA code for you.

Admittedly you will need to modify the code a bit (especially for the bit about images), but it will point you in the right direction.

If you record macros to do what you want, you can post the code for them here (along with your existing code) and we can help you to merge it in

[LadyMtBIker]

Oh wow, now there is a smart idea!

Let me try that!



And Yes, I am using VBA within Excel.

[LadyMtBIker]

Okay, so I'm working on the "1.1.X" header.

I create my header here:

Set oHeader1 = objDoc.Content.Paragraphs.Add
            With oHeader1
                .Range.Text = STR_IOS_PAGE_NAME
                .Range.Font.AllCaps = True
                .Range.Underline = wdUnderlineThick
                .Range.ParagraphFormat.KeepWithNext = True
                .Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
                .Range.Style = "Heading 3"
 
                .Range.ListFormat.ApplyNumberDefault  'e.g. 1.
            
                .Range.Font.Bold = True
                .Format.SpaceAfter = 24    '24 pt spacing after paragraph.
                .Range.InsertParagraphAfter
        
                .Range.Font.AllCaps = False
                .Range.ParagraphFormat.Alignment = wdAlignParagraphLeft
                .Range.ParagraphFormat.KeepWithNext = False
            End With

But then I took the VB code from Word here (I'm not taking all of it, just the one I need):

With ListGalleries(wdOutlineNumberGallery).ListTemplates(5).ListLevels(1)
            'I only care about List Level 1 since Normal Text data will come after this.
            'And, NumberFormat will be hardcoded since it will be merged into another larger document
            ' at section 3.1
                .NumberFormat = "3.1.%1"
                .TrailingCharacter = wdTrailingTab
                .NumberStyle = wdListNumberStyleArabic
                .NumberPosition = InchesToPoints(0)
                .Alignment = wdListLevelAlignLeft
                .TextPosition = InchesToPoints(0.3)
                .TabPosition = InchesToPoints(0.3)
                .ResetOnHigher = 0
                .StartAt = 1
                With .Font
                    .Bold = wdUndefined
                    .Italic = wdUndefined
                    .Strikethrough = wdUndefined
                    .Subscript = wdUndefined
                    .Superscript = wdUndefined
                    .Shadow = wdUndefined
                    .Outline = wdUndefined
                    .Emboss = wdUndefined
                    .Engrave = wdUndefined
                    .AllCaps = wdUndefined
                    .Hidden = wdUndefined
                    .Underline = wdUndefined
                    .Color = wdUndefined
                    .Size = wdUndefined
                    .Animation = wdUndefined
                    .DoubleStrikeThrough = wdUndefined
                    .Name = ""
                End With
                .LinkedStyle = ""
            End With
            ListGalleries(wdOutlineNumberGallery).ListTemplates(5).Name = ""
            Selection.Range.ListFormat.ApplyListTemplate ListTemplate:=ListGalleries( _
                wdOutlineNumberGallery).ListTemplates(5), ContinuePreviousList:=True, _
                ApplyTo:=wdListApplyToWholeList, DefaultListBehavior:= _
                wdWord10ListBehavior

Now, I changed "Selection" to "oHeader1" but have an error... Maybe I just have the wrong type, Word.Paragraph.

Should be an easy one.

[si_the_geek]

Hmm, I've never seen ListGalleries before, oh well!!

That means that you'll have to find out which of the following two items is correct, one of these should be used in place of both instances of ListGalleries:
objDoc.ListGalleries
objDoc.Application.ListGalleries

And instead of Selection you should have:
objDoc.Application.Selection


If the Font for this section of the document is the same as that around it, you probably don't need the "With .Font ... End With" section.

[LadyMtBIker]

ListGalleries is what the Word macro generated.

Yes, it worked!

Now, I'll try linking images... but only when I get back from vacation, so until next time!

[LadyMtBIker]

I'm back...

And I was able to add images into my Word document that were listed in Excel. The "asking" Word idea has been a great help. It makes VBA easy for me to use... with some exceptions. I need the filename/path in Excel to be checked for validity (does the file really exist?) so the Macro doesn't "break".

Working on that next...

If LEN_TEXT_IN_COL_G > 0 Then
                'Insert image for page
                 Set oPara2 = objDoc.Content.Paragraphs.Add
                 With oPara2
                    'How do I verify that the filename is right?
                    .Range.InlineShapes.AddPicture fileName:=STR_IMAGE_PATH, LinkToFile:=False, SaveWithDocument:=True
                    .Range.InsertParagraphAfter
                End With
            End If

Thanks!

[si_the_geek]

Ok, to check that a filename is valid you need something that you can't get from Macro's.. you should be able to use the Dir function.

You provide the Dir function with a full or partial file name (as well as the path) and it returns the name of a file that matches, if no matching files are found then you just get an empty string.

Your code could be something like this (assuming that STR_IMAGE_PATH contains the path and filename):

If Dir(STR_IMAGE_PATH) = "" Then
  'file not found
Else
  'file found - add it to the document
End if

In this case you can probably change the first line to this (if you only want to add the paragraph if the file exists):
If (LEN_TEXT_IN_COL_G > 0) And (Dir(STR_IMAGE_PATH) <> "") Then

[LadyMtBIker]

Thanks.