RE: My Problem - Help Please

Try this to check that the file exists. Insert it as the first code in the
sub and then processing will terminate before it attempts to open anything if
the file does not exist. I should have used this method before.

Dim testFileFind

'The following tests for a blank cell and ends processing
'It is needed because dir() function will not work with a blank.

If Len(Trim(ActiveCell)) = 0 Then
MsgBox "Active Cell " & ActiveCell.Address & " is blank"
End
End If

'The following tests for the existance of the file

testFileFind = Dir(ActiveCell)

'If the file is not found there will be nothing
'in the variable and processing ends.

If Len(testFileFind) = 0 Then
MsgBox "Invalid selection." & Chr(13) & _
"Filename " & ActiveCell & " not found"
End
End If

Regards,

OssieMac



"Launchnet via OfficeKB.com" wrote:

Hi Again, I think we are very close. I like your second suggestion best.

Please review code and my comments.

Again Thanks in Advance.


Sub NewExcelWithWorkbook()
Dim oXL As Object 'This is needed to open a new instance of Excel.
'Without it, the file is only opened as a new
window
'and I can't use that
Dim oWB As Object
'Dim wbToOpen As String
ActiveCell.Offset(0, -1).Activate 'this moves the selected cell 1 cell to
the left
'wbToOpen = ActiveCell

On Error GoTo errorHandler 'Set error capture
'Set oWB = Workbooks.Open(wbToOpen)


'DON'T KNOW WHERE TO PUT THIS LINE OF CODE
On Error GoTo 0 'Turn off error capture

'ON THE CODE LINE DIRECTLY BELOW, THE NEW INSTANCE OF EXCEL IS OPENED
'NATURALLY, IT DOES NOT CATCH AN ERROR HERE WHEN THE EXCEL APPLICATION IS
OPENED . . . SO, NOW A NEW INSTANCE OF EXCEL IS OPENED...

'WHEN THIS LINE OF CODE RUNS: Set oWB = oXL.Workbooks.Open(ActiveCell)
'AN UNVALID 'PATH OR FILE NAME" WILL CAUSE AN ERROR AND IT IS CAUGHT
'WHEN IT TRIES TO OPEN THIS BAD "PATH OR FILE NAME"

'IF THE "PATH & FILE NAME" IS GOOD, IT WORKS CORRECTLY.
'WHEN A BAD PATH OR FILE NAME CAN NOT OPEN
'IT LEAVES THE NEW INSTANCE OF EXCEL OPEN WITHOUT A *** OR FILE
'AND THE MACRO STOPS.

'WHEN I CLOSE THE NEW INSTANCE OF EXCEL, THE ERROR MESSAGE IS
'IS THEN DISPLAYED.



'MY THOUGHTS . . . . . . . . . . . .
'SOME HOW WE NEED TO TEST THE "ACTIVECELL" TO SEE IF IT IS VALID PRIOR
'TO OPENING THE NEW INSTANCE OF EXCEL. IF IT IS VALID, THEN THE
'NEW INSTANCE OF EXCEL CAN BE OPENED FOLLOWED BY OPENING THE
' "PATH & FILE NAME".
'IS THIS POSSIBLE ? ? ? ? ?



'THIS LINE OF CODE OPENS THE NEW INSTANCE OF EXCEL. THIS I NEED.
Set oXL = CreateObject("Excel.Application")

'THIS LINE OF CODE MAKES THE NEW INSTANCE OF EXCEL VISIBLE.
oXL.Visible = True

On Error GoTo errorHandler 'Set error capture

Set oWB = oXL.Workbooks.Open(ActiveCell)

GoTo pastErrorHandler 'If code runs to here skip error routine

errorHandler:
MsgBox "Not a valid file selection" & _
Chr(13) & Chr(13) & _
"Please re- select"

End 'Terminate processing

pastErrorHandler:

End Sub


OssieMac wrote:
You can use either or both of the following routines. The first only tests
for a blank cell. The second will test for all errors in opening the file.

Put the errorHandler and pastErrorHandler at the end of the sub as I have done

Sub Macro1()
'The following tests for a blank cell and ends processing
If Len(Trim(ActiveCell)) = 0 Then
MsgBox "Active Cell " & ActiveCell.Address & " is blank"
End
End If

'The following will cover all errors in opening
'the required file including misspelt filenames.
'No need to test for blank cell if you use this.

Dim wbToOpen As String

wbToOpen = ActiveCell

On Error GoTo errorHandler 'Set error capture

'I have deleted oXL from the following line
'because I don't know what it does.
Set oWB = Workbooks.Open(wbToOpen)

On Error GoTo 0 'Turn off error capture

'Put your code here

GoTo pastErrorHandler 'If code runs to here skip error routine

errorHandler:
MsgBox "Not a valid file selection" & _
Chr(13) & Chr(13) & _
"Please re- select"

End 'Terminate processing

pastErrorHandler:

End Sub

Thanks for your suggestion. The reason that I am using this method is
because many users don't know how to fill the combo box as they add
[quoted text clipped - 43 lines]

End Sub

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-functions/200707/1


.