I am running the following code, and let's say that I hit Mice instead of Ice for the varBook name
[VBA]
varBooks = Array("Fire", "Mice")
For Each varBook In varBooks
On Error Resume Next
Set WB = Nothing
Set WB = Workbooks.Open(Filename:="C:\Testing" & varBook)
On Error GoTo 0
With WB
WB.SaveAs Filename:="C:\Daily\"
WB.Close
End With
Call "RunTimE"
[/VBA]
Instead of just skipping and moving on to the Function RunTimE" It will throw an error of "Object Variable or With Block Variable Not Set" highlighting the line of WB.SaveAs
The code debugs fine, but just won't take my error handling
Moving the On Error GoTo 0 to After the With blew up my procedure, it started spooling to print a large workbook (which was the last workbook that was opened) and I had to reboot. I ran the code twice to verify it wasnt just a fluke, and it happened both times.
Sub untested_example()
Dim varBooks As Variant
Dim varBook As Variant
Dim WB As Workbook
'// Include the file extension. //
varBooks = Array("Fire.xls", "Mice.xls")
For Each varBook In varBooks
On Error Resume Next
Set WB = Nothing
'// Include the trailing seperator //
Set WB = Workbooks.Open(Filename:="C:\Testing\" & varBook)
On Error GoTo 0
'// Test to see if we set a reference before working against it. Also, //
'// if C:\Daily is a folder, you need the WB's new name included //
If Not WB Is Nothing Then
With WB
WB.SaveAs Filename:="C:\Daily\"
WB.Close
End With
'// Ditch the quote marks //
Call RunTime
End If
Next
End Sub
I am still struggling on this...my coding works to perfection if varBook does exist. However, if varBook does not exist, the code will go crazy and automatically jump to Step_Three and try to open that workbook, and once that worbook is opened it will start spooling each worksheet in that workbook to print!!!
What in my code is wrong? What I am trying to do is add in error checking in Part_One, Part_Two and part_Three so that if a varBook is entered that does not exist it will stop executing that module and move to the next module.
So if varBook "Fire" does not exist in Part_One, that's fine, let's carry on and see if it exists in Part_Two and Part_Three.
Can one of the guru's point out what I have coded here wrong?!
Check if the workbook exists or is open before you try opening it. Use Dir() to determine if it exists. Use this to see if it is open.
[VBA]Function IsWorkbookOpen(stName As String) As Boolean
Dim Wkb As Workbook
On Error Resume Next ' In Case it isn't Open
Set Wkb = Workbooks(stName)
If Not Wkb Is Nothing Then IsWorkbookOpen = True
'Boolean Function assumed To be False unless Set To True
End Function[/VBA]
As I said, you have several issues to address. Even this might not consider all but will get you closer. This is how I work, I do what I can and then fix what I forgot to consider.
[VBA]Sub test()
Dim varBooks() As Variant, WB As Workbook
Dim vPath As String, vPath2 As String, fn As String
For Each varbook In varBooks
varBooks = varBooks & ".xls"
fn = vPath & varBooks
If Dir(fn) = "" Then GoTo NextVarBook
If IsWorkbookOpen(fn) Then
Set WB = Workbooks(varBooks)
Else
Set WB = Workbooks.Open(fn)
End If
If WB Is Nothing Then
MsgBox fn, vbCritical, "Error Opening File"
GoTo NextVarBook
End If
With WB
.SaveAs vPath2 & varBooks
.Close
End With
RunTimE
NextVarBook:
Next varbook
End Sub
Function IsWorkbookOpen(stName As String) As Boolean
Dim Wkb As Workbook
On Error Resume Next ' In Case it isn't Open
Set Wkb = Workbooks(stName)
If Not Wkb Is Nothing Then IsWorkbookOpen = True
'Boolean Function assumed To be False unless Set To True
End Function[/VBA]
Kenneth did you take a look at the sample workbook I uploaded, or is this based off of my sample code? I uploaded a workbook showing my code in it's entirety since my coding isn't working that way the guru's here can see the full picture and understand what I am actually trying to do (hopefully)
Let me also add to my post that each module in the sample workbook I provided runs issue free if run individually. But when trying to run them all together is the issue.
Reply With Quote
