My Problem - Help Please

[Launchnet via OfficeKB.com]

In Cell A5, I have c:\my documents\specialhandout.xls.
Column A's width is set at 1 or 12 pixels. It's not necessary
to see what is in it.

Cell B5 displays the users description of the Excel file to open
To the Right of Cell B5 is a button with an attached
Macro. (See Macro Below) The button top is blank.

HOW USER SELECTS AND OPENS FILE
User selects Cell B5 . . . or B6 . . . or B7 etc.

Next, the user clicks the Macro Button
The activecell is changed 1 cell to the left, which for the example is Col A
This is where the path and file name are that is to be opened.
Then, the Macro continues running.

NOW, MY PROBLEM

I don't want to use the path and file name as defined
in the below macro. I want the path & file name
to be picked up from the active cell A5 or any other cell in Col
A
that may be the active cell. The reason being, that in
Cell A6 I have a different path & file name to open and B7 is
also different and so forth down Column A

The button along side of B5 would run the same macro
as the button along side B6 and the same for B7 etc. or
I could use one long (vertical) button.

This allows the user to simply select the desired cell in Col B,
then the user clicks the Button just to the right of the selected
cell and this then opens the file of their choice in
a new instance of Excel.

EXISTING MACRO

Sub NewExcelWithWorkbook()
Dim oXL As Object
Dim oWB As Object

ActiveCell.Offset(0, -1).Activate
Set oXL = CreateObject("Excel.Application")
oXL.Visible = True

BELOW IS THE CURRENT WORKING CODE THAT OPENS THE A
SPECIFIC FILE ONLY. THE PROBLEM IS THAT WHEN I USE THIS CODE,
I HAVE TO HAVE A DIFFERENT MACRO FOR EACH FILE THE USER
WANTS TO OPEN.

Set oWB = oXL.Workbooks.Open("c:\my documents\specialhandout.xls.")

I WANT TO REPLACE PART OF THIS CODE.
PLEASE BEAR WITH ME, I DON'T KNOW HOW IT SHOULD READ. I AM
POSITIVE THE IDEA WILL WORK IF SOMEONE CAN HELP ME
WITH THE CODE.

