View Full Version : [SOLVED:] Runtime error opening workbooks
skulakowski
01-24-2015, 02:38 PM
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
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
01-24-2015, 11:28 PM
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
01-25-2015, 09:54 AM
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
01-25-2015, 09:55 AM
Thanks. I'll add "DoEvents" and test.
skulakowski
01-25-2015, 09:58 AM
Sadly, DoEvents didn't help my code. Method 'Open' of object 'Workbooks' still fails when running macro while stepping through succeeds.
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
01-25-2015, 01:24 PM
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
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
01-25-2015, 03:47 PM
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
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
01-26-2015, 01:49 PM
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
01-26-2015, 01:54 PM
Have you checked if you have missing references?
Also export all the modules and import again.
Just trying to go over all possibilities here.
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?
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.