I am learning how to work with opening excel workbook files. I am getting a
run-time error 9, supscript out of range. I know what that means but I don't
see why I am getting that error.

There are 6 files in the directory and I am only processing 0-5 which is six.

Please comment.
' Fill the array (myFiles) with the list of Excel files in the folder
NumFiles = 0
Do While FilesInPath < ""
' Dynamically size MyFiles based on number of files
ReDim Preserve MyFiles(0 To NumFiles)
MyFiles(NumFiles) = FilesInPath
FilesInPath = Dir()
NumFiles = NumFiles + 1
Loop

' Loop through the files to process them
NumDone = 0

Do While (NumDone <= NumFiles)
' Subscript error at -
- Set MyBook = Workbooks.Open(MyPath & MyFiles(NumDone))
MsgBox NumDone
MyBook.Close savechanges:=False
NumDone = NumDone + 1
Loop

To process files that are in a folder, do you have to open them?

Thanks

Hi Nils,

You should post all the relevant code including your variable
declarations.

Without seeing the variable declarations l suspect that 'Set' is
causing your problem.

HTH

Michael

You didn't share where myPath got initialized. Are you sure it ends with a
backslash?

You didn't share your first Dir() statement, either.



Nils Titley wrote:

I am learning how to work with opening excel workbook files. I am getting a
run-time error 9, supscript out of range. I know what that means but I don't
see why I am getting that error.

There are 6 files in the directory and I am only processing 0-5 which is six.

Please comment.
' Fill the array (myFiles) with the list of Excel files in the folder
NumFiles = 0
Do While FilesInPath < ""
' Dynamically size MyFiles based on number of files
ReDim Preserve MyFiles(0 To NumFiles)
MyFiles(NumFiles) = FilesInPath
FilesInPath = Dir()
NumFiles = NumFiles + 1
Loop

' Loop through the files to process them
NumDone = 0

Do While (NumDone <= NumFiles)
' Subscript error at -
- Set MyBook = Workbooks.Open(MyPath & MyFiles(NumDone))
MsgBox NumDone
MyBook.Close savechanges:=False
NumDone = NumDone + 1
Loop

To process files that are in a folder, do you have to open them?

Thanks

--

Dave Peterson

Ok,

Here is all the code and thanks for the help. I know there are 6 files in
the folder and I know that it exists the do while correctly.



Sub LiftLoggerProcess()

' Declaration of Variables

Dim FilesInPath As String
Dim MyFiles() As String
Dim MyPath As String
Dim MyBook As Workbook
Dim BaseWks As Worksheet
Dim SourceRange As Range

Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Dim NumFiles As Integer
Dim NumDone As Integer

' Location of the Lift Logger Excel files for processing
MyPath = "c:\temp\Lift Logger\"

' Msgbox for No files in directory
Msg = "NO FILES, Add Files to the Lift Logger Folder " ' Define
message
Style = vbOKOnly + vbCritical + vbDefaultButton2 ' Define buttons
Title = "Lift Logger Process - ERROR MSG" ' Define title


' Check for Excel files in the Lift Logger directory
' If No files exit routine
FilesInPath = Dir(MyPath & "*.xl*")
If FilesInPath = "" Then
Response = MsgBox(Msg, Style, Title)
Exit Sub
End If

' Fill the array (myFiles) with the list of Excel files in the folder
NumFiles = 0
Do While FilesInPath < ""
' Dynamically size MyFiles based on number of files
ReDim Preserve MyFiles(0 To NumFiles)
MyFiles(NumFiles) = FilesInPath
FilesInPath = Dir()
NumFiles = NumFiles + 1
Loop

' Loop through the files to process them
NumDone = 0

Do While (NumDone <= NumFiles)

MsgBox " Before file" & ActiveWorkbook.Name

Set MyBook = Workbooks.Open(MyPath & MyFiles(NumDone))

MsgBox "The name of the active workbook is " & ActiveWorkbook.Name

MsgBox NumDone

MyBook.Close savechanges:=False

NumDone = NumDone + 1

Loop

' Notify user of the number of files processed
Msg = NumFiles & " FILES PROCESSED "
Style = vbOKOnly + vbInformation + vbDefaultButton1
Title = "Lift Logger Process - INFORMATION MSG"
Response = MsgBox(Msg, Style, Title)

End Sub


"Nils Titley" wrote:

I am learning how to work with opening excel workbook files. I am getting a
run-time error 9, supscript out of range. I know what that means but I don't
see why I am getting that error.

There are 6 files in the directory and I am only processing 0-5 which is six.

Please comment.
' Fill the array (myFiles) with the list of Excel files in the folder
NumFiles = 0
Do While FilesInPath < ""
' Dynamically size MyFiles based on number of files
ReDim Preserve MyFiles(0 To NumFiles)
MyFiles(NumFiles) = FilesInPath
FilesInPath = Dir()
NumFiles = NumFiles + 1
Loop

' Loop through the files to process them
NumDone = 0

Do While (NumDone <= NumFiles)
' Subscript error at -
- Set MyBook = Workbooks.Open(MyPath & MyFiles(NumDone))
MsgBox NumDone
MyBook.Close savechanges:=False
NumDone = NumDone + 1
Loop

To process files that are in a folder, do you have to open them?

Thanks