Coding To Open/Format/Save/Close All Files In Single Directory

[Graham L]

Hi,

My problem surounds the fact that I have 100 + files in the following
directory

C:\My Documents|Import
The first few filenames for example are ABEG, ABMN, ABXX

The files all have the extension .rpt as they have been imported via ftp
routine. What I'm aiming to do is open each file in the directory in turn,
perform identical formatting and editing in each file (for which I have code
that works!), and save the file as a word document in the same directory as
above (or preferably one called C:\My Documents|Reports). I have cobbled
together some coding, but am basically stuck and any input provided would be
incredibly welcome.
The coding seems to open the first file "ABEG", but somewhere in the routine
the filename is then lost and the routine ends up doing a loop, saving the
file with name "False" and resulting in a forced quit to get out of word. I'm
also not sure what the coding would be that ensures the macro ends normally
once the routine has been performed on each file in the directory once only.
I'd be grateful if someone could have a look at the code below and advise
accordingly

Thanks
Graham


Sub RunNR6()

Dim myFile As String
Dim PathToUse As String
Dim myDoc As Document

PathToUse = "\\FINPC13\SHARE FIN01\NR6 Draft .rpt\"

myFile = Dir$(PathToUse & "*.rpt")

While myFile <> ""

Set myDoc = Documents.Open(PathToUse & myFile)

'then perform required editing and formatting

myFile = Left$(myFile, Len(myFile) - 3) = "doc"
ActiveDocument.SaveAs FileName:=myFile, FileFormat:=wdFormatDocument
ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges

Wend


myFile = Dir$()


End Sub

 

[Jonathan West]

You asked a very similar question yesterday. Did you not see my answer?

--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org

 

[Helmut Weber]

Hi Graham,

I'd suggest to use "dir" only once
put the filenames in an array,
and work you way through it.

I think the key phrase is "dir is reentrent",
which means, that "dir" reads (more or less)
all filenames in again, but may have buffered
it's first run, and gets confused, if the
number of files have changed.


Greetings from Bavaria, Germany

Helmut Weber, MVP, WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Graham L]

I did thanks, and incorporated your answer, but unfortunately overall, my
problem is not resolved possibly due to other parts of the coding, so I
thought I would re-post the entire scope of the issue rather than just
highlighting the latter part where I thought the problem was arising. I am
currently at the tearing my hair out stage!

 

[David Sisson]

myFile = Left$(myFile, Len(myFile) - 3) = "doc"

There are two equal signs, myFile is the result of the equation, hence
the False in the filename.

I assume you're trying to get the base name of the rpt file.

myFile = Left$(myFile, Len(myFile) - 3) & "doc"

I agree with Helmut in using an array. It's more work, but you have a
known list of files at the beginning of the routine.

 

[Helmut Weber]

Hi David,

you scored 100 points. ;-)

There are two equal signs, myFile is the result of the equation, hence
the False in the filename.

how can one overlook that?

Greetings from Bavaria, Germany

Helmut Weber, MVP, WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[kandp01]

I know this is an old thread but I only just found it (during my time of need)

Looking at teh code eg below, the "myFile = Dir$()" at the bottom should
probably read "myFile = Dir$" and it should not be outside the while/wend
loop.

My macro would only read one file until I changed it as per above.

Cheers
Peter