[SOLVED] Hyperlink in Excel from one tab to the next
I have a compound EXL2K report. It has two tabs first tab is summary and second tab is detail. Is it possible to have a link on Field1 in Summary Tab which when clicked goes to its position on detail tab?. I tried searching for this in the forum but didn't find any.A car example would be great!. Thanks.This message has been edited. Last edited by: Kerry,
App Studio Version 8202 windows Platform SQL Server 2008/2012
May 16, 2012, 10:38 PM
Waz
Here is something that I knocked up.
Seems that Excel uses an anchor to link to cells in the workbook.
SET COMPOUND = OPEN
TABLE FILE CAR
PRINT
COMPUTE CNTR/I3 = IF LAST CNTR EQ 0 THEN 1 ELSE LAST CNTR + 1 ;
COMPUTE CTYLINK/A50 = '<a href="#' || COUNTRY || '!A1">' || COUNTRY || '</a>' ;
ON TABLE PCHOLD FORMAT EXL2K
END
SET COMPOUND = BYTOC CLOSE
TABLE FILE CAR
PRINT CAR MODEL
BY COUNTRY
ON TABLE PCHOLD FORMAT EXL2K
END
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
May 16, 2012, 11:49 PM
j.gross
Bravo!
-- One shortcoming -- the embedded blank in W GERMANY makes its link invalid.
The fix:
DEFINE FILE CAR
TAB/A10=STRREP(10, COUNTRY, 1, ' X', 1, '_', 10, TAB);
TAB=SUBSTR(10, TAB, 1, ARGLEN(10,COUNTRY,'I2'), 10, TAB);
END
SET COMPOUND = OPEN
TABLE FILE CAR
WRITE
COMPUTE LIST/I3 = LIST + 1 ;
COMPUTE CTYLINK/A90 = '<a href="#' || TAB || '!A1">' || COUNTRY || '</a>' ; AS COUNTRY
BY COUNTRY NOPRINT
ON TABLE PCHOLD FORMAT EXL2K
END
SET COMPOUND = CLOSE
TABLE FILE CAR
PRINT CAR MODEL
BY TAB NOPRINT
BY COUNTRY
ON TABLE PCHOLD FORMAT EXL2K BYTOC
END
May 16, 2012, 11:59 PM
Waz
Well spotted.
Seems that Excel will wrap the text in single quotes, so the below will also work.
SET COMPOUND = OPEN
TABLE FILE CAR
PRINT
COMPUTE CNTR/I3 = IF LAST CNTR EQ 0 THEN 1 ELSE LAST CNTR + 1 ;
COMPUTE CTYLINK/A50 = '<a href="#''' || COUNTRY || '''!A1">' || COUNTRY || '</a>' ;
ON TABLE PCHOLD FORMAT EXL2K
END
SET COMPOUND = BYTOC CLOSE
TABLE FILE CAR
PRINT CAR MODEL
BY COUNTRY
ON TABLE PCHOLD FORMAT EXL2K
END
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
May 17, 2012, 01:15 AM
Danny-SRL
And back!
SET COMPOUND = OPEN
TABLE FILE CAR
PRINT
COMPUTE CTYLINK/A50 = '<a href="#''' || COUNTRY || '''!A1">' || COUNTRY || '</a>' ;
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET STYLE *
TITLETEXT='Countries', $
ENDSTYLE
END
SET COMPOUND = BYTOC CLOSE
DEFINE FILE CAR
PG1/A50='<a href="#''Countries''!A1">' || 'Countries' || '</a>' ;
END
TABLE FILE CAR
PRINT CAR MODEL
BY COUNTRY
FOOTING
"<PG1 "
ON TABLE PCHOLD FORMAT EXL2K
END
Daniel In Focus since 1982 wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF
May 17, 2012, 01:22 AM
Waz
When will it end.......
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
May 17, 2012, 11:18 AM
getit
You guys are awesome! Still trying to get the link to go the exact cell value instead of A1. If we click on France on SUMMARYRPT it goes to the cell where the value of the country is 'FRANCE' IN DETAILRPT.
DEFINE FILE CAR
CTYLINK/A100 = '<a href="#''' || 'DETAILRPT' || '''!A1">' || COUNTRY || '</a>' ;
END
TABLE FILE CAR
HEADING
"Summary Report"
" "
SUM RC
BY CTYLINK
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT='SUMMARYRPT', $
TYPE=HEADING, SIZE=18, $
ENDSTYLE
ON TABLE PCHOLD FORMAT EXL2K OPEN
END
TABLE FILE CAR
HEADING
"Detail Report"
" "
PRINT SALES
BY COUNTRY
BY MODEL
BY CAR
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT='DETAILRPT', $
TYPE=HEADING, SIZE=18, $
ENDSTYLE
ON TABLE PCHOLD FORMAT EXL2K CLOSE
END
App Studio Version 8202 windows Platform SQL Server 2008/2012
May 17, 2012, 12:37 PM
j.gross
That's just a matter of replacing the A1 cell reference in the link variable with a computed variable pointing to the appropriate cell -- column A (constant), row = first row for the respective country = cumulative total of rows for previous countries + the offset of the very first detail row.
TABLE FILE CAR
WRITE CNT.SALES
COMPUTE
OFFSET/I2=LAST OFFSET+LAST CNT.SALES;
ROW/I2=OFFSET+4;
CTYLINK/A50 = '<a href="#''DETAILRPT''!A' || EDIT(ROW) || '">' || COUNTRY || '</a>' ;
BY COUNTRY
ON TABLE HOLD AS LINKS FORMAT FOCUS INDEX COUNTRY
END
JOIN CLEAR *
JOIN COUNTRY IN CAR TO COUNTRY IN LINKS AS L:
TABLE FILE CAR
HEADING
"Summary Report"
" "
SUM RC
BY CTYLINK
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT='SUMMARYRPT', $
TYPE=HEADING, SIZE=18, $
ENDSTYLE
ON TABLE PCHOLD FORMAT EXL2K OPEN
END
TABLE FILE CAR
HEADING
"Detail Report"
" "
PRINT SALES
BY COUNTRY
BY MODEL
BY CAR
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT='DETAILRPT', $
TYPE=HEADING, SIZE=18, $
ENDSTYLE
ON TABLE PCHOLD FORMAT EXL2K CLOSE
END
May 17, 2012, 02:07 PM
getit
Great , Thanks. Just one last quesion,can I get a CAR EXAMPLE to win the mega millions? Just kiddin :-), Thanks a lot.
App Studio Version 8202 windows Platform SQL Server 2008/2012