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 (http://www.vbaexpress.com/forum/member.php?256-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?