Solved: More Help With Multiple Documents

[DekHog]

Hi - I'm really struggling with this...... I found the code below which should let me apply a macro to multiple documents. It does let me browse to the folder, but I can't get the code of the saved macro to run properly, and know it's something simple but don't have the knowledge to do anything about fixing it! Can anyone help with this..... first code is the 'browse' code.

The two macros below it run fine on their own, but need them in the browse/apply code as two separate modules. The macro code should obviously go after the 'do something' statement, but I've tried lots of ways with no joy. You can see what the macro's do on their own using the document attached if it's of any help.

Browse/Apply to Multiple Docs Code
Sub UpdateDocuments()
Application.ScreenUpdating = False
Dim strFolder As String, strFile As String, wdDoc As Document
strFolder = GetFolder
If strFolder = "" Then Exit Sub
strFile = Dir(strFolder & "\*.doc", vbNormal)
While strFile <> ""
Set wdDoc = Documents.Open(FileName:=strFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False)
With wdDoc
'Do something



End With
wdDoc.Close SaveChanges:=True
strFile = Dir()
Wend
Set wdDoc = Nothing
Application.ScreenUpdating = True
End Sub

Function GetFolder() As String
Dim oFolder As Object
GetFolder = ""
Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)
If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path
Set oFolder = Nothing
End Function

Macro 1
Sub selecttables()
Dim mytable As Table
Application.ScreenUpdating = False

For Each mytable In ActiveDocument.Tables

With mytable
With .Borders(wdBorderLeft)
.LineStyle = wdLineStyleDot
.LineWidth = wdLineWidth050pt
.Color = wdColorAutomatic
End With
With .Borders(wdBorderRight)
.LineStyle = wdLineStyleDot
.LineWidth = wdLineWidth050pt
.Color = wdColorAutomatic
End With
With .Borders(wdBorderTop)
.LineStyle = wdLineStyleDot
.LineWidth = wdLineWidth050pt
.Color = wdColorAutomatic
End With
With .Borders(wdBorderVertical)
.LineStyle = wdLineStyleDot
.LineWidth = wdLineWidth050pt
.Color = wdColorAutomatic
End With
With .Borders(wdBorderBottom)
.LineStyle = wdLineStyleDot
.LineWidth = wdLineWidth050pt
.Color = wdColorAutomatic
End With
With .Borders(wdBorderHorizontal)
.LineStyle = wdLineStyleDot
.LineWidth = wdLineWidth050pt
.Color = wdColorAutomatic
End With
.Borders(wdBorderDiagonalDown).LineStyle = wdLineStyleNone
.Borders(wdBorderDiagonalUp).LineStyle = wdLineStyleNone
.Borders.Shadow = False
End With

Next
ActiveDocument.SelectAllEditableRanges (wdEditorEveryone)
ActiveDocument.DeleteAllEditableRanges (wdEditorEveryone)

ActiveDocument.Save

Application.ScreenUpdating = True
End Sub

Macro 2
Sub SelectRows()
'
' selectrows Macro
'
'
Selection.MoveDown Unit:=wdParagraph, Count:=5, Extend:=wdExtend
With Selection.Cells
With .Borders(wdBorderLeft)
.LineStyle = wdLineStyleSingle
.LineWidth = wdLineWidth050pt
.Color = wdColorAutomatic
End With
With .Borders(wdBorderRight)
.LineStyle = wdLineStyleSingle
.LineWidth = wdLineWidth050pt
.Color = wdColorAutomatic
End With
With .Borders(wdBorderTop)
.LineStyle = wdLineStyleSingle
.LineWidth = wdLineWidth050pt
.Color = wdColorAutomatic
End With
With .Borders(wdBorderBottom)
.LineStyle = wdLineStyleSingle
.LineWidth = wdLineWidth050pt
.Color = wdColorAutomatic
End With
With .Borders(wdBorderHorizontal)
.LineStyle = wdLineStyleSingle
.LineWidth = wdLineWidth050pt
.Color = wdColorAutomatic
End With
.Borders(wdBorderDiagonalDown).LineStyle = wdLineStyleNone
.Borders(wdBorderDiagonalUp).LineStyle = wdLineStyleNone
.Borders.Shadow = False
End With
With Options
.DefaultBorderLineStyle = wdLineStyleSingle
.DefaultBorderLineWidth = wdLineWidth050pt
.DefaultBorderColor = wdColorAutomatic
End With
Selection.MoveUp Unit:=wdLine, Count:=1
ActiveDocument.Save
End Sub

