Hello,

I have a spreadsheet that is created from Access using VBA to display
customer information.
The excel workbook is created ok and generates new sheets for call history
of each machine a cusomer has. However when a customer has say 40 machines
this generates a workbook with at least 40 sheets - difficult to navigate!
I automatically name each sheet in the following format - serial number -
model
On the first sheet I list all of the machines and thought it would be
usefull to hyperlink to each sheet - this I can achieve no problem and works
the first time you do it, however if you close excel and try it again Excel
opens but is blank.
If you do it again it crashes complaing about renaming sheets.
If you open task manager you can end the Excel process and then it works
again.
It appears that after creating the code to do the hyperlinks Excel seems to
remember them, until the process is not in memory.


I used the following code to create links on each sheet to go back to the
main page.

..Hyperlinks.Add Range("H1"), Address:="", SubAddress:="'Customer
Details'!A1", TextToDisplay:="Back"

If I coment out this line I can generate the spreadsheet, close it without
saving changes and then create it again with no problems.

Can anybody suggest a fix?

Use VBA to insert the hyperlinks as a formula:

Range("H1").Formula="=HYPERLINK(...)"

Where you replace the ... with the arguments you desire.
--
Gary''s Student - gsnu200750


"James Barrett" wrote:

Hello,

I have a spreadsheet that is created from Access using VBA to display
customer information.
The excel workbook is created ok and generates new sheets for call history
of each machine a cusomer has. However when a customer has say 40 machines
this generates a workbook with at least 40 sheets - difficult to navigate!
I automatically name each sheet in the following format - serial number -
model
On the first sheet I list all of the machines and thought it would be
usefull to hyperlink to each sheet - this I can achieve no problem and works
the first time you do it, however if you close excel and try it again Excel
opens but is blank.
If you do it again it crashes complaing about renaming sheets.
If you open task manager you can end the Excel process and then it works
again.
It appears that after creating the code to do the hyperlinks Excel seems to
remember them, until the process is not in memory.


I used the following code to create links on each sheet to go back to the
main page.

.Hyperlinks.Add Range("H1"), Address:="", SubAddress:="'Customer
Details'!A1", TextToDisplay:="Back"

If I coment out this line I can generate the spreadsheet, close it without
saving changes and then create it again with no problems.

Can anybody suggest a fix?

Gary,

Thanks for the quick response

That appears to work, however if the user renames the workbook and there is
a good chance they will, the links will stop working because the name of the
sheet is referenced in the formula

=HYPERLINK("[jim.xls]Customer_Details!A1","jim")

Is there a way of the formula dynamically picking up the workbook name?

Ta

"Gary''s Student" wrote:

Use VBA to insert the hyperlinks as a formula:

Range("H1").Formula="=HYPERLINK(...)"

Where you replace the ... with the arguments you desire.
--
Gary''s Student - gsnu200750


"James Barrett" wrote:

Hello,

I have a spreadsheet that is created from Access using VBA to display
customer information.
The excel workbook is created ok and generates new sheets for call history
of each machine a cusomer has. However when a customer has say 40 machines
this generates a workbook with at least 40 sheets - difficult to navigate!
I automatically name each sheet in the following format - serial number -
model
On the first sheet I list all of the machines and thought it would be
usefull to hyperlink to each sheet - this I can achieve no problem and works
the first time you do it, however if you close excel and try it again Excel
opens but is blank.
If you do it again it crashes complaing about renaming sheets.
If you open task manager you can end the Excel process and then it works
again.
It appears that after creating the code to do the hyperlinks Excel seems to
remember them, until the process is not in memory.


I used the following code to create links on each sheet to go back to the
main page.

.Hyperlinks.Add Range("H1"), Address:="", SubAddress:="'Customer
Details'!A1", TextToDisplay:="Back"

If I coment out this line I can generate the spreadsheet, close it without
saving changes and then create it again with no problems.

Can anybody suggest a fix?

Not that I know of. If the file jim.xls were renamed james.xls How could
Excel know what to do/
--
Gary''s Student - gsnu200750


"James Barrett" wrote:

Gary,

Thanks for the quick response

That appears to work, however if the user renames the workbook and there is
a good chance they will, the links will stop working because the name of the
sheet is referenced in the formula

=HYPERLINK("[jim.xls]Customer_Details!A1","jim")

Is there a way of the formula dynamically picking up the workbook name?

Ta

"Gary''s Student" wrote:

Use VBA to insert the hyperlinks as a formula:

Range("H1").Formula="=HYPERLINK(...)"

Where you replace the ... with the arguments you desire.
--
Gary''s Student - gsnu200750


"James Barrett" wrote:

Hello,

I have a spreadsheet that is created from Access using VBA to display
customer information.
The excel workbook is created ok and generates new sheets for call history
of each machine a cusomer has. However when a customer has say 40 machines
this generates a workbook with at least 40 sheets - difficult to navigate!
I automatically name each sheet in the following format - serial number -
model
On the first sheet I list all of the machines and thought it would be
usefull to hyperlink to each sheet - this I can achieve no problem and works
the first time you do it, however if you close excel and try it again Excel
opens but is blank.
If you do it again it crashes complaing about renaming sheets.
If you open task manager you can end the Excel process and then it works
again.
It appears that after creating the code to do the hyperlinks Excel seems to
remember them, until the process is not in memory.


I used the following code to create links on each sheet to go back to the
main page.

.Hyperlinks.Add Range("H1"), Address:="", SubAddress:="'Customer
Details'!A1", TextToDisplay:="Back"

If I coment out this line I can generate the spreadsheet, close it without
saving changes and then create it again with no problems.

Can anybody suggest a fix?