Userform populates bookmarks in external docs - Compile Error 3

[RP1America]

I am creating a userform through Word '07 to populate bookmarks within external documents (100+) and then saveas same document name but different folder.

Here is my command button code...where am I going wrong? I know my With...End With is incorrect somewhere.

Thanks!!

Private Sub cmdCreate_click()

Dim strQuarter As String
Dim strQuarterDates As String
Dim strDateText As String

Select Case cboQuarter
    Case "1st Quarter"
        strQuarter = "first quarter"
        strQuarterDates = "(January 1st through March 31st)"
    Case "2nd Quarter"
        strQuarter = "second quarter"
        strQuarterDates = "(April 1st through June 30th)"
    Case "3rd Quarter"
        strQuarter = "third quarter"
        strQuarterDates = "(June 1st through August 31st)"
    Case "4th Quarter"
        strQuarter = "fourth quarter"
        strQuarterDates = "(September 1st through December 31st)"
End Select

strDateText = strQuarter + " of " + txtYear + " " + strQuarterDates

With Document("I:\Ryan Plew\401(k) FS LS Architect A2.doc")
    .Bookmarks("QYD").Range.Text = strDateText
    .Bookmarks("RT2").Range.Text = txtRT21
    .Bookmarks("RT1").Range.Text = txtRT11
    .SaveAs ("I:\Ryan Plew\2010Q1\401(k) FS LS Architect A2.doc")
End With

With Document("I:\Ryan Plew\401(k) FS LS Architect A3.doc")
    .Bookmarks("QYD").Range.Text = strDateText
    .Bookmarks("RT2").Range.Text = txtRT21
    .Bookmarks("RT1").Range.Text = txtRT11
    .SaveAs ("I:\Ryan Plew\2010Q1\401(k) FS LS Architect A3.doc")
End With

End Sub

 

[fumei]

I do not know. The Document("name") must be open. Is it?

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry

 

[RP1America]

Hmmm...well now that makes sense. Can't believe I bypassed that.

Oh wow though, so I'm going to have to have VB open each document in order to populate the bookmarks and resave??

I will have over 100 documents to do this with. Seems like Word would not be able to handle opening all of those documents at once.

 

[fumei]

Oh wow though, so I'm going to have to have VB open each document in order to populate the bookmarks and resave??

Yes, you absolutely, positively, will have to open them. Did you really think you could write to an unopened Word file????? Or any file for that matter. Even using FreeFile on a text file, you are still opening it.

I will have over 100 documents to do this with. Seems like Word would not be able to handle opening all of those documents at once.

At once? No, that is probably NOT a good idea. You do not state your hardware...but it does not matter. I would not trust Word to process 100 files at once.

Even sequentially, 100 individual files may be pushing things. It depends. Word has vastly improved its memory handling, but it still can get clumsy after a while. If it was 20 files I would say no problem. However, 100 files opened, actioned, saved...maybe yes, maybe no. The issue is that Word always creates a temp file and it is still a little sloppy about cleaning up after itself. Although I do not know if this is improved with 2007, as I do not use it.

Bottom line though is, yup, ya gotta open them. You can not write to a bookmark in an unopened file. Nor can you do a SaveAs...if it is not there (opened) to save!


"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry

 

[RP1America]

Okay...actual document count is 246. Haha...

Do you think Pausing the Timer for a few seconds every 20 docs or so might be beneficial?

If so, do you know of a good way to accomplish this?

 

[macropod]

Hi RP1America,

The filecount is necessarily a big issue.

You could probably replace your two With .. End With groups with, say 'Call ProcessFolder', which could something like:

Sub ProcessFolder()
Dim StrInDir As String, StrOutDir As String, StrFilePrefix As String, _
  StrInFile As String, StrOutFile As String, i As Integer
StrInDir = GetFolder(Title:="Find the Input Folder", RootFolder:=&H0)
StrOutDir = StrInDir & "\2010Q1"
StrInFile = Dir(StrInDir & "\*.doc")
Do While StrInFile <> ""
  StrOutFile = StrOutDir & StrInFile
  Documents.Open FileName:=StrInDir & "\" & StrInFile, ConfirmConversions:=False, ReadOnly:=False, _
  AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", Revert:=False, _
  WritePasswordDocument:="", WritePasswordTemplate:="", Format:=wdOpenFormatAuto
  With Documents(StrInFile)
    If .Bookmarks.Exists("QYD") Then .Bookmarks("QYD").Range.Text = strDateText
    If .Bookmarks.Exists("RT2") Then .Bookmarks("RT2").Range.Text = txtRT21
    If .Bookmarks.Exists("RT1") Then .Bookmarks("RT1").Range.Text = txtRT11
    If .Saved = False Then
      .SaveAs StrOutFile, FileFormat:=wdFormatDocument, AddToRecentFiles:=False
      StrOutFile.Close
    Else
      .Close
    End If
  End With
  StrInFile = Dir
Loop
End Sub

Cheers
[MS MVP - Word]

 

[fumei]

I will agree with macropod. Generally, I have found that Word uses Dir quite well. Again depending on your actual situation, it should be able to process 246 files - if all you are doing is those wee three bookmark values - in good order.

The deciding issue is the size of the files. If they are "normal" size .doc files, then it should be fine. I have done similar type processing on 300 - 400 files in a folder using Dir.

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry