Trapping??? OR On Error Resume???

[nrage21]

The macro below runs tru a set of commandbuttons... as these are presse
a range of cells is checked, if everything is ok, then it copies th
range, opens and pastes the range in workbook2.

When the user clicks on another commandbutton excell asks me if I wan
to reopen Workbook2. I want to say No (because workbook2 is alread
open at this point), then that's when I am presented with erro
'1004'.
"Method 'Open' of Object 'Workbooks' failed.

I want to bypass error '1004' and continue with the paste operation i
workboo2.

My code...

Private Sub CmBLI1_1000_1_Click()
'10:00
If Application.WorksheetFunction.CountA(Range("E7:G7")) < _
Range("E7:G7").Cells.Count Then
MsgBox "Complete ALL Fields"
Else
Range("C7:J7").Copy
Set rng = ActiveSheet.Range("C7:J7")
Workbooks.Open _
("C:\My Documents\Workbook2.xls")
Set rng1 = ActiveSheet.Range("A13004").End(xlUp).Offset(1, 0)
rng.Copy Destination:=rng1
End If
End Sub


and some code I found at another site for trapping errors, but I don
know where to place this, if it works.

'Module is already in progress
On Error GoTo ErrHandler
'Tell VBA to use your trapping routine

'Module continues then
Selection.Value = 123
Exit Sub 'You must exit the sub or the
'Error Handler is invoked
'everytime you come to the end of the macro

ErrHandler:
Select Case Err
'Err is already defined in Excel to hold the
'numeric code for errors
Case 91
MsgBox "There is no active cell"
Case 1000
MsgBox "Cannot assign a value to the selection."
Case 1004
MsgBox Err & " " & Error(Err) & Chr(13) & Chr(13) _
& "The method you specified cannot be used on the object."
Case 1005
MsgBox "The worksheet is protected."
Case Else
MsgBox Error(Err) & err.number & err.description
End Select
End Sub


Any help is greatly appreacited!

TIA
- Larry -
VBA Amateu

 

[Michael J. Malinsky]

If Workbook2 is already open, then why are you using the code:

Workbooks.Open _
("C:\My Documents\Workbook2.xls")

to reopen it? If it's already open and you want to switch to that workbook,
then use:

Workbooks("Workbook2").Activate

This should then eliminate the error you receive.
--
Michael J. Malinsky
Pittsburgh, PA

"I was gratified to be able to answer promptly,
and I did. I said I didn't know." -- Mark Twain

 

[Ron de Bruin]

One way

Use this to see if the file is open

Function bIsBookOpen(ByRef szBookName As String) As Boolean
' Rob Bovey
On Error Resume Next
bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
End Function

Sub File_Open_test()
If bIsBookOpen("Test1.xls") Then
MsgBox "the File is open!"
Else
MsgBox "the File is not open!"
End If
End Sub

 

[Bob Phillips]

Hi Larry,

Try testing if it is already open

Private Sub CmBLI1_1000_1_Click()
If Application.WorksheetFunction.CountA(Range("E7:G7")) < _
Range("E7:G7").Cells.Count Then
MsgBox "Complete ALL Fields"
Else
Range("C7:J7").Copy
Set rng = ActiveSheet.Range("C7:J7")
If Not FileIsOpen("C:\My Documents\Workbook2.xls") Then
Workbooks.Open _
"C:\My Documents\Workbook2.xls"
End If
Set rng1 = ActiveSheet.Range("A13004").End(xlUp).Offset(1, 0)
rng.Copy Destination:=rng1
End If
End Sub

Function FileIsOpen(fName) As Boolean
On Error Resume Next
FileIsOpen = Len(Workbooks(fName).Name)
On Error GoTo 0
End Function


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Ron de Bruin]

That what you get when you not test it

Set rng1 = Wb.Range("A13004").End(xlUp).Offset(1, 0)

fill in the sheetname also

--
Regards Ron de Bruin
http://www.rondebruin.nl

If the workbook is already open you set Rng1 also to the active sheet.
the same as rng.

Untested, but try this

Sub test()
Dim Wb As Workbook
Dim rng As Range
Set rng = ActiveSheet.Range("C7:J7")
If Not FileIsOpen("Workbook2.xls") Then
Set Wb = Workbooks.Open("C:\My Documents\Workbook2.xls")
Else
Set Wb = Workbooks("Workbook2.xls")
End If
Set rng1 = Wb.Range("A13004").End(xlUp).Offset(1, 0)
rng.Copy Destination:=rng1
End Sub

 

[nrage21]

I get...
Run-time error '438':
Object doesn't support this property or method

Opens workbook2 but triggers error '438'
When workbook2 is open, doesn't paste, but triggers the error '438 too
Updated code...

Private Sub CmBLI1_1000_1_Click()
'10:00
If Application.WorksheetFunction.CountA(Range("E7:G7")) < _
Range("E7:G7").Cells.Count Then
MsgBox "Complete ALL Fields"
Else
test
End If
End Sub

Sub test()
Dim Wb As Workbook
Dim rng As Range

Set rng = ActiveSheet.Range("C7:J7")
If Not FileIsOpen("Workbook2") Then
Set Wb = Workbooks.Open("C:\My Documents\Workbook2.xls")
Else
Set Wb = Workbooks("Workbook2.xls")
End If
Set rng1 = Wb.Range("A13004").End(xlUp).Offset(1, 0)
rng.Copy Destination:=rng1
End Sub


Function FileIsOpen(fName) As Boolean
On Error Resume Next
FileIsOpen = Len(Workbooks("2004 Pickup History.xls").Name)
On Error GoTo 0
End Function


:

 

[nrage21]

What do you mean?????

That what you get when you not test it

Set rng1 = Wb.Range("A13004").End(xlUp).Offset(1, 0)

fill in the sheetname als

 

[Ron de Bruin]

Wb is the workbook
I must know the sheet name of the Range("A13004").End(xlUp).Offset(1, 0)

Like this
Set rng1 = Wb.Sheets("sheet1").Range("A13004").End(xlUp).Offset(1, 0)

 

[nrage21]

Ron you put the nail on the coffin

It is now fully operational...

since you really help me a lot already I would like you to see my work
so far.

Can I send you the entire workbook at (e-mail address removed)???

 

[Ron de Bruin]

I will take a look at your code if I have the time.
Don't be mad at me if If I have no time to do it

 

[nrage21]

No I don't want you to do anything, just to get the feel of th
interface. I'll send it to you later... now I got to run... check you
email tonight after 11pm eastern time.

Take care and thanks man. You guys (Frank, Tom, Ron, Bob) are reall
the top dogs in this board!



- Larry