Code error when Opening Workbook

[CT Witter]

Below is my code. I get an error when the code tries to do it's second loop and open the second book. (marked here by ********ERROR********************************) The error I receive is Run Time error '9', Subscript out of range.

Thanks!!

CODE:


Sub Autpen()
Dim var1
Dim counter, Int2 As Integer
Dim var2, var3 As String
Int2 = 1
'Prepare Excel for this procedure:
Application.ScreenUpdating = False
ActiveWindow.WindowState = xlMaximized


'Dialogue box to ask user if they are ready to run today's Consignment Report:
Range("A1").Select

Msg = "Click YES to Format and Update with New Data," & vbCrLf & "NO to Cancel and view old Data!"
Style = 4 + 32
Title = "Welcome to the ''Consignment Report'' !!"
Response = MsgBox(Msg, Style, Title)

If Response = 6 Then

ActiveWindow.FreezePanes = False
Cells.Select
Selection.UnMerge
Selection.EntireRow.Hidden = False
Selection.EntireColumn.Hidden = False
Selection.ClearContents
Range("A1").Select

'IMPORT NEW DATA FROM Consignment FILE

'Prepare Excel for the import:
Application.EnableEvents = False
Application.DisplayAlerts = False



counter = InputBox("Please Enter Number of Consignment Sheets!", "Number of Sheets?!")

Do Until Int2 > counter

'FILE IMPORT From the Capac.xls

'First, see if you have the file open; if not then open it:
var4 = "Consignment" & Int2
On Error GoTo b:

********ERROR********************************
Windows(var4).Activate

********ERROR********************************
'If you do not have the file open there will be an error and it will goto b:
GoTo c:
b:
ChDir "C:Consignment"
Workbooks.Open FileName:="C:Consignment" & var4
'This will open the file
c:

'Now when we get to c: the file is open either way so we activate it:
Windows(var4).Activate
'Then select the Capacity Report worksheet...
Sheets(var4).Select
'then prepare the sheet:
ActiveWindow.FreezePanes = False
Cells.Select
Selection.UnMerge
Selection.EntireRow.Hidden = False
Selection.EntireColumn.Hidden = False
'...then select the entire sheet...


