show another worksheet in pop up window

**tava.amitava** · 12-30-2009, 01:23 AM

Hi,

I am designing a website layout in excel so that it will be easier to develop.
I need to show a worksheet as a pop up window when a hyperlink is clicked. Can any one help me on this.

Regards,
Tava

**teylyn** · 12-30-2009, 01:27 AM

Hi Tava,

welcome to the forum.

I don't think Excel 2003 offers that functionality natively. I assume you want to re-create a target="blank" behaviour for a clicked hyperlink? This can probably be done using VBA.

I'll move the thread to the Programming Forum to catch the attention of the wizards there ...

cheers

**Leith Ross** · 12-30-2009, 04:01 AM

Hello Tava,

Without the benefit of your code and layout, I will show you the basic construct to create a pop up window from a hyperlink in HTML. This example also allows you to set certain display properties of the pop up window. To see this code better, copy the examples into Word or NotePad.

Code:

<A HREF="newWindowURL" onClick="window.open('newWindowURL','nameOfWindow','width=xxx,height=xxx,menubar,status,scrollbars,resizable,toolbar,location,directories');return false">Pop-up Window Link</A>

You may wish to create a link to close your pop-up window. Here's the code:

Code:

    <A HREF="oldWindowURL" onClick="window.close('nameOfWindow');return false;">Close this window</A>

Here is an example taken from a tutorial web site that displays their "About" page. This show how to set the display properties.

Code:

<a href="http://www.learningmovabletype.com/about2.php" onclick="window.open('http://www.learningmovabletype.com/about2.php','popup','width=600,height=700,scrollbars=yes,resizable=yes,toolbar=no,directories=no,location=no,menubar=no,status=no,left=50,top=0'); return false">About</a>

You will need to adapt this code to your situation, as these are just code examples on how to create the pop up window.

**tava.amitava** · 12-30-2009, 06:29 AM

Hi Ross,

Thank you for your reply. I am sorry that I was not very clear about my requirements. I am not looking for code to create pop up window in a web page. I am making a PROTOTYPE of a web page in an Excel sheet so that I know how the page should look like and what functions it should have.

To do this I have prepared a excel workbook. Now I want a hyperlink/button in one of the worksheet which when clicked should open a pop up window. In this pop up window I want to show another "worksheet" which I have prepared separately.

All this to be done in a MS-Excel workbook. I believe I need to work with some VBA codes but I need some start.

Appreciate your help.

Regards,
Tava

**royUK** · 12-30-2009, 07:37 AM

I can't understand why you are designing a web page layout in Excel, isn't this doubling the work?

Not sure if this is what you want

Code:

ThisWorkbook.FollowHyperlink Address:="http://www.excel-it.com", NewWindow:=True

**teylyn** · 12-30-2009, 07:47 AM

I can't understand why you are designing a web page layout in Excel,

hammer and nail? q.e.d.

**royUK** · 12-30-2009, 10:14 AM

Originally Posted by teylyn

hammer and nail? q.e.d.

And even if the resulting workbook is saved as a web page, the VBA code will not work

**tava.amitava** · 12-30-2009, 02:23 PM

Hi,

Well the purpose of creating the web page prototype in excel is to gather the required information and presenting it to the team members quickly. The same sheet can be emailed to other teams for review and getting their comments.

Later we will use this as a bible to create the actual web page using JSP/CSS/HTML, JAVA etc.

I want to show the pop up screens in excel because then it will be easier for other people to distinguish from other pages. I have seen this kind of worksheet before but could not remember how they did it.

I would be really grateful if any one can shed some light on this.

Regards,
Tava

**pike** · 12-30-2009, 06:26 PM

Not to sure what you need but hope this helps
you will need the owc library

HTML Code:

<html>
<Title>Excel Spreadsheet Component</Title>
<body>
<br/>
<br/>
<object classid="clsid:0002E551-0000-0000-C000-000000000046" 
id="Spread1" width=600 height=300>
</object>
<br/>
<br/>
<button id="btnRename">Rename this sheet</button>
<button id="btnFill">Fill with data</button>
<button id="btnSave">Save to Excel</button>

<SCRIPT LANGUAGE="VBScript">

Dim intNum ' global variable

Sub btnRename_OnClick()
Dim curSh ' ActiveSheet

set curSh = Spread1.ActiveSheet

Alert "Spreadsheet Web Component contains: " & vbCrLf _
& curSh.rows.count & " rows" & vbCrLf _
& curSh.columns.count & " columns "

If curSh.UsedRange.Address = "$A$1" and _ 
curSh.Cells(1,1).value = "" then 
Alert "No data in this sheet."
Else
intNum = intNum + 1
curSh.name = "My Data" & intNum
End if
End Sub

Sub btnFill_OnClick()
Dim conn 
Dim rst 
Dim strSQL
Dim count
Dim r
Dim c
Dim myData

' establish connection to the database
conn="Provider=Microsoft.Jet.OleDB.4.0; Data Source=" & _
"C:\Program Files\Microsoft Office\Office11\" & _ 
"Samples\Northwind.mdb"

' Create a Recordset
Set rst = CreateObject("ADODB.Recordset")

' select all records from Order Details table
strSql = "SELECT * FROM [Order Details]" 

' Open a static (3) Recordset (and execute the SQL 
' statement above) using the open connection
rst.Open strSql, conn, 3 

' enter field names as column headings
For count = 0 to rst.fields.count - 1
r = r + 1
With Spread1.ActiveSheet.Cells(1, r)
.Value = rst.Fields(count).Name
.Font.Bold = True
End with
Next

' get data from the Recordset into a variable
' data is stored as a two-demensional array
myData = rst.GetRows()

'get the number of returned rows
returnedRows = UBound(mydata, 2) + 1

' enter data into worksheet cells
For r = 1 to returnedRows
For c = 1 to rst.Fields.Count
Spread1.ActiveSheet.Cells(r+1, c).value = _ 
myData(c-1, r-1)
Next
Next

' close the Recordset
rst.close
set rst = Nothing
End Sub

Sub btnSave_OnClick()
Dim strFileName
Dim fso
Dim txtStream
Dim strData

strFileName="C:\ExcelWithOWC\myDataBook.xls"
set fso = CreateObject("Scripting.FileSystemObject")
set txtStream = fso.CreateTextFile(strFileName)
Spread1.DataType="HTMLData"
strData = Spread1.HTMLData
txtStream.WriteLine strData
txtStream.Close
set txtStream = Nothing
Set fso = Nothing
MsgBox "Your spreadsheet data was saved in " & _ 
strFileName & " file."
End Sub

</SCRIPT>
</body>
</html>