EXAMPLE:
Set oWB = oXL.Workbooks.Open("GET THE PATH AND CODE
FROM THE CURRENT ACTIVE OR SELECTED CELL IN COLUMN A HERE")

THIS THEN OPENS THE FILE THE USER HAS SELECTED.

End Sub

 

[OssieMac]

YOUR EXAMPLE:
Set oWB = oXL.Workbooks.Open("GET THE PATH AND CODE
FROM THE CURRENT ACTIVE OR SELECTED CELL IN COLUMN A HERE")

Try this:
Set oWB = oXL.Workbooks.Open(ActiveCell)

or you can set a variable to equal the ActiveCell and then use the variable

Dim wbToOpen as String

wbToOpen = ActiveCell

Set oWB = oXL.Workbooks.Open(wbToOpen)

The above is untested but I think that it will point you in the right
direction.

Regards,

OssieMac

 

[Launchnet via OfficeKB.com]

Thanks OssieMac . . .
Everything works, eccept when the user clicks where there is not a Path &
File Name. An error message is then given.

The following is the code I used. Can you show me how to check for this
error or check for blank cell so that the user gets a message like . . .
"You have not clicked a valid cell."
Then, the the operator clicks Ok Button and the macro simply resets and
returns the user to the sheet the user is working with.

I understand basically how it's done, but I can' find an actual sample in
Help.

CODE USED:
Sub NewExcelWithWorkbook()
Dim oXL As Object
Dim oWB As Object

ActiveCell.Offset(0, -1).Activate
Set oXL = CreateObject("Excel.Application")
oXL.Visible = True
Set oWB = oXL.Workbooks.Open(ActiveCell)

End Sub

Can you please show me the code and where the message code
should go?

I appreciate this very much.

Thanks Matt@Launchnet

YOUR EXAMPLE:
Set oWB = oXL.Workbooks.Open("GET THE PATH AND CODE
FROM THE CURRENT ACTIVE OR SELECTED CELL IN COLUMN A HERE")

Try this:
Set oWB = oXL.Workbooks.Open(ActiveCell)

or you can set a variable to equal the ActiveCell and then use the variable

Dim wbToOpen as String

wbToOpen = ActiveCell

Set oWB = oXL.Workbooks.Open(wbToOpen)

The above is untested but I think that it will point you in the right
direction.

Regards,

OssieMac

In Cell A5, I have c:\my documents\specialhandout.xls.
Column A's width is set at 1 or 12 pixels. It's not necessary

[quoted text clipped - 60 lines]

End Sub

 

[OssieMac]

I think that you are going about it the wrong way. Try using a combo box for
the selection and then the user is restricted to valid selections. If you
can't work out how then let me know what version of xl you have and I'll see
if I can provide you with some help.

Regards,

OssieMac

Thanks OssieMac . . .
Everything works, eccept when the user clicks where there is not a Path &
File Name. An error message is then given.

The following is the code I used. Can you show me how to check for this
error or check for blank cell so that the user gets a message like . . .
"You have not clicked a valid cell."
Then, the the operator clicks Ok Button and the macro simply resets and
returns the user to the sheet the user is working with.

I understand basically how it's done, but I can' find an actual sample in
Help.

CODE USED:
Sub NewExcelWithWorkbook()
Dim oXL As Object
Dim oWB As Object

ActiveCell.Offset(0, -1).Activate
Set oXL = CreateObject("Excel.Application")
oXL.Visible = True
Set oWB = oXL.Workbooks.Open(ActiveCell)

End Sub

Can you please show me the code and where the message code
should go?

I appreciate this very much.

Thanks Matt@Launchnet

YOUR EXAMPLE:
Set oWB = oXL.Workbooks.Open("GET THE PATH AND CODE
FROM THE CURRENT ACTIVE OR SELECTED CELL IN COLUMN A HERE")

Try this:
Set oWB = oXL.Workbooks.Open(ActiveCell)

or you can set a variable to equal the ActiveCell and then use the variable

Dim wbToOpen as String

wbToOpen = ActiveCell

Set oWB = oXL.Workbooks.Open(wbToOpen)

The above is untested but I think that it will point you in the right
direction.

Regards,

OssieMac

In Cell A5, I have c:\my documents\specialhandout.xls.
Column A's width is set at 1 or 12 pixels. It's not necessary

[quoted text clipped - 60 lines]

End Sub

 

[Launchnet via OfficeKB.com]

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
new files, which I have no control over.

With my idea, the user only has to type in the path & file name 1 time
directly into the spreadsheet.

Then, when they actually want to open 1 of ??? Excel files they simply
click the cell and then click the Macro Button, which I have placed across
the top of the page, plus I use "Freeze Panes" to keep the button always
visible. Some users have between 50 and 100 files that they open frequently.

I have a main menu page with much more than I have time to describe to you.
One of the links on the main menu takes the user to a sheet named
"Open Existing Excel Files" On this sheet they have at a minimum
(depends on screen size) of 50 paths and file names without scrolling down.
1 "Page Down" displays another 50 if they have that many. They can even
group their files by type so they can find them faster.

The selected file opens in a new instance of Excel, which the users like, as
when they are done with the file, they can simply save it and close the
workbook by clicking the Big Red X, upper right corner. When the workbook
closes, the menu is back in view and they can open their next workbook or
return to the main menu.

Therefore, I still need the code for checking if the selected cell is blank,
and
if so, give them a message.

Would appreciate your continued help very much.

Thanks Matt@Launchnet

I think that you are going about it the wrong way. Try using a combo box for
the selection and then the user is restricted to valid selections. If you
can't work out how then let me know what version of xl you have and I'll see
if I can provide you with some help.

Regards,

OssieMac

Thanks OssieMac . . .
Everything works, eccept when the user clicks where there is not a Path &

[quoted text clipped - 55 lines]

--
Please take a look at www.openoursite.com Click on: "Keywords" and then
Click on "Matt's Story" and if you are a man, you should be very happy that
you read my story. God Bless for everyones help.

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

 

[OssieMac]

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
new files, which I have no control over.

With my idea, the user only has to type in the path & file name 1 time
directly into the spreadsheet.

Then, when they actually want to open 1 of ??? Excel files they simply
click the cell and then click the Macro Button, which I have placed across
the top of the page, plus I use "Freeze Panes" to keep the button always
visible. Some users have between 50 and 100 files that they open frequently.

I have a main menu page with much more than I have time to describe to you.
One of the links on the main menu takes the user to a sheet named
"Open Existing Excel Files" On this sheet they have at a minimum
(depends on screen size) of 50 paths and file names without scrolling down.
1 "Page Down" displays another 50 if they have that many. They can even
group their files by type so they can find them faster.

The selected file opens in a new instance of Excel, which the users like, as
when they are done with the file, they can simply save it and close the
workbook by clicking the Big Red X, upper right corner. When the workbook
closes, the menu is back in view and they can open their next workbook or
return to the main menu.

Therefore, I still need the code for checking if the selected cell is blank,
and
if so, give them a message.

Would appreciate your continued help very much.

Thanks Matt@Launchnet

I think that you are going about it the wrong way. Try using a combo box for
the selection and then the user is restricted to valid selections. If you
can't work out how then let me know what version of xl you have and I'll see
if I can provide you with some help.

Regards,

OssieMac

Thanks OssieMac . . .
Everything works, eccept when the user clicks where there is not a Path &

[quoted text clipped - 55 lines]

End Sub

--
Please take a look at www.openoursite.com Click on: "Keywords" and then
Click on "Matt's Story" and if you are a man, you should be very happy that
you read my story. God Bless for everyones help.

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

 

[Launchnet via OfficeKB.com]

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 SHEET 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

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]

 

[OssieMac]

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

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 SHEET 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

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

 

[Launchnet via OfficeKB.com]

Thank You . . . Thank You
I know the usage of Excel for general business very good.
I really enjoy building applications to save people time and effort.
I just wish that I new more code.
I understand what I want and what I need, but the actual code eludes me.
I am 75 years old and am very young in heart, mind and body.
Thanks to God Almighty.
Thank you my friend for your patience and help.
Thanks to all of you that are willing to help people like me.

Here is how the code turned out:

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

Dim testFileFind
Dim oWB As Object

ActiveCell.Offset(0, -1).Activate 'this moves the selected cell 1 cell to
the Left

'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. You have not
entered a Path & File Name."
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

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

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

Set oWB = oXL.Workbooks.Open(ActiveCell)

End Sub

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

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

[quoted text clipped - 114 lines]

 

[Launchnet via OfficeKB.com]

THE NEXT STEP . . .

I needed the same procedure for opening a word document just like in Excel.
So, I modified the code and all works EXCEPT . . .PLEASE read my explaination
Can you give me a hand again ?
Thanks
Matt@Launchnet


Sub NewWordWithDocument()
Dim oWordApp As Object
Dim oWordDoc As Object
Set oWordApp = CreateObject("Word.Application")
' oWordApp.Visible = True
' Set oWordDoc = oWordApp.Documents.Open( _
' "C:\documents and settings\default\my documents\CompClassChurchBulletin.
doc")

'WITH THE PATH AND DOCUMENT HARD CODED ABOVE, IT WORKED, BUT HAD NO
TESTING.





'FROM HERE DOWN THE TESTING WORKS FINE.
'THEN, WHEN IT GETS TO THE LAST LINE OF CODE WHICH IS TO OPEN THE FILE
IN ACTIVECELL.
'AN ERROR MESSAGE IS DISPLAYED: "Type Mismatch"
'I CLICK DEBUG AND THE LAST LINE OF CODE IS HIGHLITED IN YELLOW.
'I DON'T KNOW WHAT I DID WRONG.




Dim testFileFind
Dim oWB As Object

ActiveCell.Offset(0, -1).Activate 'this moves the selected cell 1 cell to
the Left

'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. You have not
entered a Path & File Name."
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

'THIS LINE OF CODE OPENS THE NEW INSTANCE OF WORD.
Set oWordApp = CreateObject("Word.Application")

'THIS LINE OF CODE MAKES THE NEW INSTANCE OF WORD VISIBLE.
oWordApp.Visible = True

Set oWordDoc = oWordApp.Documents.Open(ActiveCell)

End Sub

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

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

[quoted text clipped - 114 lines]

 

[Launchnet via OfficeKB.com]

Found the answer, from John Lundy . . .

All I had to do was ADD: .text to the last line of code after
ActiveCell.text

Thanks Everyone
Matt@Launchnet

THE NEXT STEP . . .

I needed the same procedure for opening a word document just like in Excel.
So, I modified the code and all works EXCEPT . . .PLEASE read my explaination
Can you give me a hand again ?
Thanks
Matt@Launchnet

Sub NewWordWithDocument()
Dim oWordApp As Object
Dim oWordDoc As Object
Set oWordApp = CreateObject("Word.Application")
' oWordApp.Visible = True
' Set oWordDoc = oWordApp.Documents.Open( _
' "C:\documents and settings\default\my documents\CompClassChurchBulletin.
doc")

'WITH THE PATH AND DOCUMENT HARD CODED ABOVE, IT WORKED, BUT HAD NO
TESTING.

'FROM HERE DOWN THE TESTING WORKS FINE.
'THEN, WHEN IT GETS TO THE LAST LINE OF CODE WHICH IS TO OPEN THE FILE
IN ACTIVECELL.
'AN ERROR MESSAGE IS DISPLAYED: "Type Mismatch"
'I CLICK DEBUG AND THE LAST LINE OF CODE IS HIGHLITED IN YELLOW.
'I DON'T KNOW WHAT I DID WRONG.




Dim testFileFind
Dim oWB As Object

ActiveCell.Offset(0, -1).Activate 'this moves the selected cell 1 cell to
the Left

'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. You have not
entered a Path & File Name."
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

'THIS LINE OF CODE OPENS THE NEW INSTANCE OF WORD.
Set oWordApp = CreateObject("Word.Application")

'THIS LINE OF CODE MAKES THE NEW INSTANCE OF WORD VISIBLE.
oWordApp.Visible = True

Set oWordDoc = oWordApp.Documents.Open(ActiveCell)

End Sub

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

[quoted text clipped - 32 lines]