Thanks for your help......

[Frosty]

There is one main problem with your code...
1) you're opening the document with Visible:= False ... that means that opened document will *not* be either the ActiveDocument or the document with a Selection object available to you.

Do you need to open these documents with visible = false? If so, then you will need to pass a document parameter to your subroutines, and pass the document object... ala...

Sub SelectTables (oDoc As Document)
For Each myTable in oDoc.Tables
'yada yada
End Sub

I'd suggest changing the visible to True for the moment, strictly for testing.

If that gets you going, then we can talk about how to remove the use of ActiveDocument and Selection from your code-- didn't you want to run this code on 650+ documents?

[DekHog]

Yeah, it's around 650 documents! TBH, I don't even know what the 'visible = false' does..... I'm just a non-geek trying to save myself days of laborious work! :)

I was just taking the sub and end sub out of the macros and pasting the remainder after the 'Do Something' line....... living in hope; false hope! :)

I'm assuming just the visible = false isn't the only problem with this?

Thanks for your help again...... the MulitMacro I mentioned in the previous post gives runtime errors with these two macros, so can't use it for this. A great pity as it's pretty much idiot-proof.....

[DekHog]

Wow, nearly there! Changing to =True and inserting the code for Macro 1 it works a treat - the Macro 2 doesn't work for some reason; the first three lines of the first table remain dotted rather than changing to a solid 0.5pt line..... but I'm half way home, so nearly a very happy man! :)

I'd LOVE to be able to understand all this, but my logical (illogical?) brain just does not compute it for some reason..... :(

But I'm so grateful for the help up to now, I just hit the 'Donate' button for the forum.... :)

[fumei]

Yeah, it would be better to ot use Selection. Perhaps trying to action the table directly.

Also in terms of organizing when the different macros run, try using a Call instruction.

[DekHog]

Yeah, it would be better to ot use Selection. Perhaps trying to action the table directly.

Also in terms of organizing when the different macros run, try using a Call instruction.

Sorry, but that all went over my head.....

You're not the first person to tell me not to use selections, but I'm really not too bothered about the most efficient code, I just need it to work, rough and slow or not! :)

Frosty, HELP with getting Macro 2 to work .......... please!! :)

[Frosty]

Well, it's a good idea to try and learn as much as you can for each question you post-- otherwise, you'll just end up posting repetitive questions. That said... I'm not sure why your Macro2 isn't working. It's based on the Selection... which should be the active document, now that you're opening the documents and displaying them too.

Do you know how to step through a macro by using F8? Where is your selection when you call that macro? Is it in the document you want it to be in.

I'm not meaning to speak for Fumei, but both he and I tend to try to teach people to solve their own problems, rather than just post working code which would be, as you say, over your head.

To me, this forum is about teaching people how to solve their own VBA problems. Others may just post working code... but that just means you'll be back to get more free code without learning more and ultimately learning enough to help other people.

So, I'd prefer to help you learn how to troubleshoot your own code, than simply give you the answer...

Why don't you post the code you're currently using, and then we can go from there.

[DekHog]

Yes, I can step through it, but as it doesn't actually show the document it's working on, I can't see what happening or not happening or where the selection actually is? I do agree about the learning bit, but if I'm totally honest, as much as I try, I just really don't get it....... it's NOT lack of effort, or not wanting to, I just don't.... :(

I know it works if not inside the browse code...... what I'm running for Macro 2 at the moment is.....

EDIT: OK, idiot me has pasted the same code as Macro 1 into the browse code...... forget it, I'll go try and do the right thing.... :)

