Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 523
  • Last Modified:

write_url function to display a particular sheet in an excel file

Hi,

I want to know whether we can point to a particular excel sheet $sheetname in an excel file which is composed of many such sheets through the write_url function?

Is it possible to point to say sheetname A through this?

Here is what I did, its working fine except that when I click on Fail it takes me to the first sheet of the excel file whereas I want to display the particular excel sheet.

=============================
            print "\n\nTest Failed\n\n";
            ###############Writing Passed/Failed To All Source Sheet#############################################################
            my $wr_parser  = Spreadsheet::ParseExcel::SaveParser->new();
            my $wr_workbook = $wr_parser->Parse("/v/region/na/appl/qatools/util/data/reports/automation_framework/Sanity_Tests/Sanity_Check_RunBook.xls") or die "could not read the excel: $!";
            my $wr_worksheet = $wr_workbook->{Worksheet}[0];
            my $wr_format1 = $wr_workbook->AddFormat(Fill => [0,11,0]);
            my $wr_format2 = $wr_workbook->AddFormat(Fill => [1,10,0]);
            $wr_worksheet->AddCell($all_row, 4, "Fail", $wr_format2);
            $wr_worksheet->AddCell($all_row, 3, $cob_date2, $wr_format2);
            my $oWriteExcel = $wr_workbook->SaveAs("/v/region/na/appl/qatools/util/data/reports/automation_framework/Sanity_Tests/Sanity_Check_RunBook.xls");
            my $format = $oWriteExcel->add_format();
            $format->set_bg_color('red');
            $format->set_underline();
            $oWriteExcel->sheets(0)->write_url($all_row, 4, 'external:file://///v/region/na/appl/qatools/util/data/reports/automation_framework/Sanity_Tests/Sanity_Batch_Tests_Config.xls', "Fail", $format);
==============================================
0
sunny82
Asked:
sunny82
  • 12
  • 6
1 Solution
 
