Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery
 
PerlMonks  

Re: Excel - adding & writing to multiple worksheets query

by technojosh (Priest)
on Oct 15, 2010 at 17:30 UTC ( #865529=note: print w/ replies, xml ) Need Help??


in reply to Excel - adding & writing to multiple worksheets query

I added use warnings; and moved one of the excel OLE calls out of your for loop... your code comments are oddly placed IMO but that has no effect on behavior ;)

use strict ; use warnings; use OLE; use Win32::OLE::Const "Microsoft Excel"; my ($excel, $workbook, $sheet, $j, $row, $range, $cell_str, $content, +$contentb, , $contentc, $width_found, $id, $test_cell_idg ); my ($worktable_name, $jwk, $jr, $sheets_total, $new_wk); # request the number of worktables print "Enter the number of worktables "; $sheets_total = <STDIN>; #___ DEFINE EXCEL $excel = CreateObject OLE "Excel.Application"; #___ MAKE EXCEL VISIBLE $excel -> {Visible} = 1; ## I MOVED THIS OUT OF THE FOR LOOP ## $workbook = $excel -> Workbooks -> Add; for($jwk = 1; $jwk <= $sheets_total; $jwk ++) { #___ ADD NEW WORKBOOK # $sheet = $workbook -> Worksheets("Sheet 1"); if($jwk == 1) { $sheet = $workbook -> Worksheets("Sheet1"); $sheet -> Activate; #___ ADD NEW WORKSHEET } else { $workbook -> Worksheets -> Add({After => $workbook -> Workshee +ts($workbook -> Worksheets -> {Count})}); $new_wk = "Sheet" . $jwk; print($new_wk); $sheet = $workbook -> Worksheets($new_wk); $sheet -> Activate; #___ CHANGE WORKSHEET NAME } $worktable_name = "worktable_name-" . $jwk; print "\njwk <$jwk> sheet name <$worktable_name> workbook <$workbo +ok> sheet <$sheet>\n"; $sheet -> {Name} = $worktable_name; for ($jr = 1; $jr <= 10; $jr++) { $range = 'A' . $jr; $sheet -> Range($range) -> {Value} = $jr . '-' . $jwk; } } $excel -> {DisplayAlerts} = 0; # This turns off the "This file already + exists" message. $workbook -> SaveAs ("c:\\n-sheets-test"); $excel -> Quit;


Comment on Re: Excel - adding & writing to multiple worksheets query
Select or Download Code
Re^2: Excel - adding & writing to multiple worksheets query
by merrymonk (Friar) on Oct 15, 2010 at 17:52 UTC
    Many thanks - that works just as I wanted it to!!
    I am sure your efforts have saved me much toil and trouble.
      merrymonk:

      You need to STOP coming here solely to avoid "toil and trouble" and come, instead, to learn. - - for abusing the Monastery (again!) as a code-writing or code-editing service.

      And, as you have been told several times, </br> is not a valid code here. PM doesn't use a full-spec html, xml, xhtm, or anything standard in user-written nodes. The acceptable markup can be found, profusely illustrated, in Markup in the Monastery; a briefer version is in Perl Monks Approved HTML tags .

      When you want to make a bulleted list, use <ul><li>an item</li><li>next item</li><li>last</li></ol>; if you want your list numbered, use <ol> (li pairs, to taste) </ol> which will be much easier to read.

      You'll also make your questions more readily readable (comprehensible) if you separate your ideas (topics) into paragraphs. Stream-of-consciousness narrative may be fine in fiction, but it really doesn't help you get help here.

      Update: linked to wrong faq for "Approved HTML Tags;" now fixed.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://865529]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (9)
As of 2015-07-04 10:56 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (59 votes), past polls