Code error when Opening Workbook

CT Witter

MrExcel MVP
Joined
Jul 7, 2002
Messages
1,212
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 Auto_Open()
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
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Russell Hauf

MrExcel MVP
Joined
Feb 10, 2002
Messages
1,611
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> Auto_Open()<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

MrExcel MVP
Joined
Feb 10, 2002
Messages
1,611
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

MrExcel MVP
Joined
Jul 7, 2002
Messages
1,212
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

MrExcel MVP
Joined
Feb 10, 2002
Messages
1,611

ADVERTISEMENT

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

MrExcel MVP
Joined
Jul 7, 2002
Messages
1,212
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 Auto_Open()
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

MrExcel MVP
Joined
Feb 10, 2002
Messages
1,611
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
 

Forum statistics

Threads
1,144,329
Messages
5,723,732
Members
422,512
Latest member
MHau5

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top