Using Dir command to loop through documents

[SteIT]

I am using Access to edit a number of word documents in a folder. I need to edit each file within the folder, save file (same name & same folder).

All works okay but am having trouble picking the next document within the folder to edit.

Using:
Do While Fname <> ""
open Fname, edit and save
Fname = Dir
loop

The same file is being picked each time, and not picking the next file. Is this because the file is being modified and saved in the same folder ?

 

[PHV]

What is the first call to the Dir function ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SteIT]

Sorry, missed this off.

Fname = Dir(<doc path string>)

Regards

 

[PHV]

Syntax error in your last post ...

 

[SteIT]

Not sure I know what you mean.

Fname = Dir(<doc path string>)

<doc path string> is a variable set to the path of the documents I opening/editing.

Thanks for the prompt response

 

[PHV]

Why not posting the REAL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Spooku777]

This all depends on the string in the first use of DIR.

The initial use of DIR should be for path only. Path and document name only returns that filename string and subsequent use of DIR returns nothing.

And make sure the FIRST use of DIR includes path only with "\" at the end of the string to return the first filename. Then each subsequent use of DIR returns the next filename

FName = Dir("C:\MyFoldername\")
Do While Fname <> ""
    open Fname, edit and save
    Fname = Dir
loop

Hope that helps

 

[SteIT]

Extract of code.
Do a lot of othere things within the macro

Dim newdocpath As String
Dim compname As String
Dim Fname As String
Dim objWord As Word.Application

newdocpath = "C:\RCS\" ' folder contains compname folders

compname = (CStr(Forms![Retainer Companies]!CompanyName))


Fname = Dir(newdocpath & compname & "\")


Do While Fname <> ""
filepath = newdocpath & compname & "\" & Fname
Set objWord = CreateObject("Word.Application")
With objWord
.Visible = True
.Documents.Open (filepath)

.ActiveDocument.Bookmarks("company").select
.Selection.Text = compname
.
.

.
.
.activeDocument.Close (wdSaveChanges)

Fname = Dir
loop
objWord.Quit

Works with only one document in the folder, i.e. on second pass of loop Fname = "".

Thanks

 

[PHV]

Why creating a Word.Application object on each iteration ?
What is the value of the Forms![Retainer Companies]!CompanyName control ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SteIT]

Thanks. I suppose I can keep Word application open, and then close it outside of the loop.

Forms![Retainer Companies]!CompanyName is a company name selected from listbox in access form.

I have assigned this to variable 'compname'.

All works okay on first document, but the next document within folder is not being selected, it selects the same one all the time.

Regards

 

[mintjulep]

Wouldn't it make sense to dim fname as a file object and use.

For each fname in directory
...

next fname

?

 

[Spooku777]

Note sure why DIR doesn't work except on the first one.

You could use FileSystemObject to list files. See sample code below (new bits highlighted in [red]red[/red])

Note
- there's a check to make sure it's a Word document before opening it the Word application.
- For loop is used rather than While since it's seems easier to assign the file object that way.

Dim newdocpath As String
Dim compname As String
Dim FilePath As String
Dim objWord As Object
    
newdocpath = "C:\RCS\"   ' folder contains compname folders
compname = (CStr(Forms![Retainer Companies]!CompanyName))
[red]    
Dim fso As Object
Dim fldr As Object
Dim fle As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set fldr = fso.GetFolder(newdocpath & compname)
[/red]    
Set objWord = CreateObject("Word.Application")
With objWord
    .Visible = True
    [red]For Each fle In fldr.Files
        FilePath = newdocpath & compname & "\" & fle.Name
        If LCase(fle.Type) = LCase("Microsoft Word Document") Then [/red]
            .Documents.Open (FilePath)
            .ActiveDocument.Bookmarks("company").Select
            .Selection.Text = compname
            .
            .
            .
            .ActiveDocument.Close (wdSaveChanges)
        [red]End If
    Next[/red]
End With
objWord.Quit

This is an alternative to DIR that may solve your problem.

 

[combo]

[!]Fname[/!] = Dir(newdocpath & compname & "\")
 
    Do While Fname <> ""
        [!]filepath[/!] = newdocpath & compname & "\" & [!]Fname[/!]

Aer you sure that so build filepath exists?


combo