OpenWorkbook error

[Larry]

I wrote some code to inspect all workbooks in a file structure, but am
now getting an error "1004" on a workbook with an odd character in it.

The first thing I do is use a DIR command (through SHELL) to generate a
text file of all XLS files in the structure, then I loop through the
text file to open each workbook and do my processing.

The name of one of the workbooks is: Tubar=E2o.xls

But the output from the DIR command shows: Tubar=83o.xls

So, of course, when the OpenWorkbook statement tries to open this
workbook, which the text file says is there, it can't find it.

Does anyone know what I can do to get around this? And no, I can't
rename it, these files come from other sources and I have no control on
the name of the file.

TIA
Larry

[damorrison]

could you try to copy and and paste the name
into the code?

[Larry]

This code has to work on other workbooks that will be coming to us, so
others could have names with these characters in it too. We don't want
to have to do that for each one of them. But thanks anyway, that would
have solved this particular problem.

[Dave Peterson]

Maybe dropping the shell and just keeping track of the filenames while the code
is running would work. (I didn't test it, but it won't take long before you
know if it works with filenames with those funny characters.)


Option Explicit
Sub testme01()

Dim myNames() As String
Dim fCtr As Long
Dim myFile As String
Dim myPath As String

'change to point at the folder to check
myPath = "c:\my documents\excel\test"
If Right(myPath, 1) <> "\" Then
myPath = myPath & "\"
End If

myFile = ""
On Error Resume Next
myFile = Dir(myPath & "*.xls")
On Error GoTo 0
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If

'get the list of files
fCtr = 0
Do While myFile <> ""
fCtr = fCtr + 1
ReDim Preserve myNames(1 To fCtr)
myNames(fCtr) = myFile
myFile = Dir()
Loop

If fCtr > 0 Then
For fCtr = LBound(myNames) To UBound(myNames)
'code to process the files.
Next fCtr
End If

End Sub

Larry wrote:
>
> I wrote some code to inspect all workbooks in a file structure, but am
> now getting an error "1004" on a workbook with an odd character in it.
>
> The first thing I do is use a DIR command (through SHELL) to generate a
> text file of all XLS files in the structure, then I loop through the
> text file to open each workbook and do my processing.
>
> The name of one of the workbooks is: Tubarâo.xls
>
> But the output from the DIR command shows: Tubarƒo.xls
>
> So, of course, when the OpenWorkbook statement tries to open this
> workbook, which the text file says is there, it can't find it.
>
> Does anyone know what I can do to get around this? And no, I can't
> rename it, these files come from other sources and I have no control on
> the name of the file.
>
> TIA
> Larry

--

Dave Peterson

[Larry]

I had thought of that the first time I went through this process, but
that only gets the workbooks in the specified folder. I need all
workbooks in the top folder and all subfolders.

I have written code to recurse through folders before, but I thought
this method I was using now was quicker and easier. And it is, IF this
character problem wasn't an issue!

Thanks for the thought.

[Dave Peterson]

This is based on code that Bill Manville posted. But if the first version
didn't work with those funny characters, then this won't either (still untested
for those funny characters).

Option Explicit
Dim myFileNames() As String
Dim fCtr As Long
Sub DoTheWork()
fCtr = 0
Call GetAListOfFiles("c:\my documents\excel", "*.xls")
If fCtr > 0 Then
For fCtr = LBound(myFileNames) To UBound(myFileNames)
'MsgBox myFileNames(fCtr)
Next fCtr
End If
End Sub
Sub GetAListOfFiles(myFolder As String, myPattern As String)
Dim myFolders() As String
Dim dCtr As Long
Dim myFileName As String

If Right(myFolder, 1) <> "\" Then
myFolder = myFolder & "\"
End If

dCtr = 0
myFileName = myFolder & Dir(myFolder & myPattern, vbDirectory)
Do While myFileName <> myFolder
If Right(myFileName, 2) = "\." Or Right(myFileName, 3) = "\.." Then
' do nothing - GetAttr doesn't like these directories
ElseIf (GetAttr(myFileName) And vbDirectory) = vbDirectory Then
' add to local array of directories
dCtr = dCtr + 1
ReDim Preserve myFolders(1 To dCtr)
myFolders(dCtr) = myFileName
Else
' add to global array of files
fCtr = fCtr + 1
ReDim Preserve myFileNames(1 To fCtr)
myFileNames(fCtr) = myFileName
End If
myFileName = myFolder & Dir()
Loop

' now, for any directories in myFolders call self recursively
If dCtr > 0 Then
For dCtr = 1 To UBound(myFolders)
Call GetAListOfFiles(myFolders(dCtr) _
& Application.PathSeparator, myPattern)
Next dCtr
End If
End Sub

Larry wrote:
>
> I had thought of that the first time I went through this process, but
> that only gets the workbooks in the specified folder. I need all
> workbooks in the top folder and all subfolders.
>
> I have written code to recurse through folders before, but I thought
> this method I was using now was quicker and easier. And it is, IF this
> character problem wasn't an issue!
>
> Thanks for the thought.

--

Dave Peterson