Runtime error opening workbooks

[skulakowski]

My files are in the Zips directory. They're named "Seq" + a sequence number + ".xls".

If I step through the Workbooks.Open line, this code runs perfectly. If I try to run the complete code, it fails at "Workbooks.Open Filename:=ThisSeq" with run-time error 1004. Does anybody have any idea what is wrong?

dim ThisSeq as String

q = 34
ThisStop = 86

For i = q To q + ThisStop

ThisSeq = "C:\Zips\Seq" & i & ".xls"

Workbooks.Open Filename:=ThisSeq

next i

[pike]

hi skulakowski,
If the file path and the files exist and they are not corrupt and they are not open then

Dim ThisSeq As String
q = 34
ThisStop = 86
For i = q To q + ThisStop
ThisSeq = "C:\Zips\Seq" & i & ".xls"
Workbooks.Open Filename:=ThisSeq
DoEvents
Next i

[fredlo2010]

Hello,

are you sure you want this line?

For i = q To q + ThisStop

Does not make too much sense to me. This would be most likely what you want.

For i = q To ThisStop

What's the last file number you want to open 86 or 120?

Thanks

[skulakowski]

Oh. And yes, c:\Zips\Seq is the active directory.

No, I do want q + ThisStop as I'm working my way through the Seq files in chunks. Seq120 is the last file in this chunk. But that's not where the problem is. The i counter counts perfectly.

[skulakowski]

Thanks. I'll add "DoEvents" and test.

[skulakowski]

Sadly, DoEvents didn't help my code. Method 'Open' of object 'Workbooks' still fails when running macro while stepping through succeeds.

[GTO]

Just a quick/easy/temp check...

Adjusting for your path and filename parameters, try:

Option Explicit
  
Sub example()
Dim ThisSeq As String
Dim q As Long, i As Long, ThisStop As Long
  
  q = 1
  ThisStop = 86
  
  For i = q To q + ThisStop
    ThisSeq = "F:\vbax\vbax-51605-Runtime-error-opening-workbooks\Book" & i & ".xls"
    If WorkbookExists(ThisSeq) Then
      Workbooks.Open Filename:=ThisSeq
    Else
      Debug.Print "'" & ThisSeq & "' does not exist."
    End If
  Next I
  
End Sub
  
Private Function WorkbookExists(ByVal FileFullName As String) As Boolean
  WorkbookExists = CreateObject("Scripting.FileSystemObject").FileExists(FileFullName)
End Function

Does anything get printed to the immediate window?

Mark

[skulakowski]

Mark, Thanks for helping with this strange error.

All ThisSeq# files exist. I reset my counters for 1 to 5.

Funny thing is that running the macro a) opens and processes Seq1.xls; b) opens and processes Seq2.xls; then c) constructs the name of Seq3.xls (which does exist) and fails to open Seq3 at this Workbooks.Open within the error trap.

It's a program. Seq3 shouldn't fail if Seq1 and Seq2 process correctly. (At least I think I know that programs can't work 'sometimes'.)

And thanks for the nifty WorkbookExists function.

Susan

[pike]

skulakowski

What happens if you run this code.. it should open all the xls workbooks in the folder

Sub OpenFiles()
    Dim MyFolder As String
    Dim MyFile As String
    MyFolder = "C:\Zips\"
    MyFile = Dir(MyFolder & "*.xls")
    Do While MyFile <> ""
        Workbooks.Open Filename:=MyFolder & "\" & MyFile
    End If
    MyFile = Dir
Loop
End Sub

if it does not error you can add an if statement to only open the correct numered files

[fredlo2010]

Hello,

Maybe your workbook 3 is corrupt. Can you open the workbook without the macro?

Try this

Option Explicit

Sub OpenWorkbook()
    
    Dim ThisSeq As String
    Dim ThisStop As Long
    Dim q As Long, i As Long
    
    q = 34
    ThisStop = 86


    For i = q To q + ThisStop
        ThisSeq = "C:\Zips\Seq" & i & ".xls"
        Workbooks.Open Filename:=ThisSeq, ReadOnly:=True, Corruptload:=XlCorruptLoad.xlRepairFile
    Next i
    
End Sub

Thanks

[pike]

or another way

Sub OpenFiles()
    Dim MyFolder As String
    Dim MyFile As String
    MyFolder = "C:\Zips\"
    MyFile = Dir(MyFolder & "*.xls")
    Do While MyFile <> ""
        If MyFile Like "Seq*.xls" Then
            If ExtractifNumber(FileName) > 33 And ExtractifNumber(FileName) < 121 Then
                Workbooks.Open FileName:=MyFolder & "\" & MyFile, ReadOnly:=True, Corruptload:=XlCorruptLoad.xlRepairFile

            End If
        End If
        MyFile = Dir
    Loop
End Sub
Function ExtractifNumber(FileName As String)
    Dim iCount As Integer, lNum As Long, bln As Boolean
    For iCount = 1 To Len(FileName)
        If Mid(FileName, iCount, 1) Like "#" Then bln = True: lNum = lNum & Mid(FileName, iCount, 1)
    Next iCount
    ExtractifNumber = IIf(bln, lNum, "Nil")
End Function

[skulakowski]

No, the files aren't corrupt because I can step through the code properly. (In fact, that's what I did to process the files.)

Thank you all very much. I'm going to mark this as "solved" because the code works by stepping through it (so code/files aren't the problem) and clearly there's some internal difference between stepping and running an Excel macro that will remain a Microsoft mystery.

[fredlo2010]

Have you checked if you have missing references?

Also export all the modules and import again.

Just trying to go over all possibilities here.

[pike]

Last two atemps first use ChDir path to C:\Zips\ something that use to cause a problem when open files and

second what happens if you add 'On Error Resume Next' statement before the file is opened code?