Sub UpdateRows()
Application.ScreenUpdating = False
Dim strFolder As String, strFile As String, wdDoc As Document
strFolder = GetFolder
If strFolder = "" Then Exit Sub
strFile = Dir(strFolder & "\*.doc", vbNormal)
While strFile <> ""
Set wdDoc = Documents.Open(FileName:=strFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=True)
With wdDoc
'Do something

Dim mytable As Table
Application.ScreenUpdating = False

For Each mytable In ActiveDocument.Tables

With mytable
With .Borders(wdBorderLeft)
.LineStyle = wdLineStyleDot
.LineWidth = wdLineWidth050pt
.Color = wdColorAutomatic
End With
With .Borders(wdBorderRight)
.LineStyle = wdLineStyleDot
.LineWidth = wdLineWidth050pt
.Color = wdColorAutomatic
End With
With .Borders(wdBorderTop)
.LineStyle = wdLineStyleDot
.LineWidth = wdLineWidth050pt
.Color = wdColorAutomatic
End With
With .Borders(wdBorderVertical)
.LineStyle = wdLineStyleDot
.LineWidth = wdLineWidth050pt
.Color = wdColorAutomatic
End With
With .Borders(wdBorderBottom)
.LineStyle = wdLineStyleDot
.LineWidth = wdLineWidth050pt
.Color = wdColorAutomatic
End With
With .Borders(wdBorderHorizontal)
.LineStyle = wdLineStyleDot
.LineWidth = wdLineWidth050pt
.Color = wdColorAutomatic
End With
.Borders(wdBorderDiagonalDown).LineStyle = wdLineStyleNone
.Borders(wdBorderDiagonalUp).LineStyle = wdLineStyleNone
.Borders.Shadow = False
End With

Next
ActiveDocument.SelectAllEditableRanges (wdEditorEveryone)
ActiveDocument.DeleteAllEditableRanges (wdEditorEveryone)

ActiveDocument.Save

Application.ScreenUpdating = True

End With
wdDoc.Close SaveChanges:=True
strFile = Dir()
Wend
Set wdDoc = Nothing
Application.ScreenUpdating = True
End Sub

Function GetFolder() As String
Dim oFolder As Object
GetFolder = ""
Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)
If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path
Set oFolder = Nothing
End Function

[DekHog]

Working!! Sorry for being a dummy.....

Can someone quickly explain to me why the visible= thing was the difference between it working and not working?

Thanks for helping out..... saved me hours of work.... :)

[Frosty]

Two quick concepts.. most of the time when you record a macro, the recorded macro uses one or both of these two "main" objects: ActiveDocument and Selection.

That is because you're always recording a macro of your interactions with the document on your screen, and the cursor within that document. And that's what those objects "mean" -- ActiveDocument is the currently "active" document... and Selection is wherever your cursor is (whether it's just an insertion point, or if you've selected a couple of paragraphs).

When you instruct vba to .Open a given document with the .Visible parameter as False, it does just that-- it opens the document, but it doesn't display the document.

Which means your ActiveDocument still refers to the document on your screen, not the document you've just opened. And the Selection still refers to the cursor in your ActiveDocument, since there is no cursor in that hidden document.

Once you get a little more adept, you'll start to see how you can use variations of those concepts (the Document object-- which does *not* have to be the ActiveDocument, and a Range object, which can be the same as your cursor, but is often more efficient to simply think of as multiple "hidden" "cursors" that you can orient, move around, and then manipulate however you want.

But that's a topic for another day.

[DekHog]

Frosty, I actually understood that..... can you write a book please? :)

Thanks again......

[Frosty]

We aim to please. :)

There are already too many books on these subjects. The problem with "x for dummies" is that it is always an undelivered promise. Either one desires to stay a dummy, or one desires to learn.

If you want to learn... google is better than any single book.

If you don't-- what's the point of a book?

Glad you understood the above. Good luck, and keep coming back. Soon enough you'll be able to help people too.

I learned the majority of what I know by both asking and answering questions. Which is why I keep doing both: because I continue to be willing to learn, even if, as in a thread like this, my learning revolves around learning how to explain a concept.

In a paraphrase of Hamlet: "The willingness is all."

Best!
Jason aka Frosty