Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister

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;

Replies are listed 'Best First'.
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.

      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.