Wild Cards with Documents.Open( )?

[Andrew Cushen]

Hi all-

I have a series of Word 2000 documents I need to open from
Excel 2000, using VBA & Word 2000 Automation, to extract
some data from; the data will go into the Excel
spreadsheet. The problem is, the names of the documents
vary. They are in this general format:

10020 abcd.doc
10021 jky.doc
10022 fden.doc

etc. The numbers are consecutive, but the text at the end
of the filename varies, as does its length- though it's
never longer than 4 letters. The number sequence never
repeats, and the numbers are unique. A filespec
like: "10020*.doc" would be what I need to open them.

My problem, as you may have guessed, is that the
Documents.Open() command wants an exact filename, not a
name with wildcards.

Does anyone know of a method by which I could open the
files, using wildcards or something similar?

TIA,

-Andrew

 

[DA]

Hi Andrew

Use the FileSearch property to find the files you want
and collect them in an array.

For example:
Private Sub GetDocs()
Dim lngCounter As Long
Dim astrFileList() As String

With Application.FileSearch
'This would look for all docs matching 10*.doc
'You could also use a counter value here if you want
'to collect a certain range

.FileName = "10*.doc"
.LookIn = "C:\tmp"
.Execute

If .FoundFiles.Count > 0 Then
ReDim astrFileList(.FoundFiles.Count - 1)
Else
MsgBox "No Files Found"
End If
For lngCounter = 0 To .FoundFiles.Count - 1
astrFileList(lngCounter) = _
.FoundFiles(lngCounter + 1)
Next lngCounter
End With
End Sub
--------

Once you've got the array filled, just read them out as
required and open your documents as usual.

Hope that's helped.
Dennis.

 

[Andrew Cushen]

Thanks Dennis,

It's not exactly what I was hoping for, but if it does the
trick...
I'll give it a shot.

Thanks.

-Andrew

 

[Peter Hewett]

Hi Andrew Cushen

If that's not what you want here's another version:

Public Function OpenWordDoc(ByVal strPath As String, _
ByVal strFileName As String) As Word.Document
Dim strFileFound As String

strFileFound = Dir$(strPath & strFileName)
If LenB(strFileFound) > 0 Then
Set OpenWordDoc = Documents.Open(strPath & strFileFound)
End If
End Function

You call it like this:
Dim docNew As Word.Document

Set docNew = OpenWordDoc("c:\myfiles\", "10020*.doc")

The only constraints are that the number part of the file name must be unique or else
unpredictable results will occur. Likewise you must pass in the Path part of the file
name separately.

HTH + Cheers - Peter

 

[Andrew Cushen]

Peter-

Thanks, that's exactly what I was looking for- but in the
meantime, I got Dennis' version to do what I wanted. I
have done a fair amount of Outlook VBA programming, and
automating Outlook from VB6, but Word & Excel VBA is
pretty new to me.

FWIW, I had a bunch of Word docs that were invoices,
created from templates with fields, from which I needed to
pull the invoice #, client name, and the invoice date,
then place those into an Excel spreadsheet, in order, each
on their own line, for tax purposes.

It turned into an adventure when I realized that the
person who created the template used a formfield for the
date that automatically put today's date in- today being
the day you opened it! So that was worthless; it gave me
today's date, not the date the invoice was created. Also
the files had been moved around, so the file creation date
was no good. I wound up getting the "file modified" date
and using that. Then I discovered that midway through the
invoices, the template creator discovered his/her error
& "fixed" it; not to mention making other changes, like
changes to the field names! Ugh! So I added "If"
statements to pull dates from the invoices where the dates
were valid, and changed my code that relied on field names
to code that searched for the words immediately preceeding
the info I needed, then moved the range to get the info.
THEN I discovered 2 invoices with the same #...

Suffice to say I pulled it together, though a small amount
of manual editing was needed afterwards, and there is
NOTHING more satisfying than running code like this and
seeing the Word files open & close one after the other,
followed by the data appearing, line after line, in the
Excel spreadsheet, without the errors that result from
manual copying, as if by magic! The client was suitably
amazed.

Thanks to both Dennis & Peter for their help in automating
what otherwise would have taken DAYS of error-prone manual
copy-and-paste work. The client can now do his taxes
before his time extension runs out, and I have moved the
client to a "real" invoicing program so this won't happen
again!

-Andrew
========================================================