Columns("D:D").Select
Selection.Find(What:="2PT1", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
ActiveCell.Select
ActiveCell.Offset(-1, 0).Select
var1 = ActiveCell.Row
Rows("1:" & var1).Select
Selection.EntireRow.Select

'...and then copy the entire sheet's range onto the clipboard.
Selection.Copy

'Next, re-activate the CapacityReport file:
Windows("Consignment_Report.xls").Activate
'Then select the destination worksheet...
Sheets("Sheet1").Select
'...then select the entire sheet's range to copy...
'Cells.Select
'...and PSV from Capac.xls's Capacity Report sheet
'into CapacityReport.xls's CapacityReport sheet
ActiveSheet.Paste
Range("A1").Select
Application.CutCopyMode = False

'Close Capac file because we don't need it anymore.
Windows(var4).Activate
ActiveWindow.Close

'Now the CapacityReport file has today's data.
'Re-activate the CapacityReport file:
Windows("Consignment_Report.xls").Activate


Call Processor

Int2 = Int2 + 1
var4 = " "
Loop


'Now save
ActiveWorkbook.Save



'Inform the user that the macro is complete.
MsgBox prompt:="Report is complete.", _
Title:="Capacity Report Finished.", _
Buttons:=64


'Now, if the user way back when clicked No to the original question
'about if they want to run the Report macro, here's
'what happens to them:
Else

MsgBox prompt:=("Click ''OK'' to enter the file." & vbCrLf & "(Note: This is the data from the last time your ran this report." & vbCrLf & "To update, Re-run and select YES!)"), _
Title:="You Have Selected No!!", _
Buttons:=vbExclamation

End If

Range("A1").Activate
ActiveCell.End(xlToLeft).Select
ActiveWindow.ScrollColumn = 1

'Reset Excel back to the way it was before it was open:
Application.CutCopyMode = False
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True


End Sub


Sub ActivateNextBlankDown()
ActiveCell.Offset(1, 0).Select
Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Sub Processor()


'Return to Beginning and Process Phase1

Range("A1").Select
Columns("A:F").Select
Selection.Delete Shift:=xlToLeft
Columns("F:H").Select
Selection.Delete Shift:=xlToLeft
Range("C1").Select
var2 = ActiveCell.Value
Range("E1").Select
var3 = ActiveCell.Value
Columns("A:A").Select
Selection.Find(What:="2*", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
ActiveCell.Select
ActiveCell.Offset(-1, 0).Select
var1 = ActiveCell.Row
Rows("1:" & var1).Select
Selection.EntireRow.Select
Selection.Delete Shift:=xlToTop
Columns("B:C").Select
Selection.Delete Shift:=xlToLeft
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.Value = var2
Range("B1").Select
ActiveCell.Value = var3
Range("A1").Activate
Call ActivateNextBlankDown
ActiveCell.Offset(2, 0).Select


End Sub

 

[Russell Hauf]

Hi CT, welcome to the board!

I would do something like what I have below. I think your error is that you have "GoTo c:" right below "b:", where you maybe wanted "On Error GoTo c:"(?).<pre><font color='#000000'><font color='#000080'>Sub</font> Autpen()<font color='#000080'>On</font><font color='#000080'>Error</font><font color='#000080'>GoTo</font> HandleErr<font color='#008000'>' code</font>

Windows(var4).Activate<font color='#008000'>' rest of code</font>

ExitHere:<font color='#000080'>Exit</font><font color='#000080'>Sub</font>

HandleErr:<font color='#000080'>Select</font><font color='#000080'>Case</font> Err.Number<font color='#000080'>Case</font> 9
Workbooks.Open var4
Resume Next<font color='#000080'>Case</font><font color='#000080'>Else</font>
MsgBox Err.Description
Resume ExitHere<font color='#000080'>End</font><font color='#000080'>Select</font><font color='#000080'>End</font><font color='#000080'>Sub</font></font></pre>

HTH,

Russell
This message was edited by Russell Hauf on 2002-09-23 11:40

 

[Russell Hauf]

Sorry for the short message, I was called away during the last one. The

Resume Next

line in the error handler tells the code to go to the line after the line where the error was.

I hope this helps,

Russell

 

[CT Witter]

Russell -

Thanks for your suggestion but am still getting an error I believe with the Activate method. If I comment out the line, or change based on your solution..the code now errors at

.....
'First, see if you have the file open; if not then open it:
var4 = "Consignment" & Int2
On Error GoTo b:
'Windows(var4).Activate
'If you do not have the file open there will be an error and it will goto b:
GoTo c:
b:
ChDir "C:Consignment"
Workbooks.Open FileName:="C:Consignment" & var4
'This will open the file
c:

'Now when we get to c: the file is open either way so we activate it:

*********ERRROR**************************
Windows(var4).Activate
*********ERRROR**************************
'Then select the Capacity Report worksheet...
Sheets(var4).Select
'then prepare the sheet:
ActiveWindow.FreezePanes = False
Cells.Select
Selection.UnMerge
Selection.EntireRow.Hidden = False
Selection.EntireColumn.Hidden = False
'...then select the entire sheet...

 

[Russell Hauf]

Is your workbook being opened? One thing I think you need is a backslash after your folder (by the way, you don't need the ChDir statement before the open statement) - before the filename. So if your file is named File1.xls, you'd want:

Workbooks.Open "C:FolderFile1.xls"

Or,

Workbooks.Open "C:Consignment" & var4

If this does not help, could you post the code in it's entirety - just as you ran it? (Without the ****ERROR**** parts, and again, including the entire code).

EDIT: If you quote this message, you will see that there is a backslash after "Consignment" in the C:Consignment & var4 line (I left the quotes off in hopes that it will show up without them, but obviously you need them in your code).
This message was edited by Russell Hauf on 2002-09-24 09:05

 

[CT Witter]

Russell:

Still no luck, it confuses me because it run ok once for Consignment1 as the var, then when it gets to the second file it errors out.

Here is the entire code again. Thank you very much for your help!


************CODE*************************


Sub Autpen()
Dim var1
Dim counter, Int2 As Integer
Dim var2, var3 As String
Int2 = 1
'Prepare Excel for this procedure:
Application.ScreenUpdating = False
ActiveWindow.WindowState = xlMaximized


'Dialogue box to ask user if they are ready to run today's Capacity Report:
Range("A1").Select

Msg = "Click YES to Format and Update with New Data," & vbCrLf & "NO to Cancel and view old Data!"
Style = 4 + 32
Title = "Welcome to the ''Consignment Report'' !!"
Response = MsgBox(Msg, Style, Title)

If Response = 6 Then

ActiveWindow.FreezePanes = False
Cells.Select
Selection.UnMerge
Selection.EntireRow.Hidden = False
Selection.EntireColumn.Hidden = False
Selection.ClearContents
Range("A1").Select

'IMPORT NEW DATA FROM Consignment FILE

'Prepare Excel for the import:
Application.EnableEvents = False
Application.DisplayAlerts = False



counter = InputBox("Please Enter Number of Consignment Sheets!", "Number of Sheets?!")

Do Until Int2 > counter

'FILE IMPORT From the Capac.xls

'First, see if you have the file open; if not then open it:
var4 = "Consignment" & Int2
On Error GoTo b:
'Windows(var4).Activate
'If you do not have the file open there will be an error and it will goto b:
GoTo c:
b:
ChDir "C:Consignment"
Workbooks.Open FileName:="C:Consignment" & var4
'This will open the file
c:

'Now when we get to c: the file is open either way so we activate it:
Windows(var4).Activate
'Then select the Capacity Report worksheet...
Sheets(var4).Select
'then prepare the sheet:
ActiveWindow.FreezePanes = False
Cells.Select
Selection.UnMerge
Selection.EntireRow.Hidden = False
Selection.EntireColumn.Hidden = False
'...then select the entire sheet...


Columns("D:D").Select
Selection.Find(What:="2PT1", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
ActiveCell.Select
ActiveCell.Offset(-1, 0).Select
var1 = ActiveCell.Row
Rows("1:" & var1).Select
Selection.EntireRow.Select

'...and then copy the entire sheet's range onto the clipboard.
Selection.Copy

'Next, re-activate the CapacityReport file:
Windows("Consignment_Report.xls").Activate
'Then select the destination worksheet...
Sheets("Sheet1").Select
'...then select the entire sheet's range to copy...
'Cells.Select
'...and PSV from Capac.xls's Capacity Report sheet
'into CapacityReport.xls's CapacityReport sheet
ActiveSheet.Paste
If Int2 = 1 Then
Range("A1").Select
Else
var5 = "A" & var1
Range(var5).Select
End If
Application.CutCopyMode = False

'Close Capac file because we don't need it anymore.
Windows(var4).Activate
ActiveWindow.Close

'Now the CapacityReport file has today's data.
'Re-activate the CapacityReport file:
Windows("Consignment_Report.xls").Activate


Call Processor

Int2 = Int2 + 1
var4 = " "
Loop


'Now save
ActiveWorkbook.Save



'Inform the user that the macro is complete.
MsgBox prompt:="Report is complete.", _
Title:="Capacity Report Finished.", _
Buttons:=64


'Now, if the user way back when clicked No to the original question
'about if they want to run the Report macro, here's
'what happens to them:
Else

MsgBox prompt:=("Click ''OK'' to enter the file." & vbCrLf & "(Note: This is the data from the last time your ran this report." & vbCrLf & "To update, Re-run and select YES!)"), _
Title:="You Have Selected No!!", _
Buttons:=vbExclamation

End If

Range("A1").Activate
ActiveCell.End(xlToLeft).Select
ActiveWindow.ScrollColumn = 1

'Reset Excel back to the way it was before it was open:
Application.CutCopyMode = False
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True


End Sub


Sub ActivateNextBlankDown()
ActiveCell.Offset(1, 0).Select
Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Sub Processor()


'Return to Beginning and Process Phase1

Range("A1").Select
Columns("A:F").Select
Selection.Delete Shift:=xlToLeft
Columns("F:H").Select
Selection.Delete Shift:=xlToLeft
Range("C1").Select
var2 = ActiveCell.Value
Range("E1").Select
var3 = ActiveCell.Value
Columns("A:A").Select
Selection.Find(What:="2*", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
ActiveCell.Select
ActiveCell.Offset(-1, 0).Select
var1 = ActiveCell.Row
Rows("1:" & var1).Select
Selection.EntireRow.Select
Selection.Delete Shift:=xlToTop
Columns("B:C").Select
Selection.Delete Shift:=xlToLeft
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.Value = var2
Range("B1").Select
ActiveCell.Value = var3
Range("A1").Activate
Call ActivateNextBlankDown
ActiveCell.Offset(2, 0).Select



End Sub

 

[Russell Hauf]

I still say that you need a backslash in the Workbooks.Open line -- after Consignment.

Like this:<pre><font color='#000000'>
Workbooks.Open Filename:="C:Consignment" & var4</font></pre>

(There should be only one backslash at each place there are 2)

Are you stepping through the code? If not, do so. With the way you have it now, it shouldn't open any files, unless you have one named:

ConsignmentConsignment1.xls

Try putting the backslash on and see if it works. Again, you don't need the ChDir line.

Let me know what happens after you put the backslash on,

Russell
This message was edited by Russell Hauf on 2002-09-24 09:20