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

--
Message posted via http://www.officekb.com

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" wrote:

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

--
Message posted via http://www.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

OssieMac wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200707/1

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

"Launchnet via OfficeKB.com" wrote:

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

OssieMac wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200707/1

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

OssieMac wrote:
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.a...tions/200707/1

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



"Launchnet via OfficeKB.com" wrote:

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

OssieMac wrote:
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.a...tions/200707/1