Problem opening Workbook files in Macro

[Nils Titley]

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

 

[michael.beckinsale]

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

 

[Dave Peterson]

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]

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

 

[Dave Peterson]

This portion of your code builds the array of filenames:

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

After the last file is found, you do one more Dir() call. Then in the next
line, you add one to the numfiles accumulator--whether you found another file or
not!

So one way to fix it is to only add one if you've found another file:

FilesInPath = Dir()
if filesinpath <> "" then
NumFiles = NumFiles + 1
end if
Loop

====
Another way would be to avoid the last number in the loop:

Do While (NumDone <= NumFiles -1)

I like the top one better.

 

[Nils Titley]

Dave,

That made it work. My debug logic was not right so I missed it.

I need some other help. I have looked at lots of examples some rom Ron De
Bruin's website but I am not finding what I need. I have a macro that people
have helped me with. It processes with 1400 rows and writes the data to the
bottom of the sheet. It works fine. I know have to read from 9 to 60 files
and process them and write the results to the same sheet to create a report.
You can see that I can create a list of the name of the files that are in my
directory. What I am having a problem with is ( and I am not sure I will use
the right terms) accessing or pointing to the workbook that has the data,
they will always be sheet 1 and then writing the results to another workbook,
sheet 1.

Can you provide me some code that might getting me started. I am just not
seeing how to make this work.

Thanks for your help.

 

[Dave Peterson]

Without knowing what you're doing, you may want to dedicate a folder with all
the input files and then look at the code in Ron's site once more:

http://www.rondebruin.nl/copy3.htm
http://www.rondebruin.nl/fso.htm