clockwatcherCommented:
If you define a named range (http://support.microsoft.com/kb/229808) in the sheet to which you're linking (Sanity_Batch_Tests_Config.xls) and then reference it via #whatever_you_called_it in your URL, it should open to that named range.

 external:file://///v/region/na/appl/qatools/util/data/reports/automation_framework/Sanity_Tests/Sanity_Batch_Tests_Config.xls#mydefinedname

0
 
sunny82Author Commented:
For example, if my sheet name is $sheetname1 = 'FCBalance' then will this work?

external:file://///v/region/na/appl/qatools/util/data/reports/automation_framework/Sanity_Tests/Sanity_Batch_Tests_Config.xls#$sheetname1

0
 
sunny82Author Commented:
Any solution to this above question? what do you think?
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
tel2Commented:
Hi sunny82,

As you know, clockwatcher has given you a possible solution.  I have no idea whether the example you posted would work, but...

Questions:
1. Have you read the webpage at the Microsoft link he provided?
2. Have you tested it?
3. What was the result?
0
 
sunny82Author Commented:
The problem with that solution is I am building a framework in Perl and generating many possible excel sheets on the fly. Hence I cannot open an excel sheet and specify the ranges. I think thats what the link says as per my understanding though I may be wrong. I need to store a sheetname in $sheetname and display that with the write_url function as I stated in the code above.

Pls let me know if this is possible. At present I can create an url for the whole excel file and not its individual tabs.
0
 
clockwatcherCommented:
Nowhere have you specified that the file that you're linking to is being generated on the fly-- only the file that contains the link to it.  It's no big deal, it just wasn't specified anywhere.  

The name needs to be defined in the sheet to which you're linking.  You can't do anything about it in the sheet that is linking to it.  In other words, the code that creates the Sanity_Batch_Tests_Config.xls file needs to contain a line like the below:

  $oWriteExcel->define_name('mybookmark', sprintf('=%s!$A$1', $sheetname);

The code that creates the workbook that links to it needs to then reference that bookmark.

   $oWriteExcel->sheets(0)->write_url($all_row, 4, 'external:file://///v/region/na/appl/qatools/util/data/reports/automation_framework/Sanity_Tests/Sanity_Batch_Tests_Config.xls#mybookmark', "Fail", $format);
0
 
sunny82Author Commented:
I did this as you suggested --

============================================
my $oWriteExcel = $wr_workbook->SaveAs("/v/region/na/appl/qatools/util/data/reports/automation_framework/Sanity_Tests/Sanity_Check_RunBook.xls");
            my $format = $oWriteExcel->add_format();
            $format->set_bg_color('lime');
            $format->set_underline();
            $oWriteExcel->define_name('mybookmark', sprintf('=%s!$A$1', $input));
            $oWriteExcel->sheets(0)->write_url($all_row, 4, 'external:file://///v/region/na/appl/qatools/util/data/reports/automation_framework/Sanity_Tests/Sanity_Batch_Tests_Config.xls#mybookmark', "Pass", $format);
$oWriteExcel->define_name('mybookmark', sprintf('=%s!$A$1', $input));
            $oWriteExcel->sheets(0)->write_url($all_row, 4, 'external:file://///v/region/na/appl/qatools/util/data/reports/automation_framework/Sanity_Tests/Sanity_Batch_Tests_Config.xls#mybookmark', "Pass", $format);
==================================================

But getting this error, I dont know why this is coming because the sheet FC Balance Check is present in Sanity_Batch_Tests_Config.xls


===========================
Unknown sheet name FC Balance Check in formula
 at Sanity_Tests.pl line 38
        main::__ANON__('Unknown sheet name FC Balance Check in formula\x{a}') called at //ms/dist/perl5/PROJ/Spreadsheet-WriteExcel/2.35/lib/perl5/Spreadsheet/WriteExcel/Formula.pm line 791
============================
0
 
sunny82Author Commented:
I mean I did this
======================
============================================
my $oWriteExcel = $wr_workbook->SaveAs("/v/region/na/appl/qatools/util/data/reports/automation_framework/Sanity_Tests/Sanity_Check_RunBook.xls");
            my $format = $oWriteExcel->add_format();
            $format->set_bg_color('lime');
            $format->set_underline();
            $oWriteExcel->define_name('mybookmark', sprintf('=%s!$A$1', $input));
            $oWriteExcel->sheets(0)->write_url($all_row, 4, 'external:file://///v/region/na/appl/qatools/util/data/reports/automation_framework/Sanity_Tests/Sanity_Batch_Tests_Config.xls#mybookmark', "Pass", $format);
=======================================


and still got the error even though the sheet FC Balance Check is present in Sanity_Batch_Tests_Config.xls

===========================
Unknown sheet name FC Balance Check in formula
 at Sanity_Tests.pl line 38
        main::__ANON__('Unknown sheet name FC Balance Check in formula\x{a}') called at //ms/dist/perl5/PROJ/Spreadsheet-WriteExcel/2.35/lib/perl5/Spreadsheet/WriteExcel/Formula.pm line 791
============================

0
 
clockwatcherCommented:
What is line 38 of Sanity_Tests.pl?
0
 
clockwatcherCommented:
Honestly... doesn't even really matter.  I think I must not be communicating very well.

You need to define the name in the workbook that's being linked to not the workbook that contains the link.  The code that you posted is creating a sheet called:

  Sanity_Check_RunBook.xls

inside this workbook, you're adding a link to another workbook (and specifically to a sheet within that workbook):

   Sanity_Batch_Tests_Config.xls

The name has to be defined in the Sanity_Batch_Tests_Config.xls file.  The code that you've posted is defining the name in the  Sanity_Check_RunBook.xls file.  

Where is the code that generates Sanity_Batch_Tests_Config.xls?  That's where the name needs to be defined.

   
0
 
sunny82Author Commented:
Oops, I did no see that. The problem is definitely a lack of communicatioion on my part. The workbook Sanity_Batch_Tests_Config.xls has some excel tabs FC Balance Check, FC Qty Check and FC Variance containing individual queries and their success and fail conditions. The other excel workbook Sanity_Check_RunBook.xls contains a list of those checks to be run (which are essentially the 3 tabs in Sanity_Batch_Tests_Config.xls), which tab to run marked by a Y or N. If it is Y, it will run that particular Sanity_Batch_Tests_Config.xls tab which are essentially the SQL queries in that tab and generate results, compare against their pass and fail conditions and update whether it is Pass or Fail in Sanity_Check_RunBook.xls workbook with a hyperlink beside that tab name in a status column. Clicking on this hyperlink will take me back to the particular tab of Sanity_Batch_Tests_Config.xls where the query ran.

Thats the reason why I read Sanity_Batch_Tests_Config.xls with Spreadsheet::ParseExcel module to read and run its queries while the workbook Sanity_Check_RunBook.xls is to be updated with Pass or Fail and hence I used SaveParser module for that.

In this scenario, is there a way to access the function "define_name('mybookmark', sprintf('=%s!$A$1', $input)" with a ParseExcel object, since I only used ParseExcel module to read Sanity_Batch_Tests_Config.xls and there is no WriteExcel object defined for it?? Without mentioning this tab name, it only takes me to the first tab of Sanity_Batch_Tests_Config.xls  on clicking Pass or Fail.

0
 
clockwatcherCommented:
Now I'm back to my original comment because it doesn't sound like you're dynamically creating Sanity_Batch_Tests_Config.xls.  

Are you not allowed to modify Sanity_Batch_Tests_Config.xls?  

Is there a problem with just opening up that file (Sanity_Batch_Test_Config.xls) in Excel and defining the names (bookmarks) by hand ?  Open Sanity_Batch_Tests_Config.xls in Excel, from the menu bar, select Insert, Name, Define, enter the name you want to use to refer to it, the sheet reference it refers to and hit the Add button. Save the sheet.

Once the bookmarks have been defined in the Sanity_Batch_Tests_Config.xls sheet, then the links that you're placing in Sanity_Check_RunBook.xls with write_url will work.
0
 
sunny82Author Commented:
If I name all the three sheets FC Balance Check , FC Variance Check and FC Qty Check as mybookmark, will the code be intelligent enough to open that particular sheet when I click its corresponding "P" or "F". Or Conversely, if I use three different bookmarknames for the three sheets can the code determine which sheet to open?
0
 
sunny82Author Commented:
This code --

$oWriteExcel->define_name('mybookmark', sprintf('=%s!$A$1', $sheetname);

is generic for all the 3 sheets, thats why I said the above.
0
 
sunny82Author Commented:
I put in a bookmarkname for FC Balance Check sheet as "mybookmark" manually in that sheet and saved it in Sanity_Batch_Test_Config.xls then ran this --

============================================
my $oWriteExcel = $wr_workbook->SaveAs("/v/region/na/appl/qatools/util/data/reports/automation_framework/Sanity_Tests/Sanity_Check_RunBook.xls");
            my $format = $oWriteExcel->add_format();
            $format->set_bg_color('lime');
            $format->set_underline();
            $oWriteExcel->define_name('mybookmark', sprintf('=%s!$A$1', $input));
            $oWriteExcel->sheets(0)->write_url($all_row, 4, 'external:file://///v/region/na/appl/qatools/util/data/reports/automation_framework/Sanity_Tests/Sanity_Batch_Tests_Config.xls#mybookmark', "Pass", $format);
=======================================


but getting the same error as earlier --
===========================
Unknown sheet name FC Balance Check in formula
 at Sanity_Tests.pl line 38
        main::__ANON__('Unknown sheet name FC Balance Check in formula\x{a}') called at //ms/dist/perl5/PROJ/Spreadsheet-WriteExcel/2.35/lib/perl5/Spreadsheet/WriteExcel/Formula.pm line 791
============================

Am I doing any mistake again?
0
 
clockwatcherCommented:
You don't need this line:

  $oWriteExcel->define_name('mybookmark', sprintf('=%s!$A$1', $sheetname);

In any of your code.  All you need is the bookmark defined in the sheet that is being linked to and then the write_url in the sheet that contains the link.
0
 
sunny82Author Commented:
Ok Cool. Another problem I having is in the excel file Sanity_Check_RunBook.xls

It is like this

FC Balance Check                Y                       Pass
FC Qty Check                       Y                       Fail
FC Variance Check              Y                       Pass

where Pass and Fail are all hyperlinks. If the status is N, that check wont run. The problem is only the last hyperlink is persisting. For e.g. in the above if I run all the three (since status is Y) only the last Pass has a hyperlink whereas in the case of first two the hyperlinks completely disappear.
Is it because of the WriteExcel object which gets created from the SaveAs method of ParseExcel SaveParser everytime such that when it writes the last hyperlink, in the case of first two the hyperlinks simply disappear though all the checks are run one after another?

Here is the code --
==========================================
my $oWriteExcel = $wr_workbook->SaveAs("/v/region/na/appl/qatools/util/data/reports/automation_framework/Sanity_Tests/Sanity_Check_RunBook.xls");
            my $format = $oWriteExcel->add_format();
            $format->set_bg_color('lime');
            $format->set_underline();
            $oWriteExcel->sheets(0)->write_url($all_row, 4, 'external:file://///v/region/na/appl/qatools/util/data/reports/automation_framework/Sanity_Tests/Sanity_Batch_Tests_Config.xls#mybookmark', "Pass", $format);
0
 
sunny82Author Commented:
Just to clarify what I said above, the first two "Pass" and "Fail" are not hyperlinks anymore but the third "Pass" is a hyperlink. Is it because of the reason I stated above?
0
 
sunny82Author Commented:
Pls let me know if the problem I am facing is because of the writeExcel object as I stated above?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 12
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now