Writing to an Excel File - KiXtart.org

You are not logged in. [Log In] KiXtart.org website » Forums » KiXtart » Basic Scripting » Writing to an Excel File

Page 1 of 1

Topic Options

#40724 - 2003-05-26 04:30 PM Writing to an Excel File
Richie19Rich77 Richie19Rich77 Seasoned Scripter Registered: 2002-08-16 Posts: 624 Loc: London, England	Hi I have search through the forum list, and I don't think this can be done. I have a excel file with over 500 rows and 10 coloums. I want to write data to Row 5 Coloum 10, is this possible. Thanks Rich
Top

#40725 - 2003-05-26 04:36 PM Re: Writing to an Excel File
Howard Bullock Howard Bullock KiX Supporter Registered: 2000-09-15 Posts: 5809 Loc: Harrisburg, PA USA	I have not done any Excel COM scripting yet but I believe that you should be able to done just about anything you can do in the Excel application itself. You should be able to change the value of a specific cell. I know this type of thing has been discussed on one of my Perl mailing lists. I will see if i can find a reference example that can be translated to KiXtart. _________________________ Home page: http://www.kixhelp.com/hb/
Top

#40726 - 2003-05-26 04:37 PM Re: Writing to an Excel File
Shawn Shawn Administrator Registered: 1999-08-13 Posts: 8611	How about this: code: $Excel.ActiveSheet.Rows(5).Columns(10).Value = 100
Top

#40727 - 2003-05-26 04:40 PM Re: Writing to an Excel File
Richie19Rich77 Richie19Rich77 Seasoned Scripter Registered: 2002-08-16 Posts: 624 Loc: London, England	Finding the cell is not a problem, but how do i write to it ?
Top

#40728 - 2003-05-26 04:41 PM Re: Writing to an Excel File
Richie19Rich77 Richie19Rich77 Seasoned Scripter Registered: 2002-08-16 Posts: 624 Loc: London, England	Doh I have my glasses on now, didn't see the .value command
Top

#40729 - 2003-05-26 04:44 PM Re: Writing to an Excel File
Richie19Rich77 Richie19Rich77 Seasoned Scripter Registered: 2002-08-16 Posts: 624 Loc: London, England	Thanks Shawn, you saved the day again
Top

#40730 - 2003-05-26 04:48 PM Re: Writing to an Excel File
Howard Bullock Howard Bullock KiX Supporter Registered: 2000-09-15 Posts: 5809 Loc: Harrisburg, PA USA	I was thinking more like this... $Worksheet.Range("J5:J5").Value=$value {edit} Darn again. [ 26. May 2003, 16:49: Message edited by: Howard Bullock ] _________________________ Home page: http://www.kixhelp.com/hb/
Top

#40731 - 2003-07-15 02:16 PM Re: Writing to an Excel File
codacs codacs Lurker Registered: 2003-07-15 Posts: 4 Loc: the Netherlands	is there a possibility to append to an excel file? I want to record all the workstations internal memory, workstationname and CPU speed into one excel file. I got it so far, that I can record one, but I can't get the next info on the next line, so it overwrites previously recorded data... Any ideas? I'm VERY new to this all, so I'm sorry if this was previously recorded somewhere, but I really couldn't find it... thx Andres _________________________ the trouble with doing nothing, is that you can't stop to take a rest.
Top

#40732 - 2003-07-15 02:20 PM Re: Writing to an Excel File
Jochen Jochen KiX Supporter Registered: 2000-03-17 Posts: 6380 Loc: Stuttgart, Germany	You can write to excel Row-wise in just defining the range and fill that with (for example the second dimension) of an array of strings : basic sample (header): $header = "Server","Ip-Address","Distribution Status" $xl = createobject("excel.application") if not @error $ = $xl.Workbooks.Add $xl.Range("A1:C1").Value = $header ; etc ... I write my logfiles of my admin scripts as such since a while now _________________________
Top

#40733 - 2003-07-15 02:51 PM

Re: Writing to an Excel File

codacs Offline

Lurker

Registered: 2003-07-15
Posts: 4
Loc: the Netherlands

OK as I said, I'm very new to all this
I've attached the code of what I have so far.

@Jochen: I appreciate what you say, but I don't understand it.
What's the difference in what you say, and what I do?

code:

$xlfilename = "\\server\memory\memory.xls"
$oXL = CreateObject("EXCEL.application")
$ws = @WKSTA
$mem = Val(WMIQuery("TotalPhysicalMemory","Win32_LogicalMemoryConfiguration"))/1024
$cpu = WMIQuery("CurrentClockSpeed","Win32_Processor")


If @error = 0
   $oXL.Visible = 1
   $Rc = $oXL.Workbooks.open ($xlfilename)
   $array = "Workstation", "CPU", "Memory"

   $oXL.Range("A1:C1").Value = $array	

$oXL.Cells(1).Value = $ws
$oXL.Cells(2).Value = $cpu
$oXL.Cells(3).Value = $mem
$Rc = $oXL.Range("A1:C1").EntireColumn.AutoFit

[ 15. July 2003, 14:54: Message edited by: codacs ]

_________________________
the trouble with doing nothing, is that you can't stop to take a rest.

Top

#40734 - 2003-07-15 03:05 PM Re: Writing to an Excel File
Sealeopard Sealeopard KiX Master Registered: 2001-04-25 Posts: 11165 Loc: Boston, MA, USA	You are always writing into the range of A1-C1. You will have to change the ranges depending on the row you want to write to. However, it would be easier to either wirte to a CSV-file, then import that into Excel or really go the professional route and use a database like MS Access or any SQL server for this. This will give you the flexibility of using SQL queries to SELECT/INSER/UPDATE your records. Appropriate UDFs can be found in the UDF Forum. [ 15. July 2003, 15:06: Message edited by: sealeopard ] _________________________ There are two types of vessels, submarines and targets.
Top

#40735 - 2003-07-15 03:12 PM Re: Writing to an Excel File
codacs codacs Lurker Registered: 2003-07-15 Posts: 4 Loc: the Netherlands	hmmm...not looking to get too professional right now... This is difficult enough as it is. (had a quick glance at the database thingy, but I had to close the screen, otherwise my head would've exploded... ) thx anyway, Jens. I'll try your option. _________________________ the trouble with doing nothing, is that you can't stop to take a rest.
Top

#40736 - 2003-07-15 03:52 PM

Re: Writing to an Excel File

Jochen Offline

KiX Supporter

Registered: 2000-03-17
Posts: 6380
Loc: Stuttgart, Germany

I'd go that way anyway if for logon script purposes [Wink]

Well, not the database but the ini-file way (see documentation for writeprofilestring() )

You can then fill an Excel logfile directly from an admin Workstation...

Ok, Silver Plate Code served today [Wink]

In your logonscript place this (untested) :


$log = "\\Pathtoini\inifilename.ini" ;change this

$_ = writeprofilestring( $log,@wksta,'MEM',(Val(WMIQuery("TotalPhysicalMemory","Win32_LogicalMemoryConfiguration"))/1024) )
$_ = writeprofilestring( $log,@wksta,'CPU',WMIQuery("CurrentClockSpeed","Win32_Processor") )

Run this script from an admin Workstation:


$log    = "\\Pathtoini\inifilename.ini" ;change this as well
$xlFile = "\\Pathtofile\Excelfilename.xls"

if exist($xlFile) del $xlFile endif

$asSections = split(readprofilestring($log,"",""),chr(10))
redim preserve $asSections[ubound($asSections)-1] ;strip last empty

global $machines[ubound($asSections)] ;declare '1st dimension'

for $i = 0 to ubound($machines)       ; fill '2nd Dimension'
	$machines[$i] = $asSections[$i],
                        val(readprofilestring($log,$asSections[$i],"MEM")),
                        val(readprofilestring($log,$asSections[$i],"CPU"))
next

$header = "Workstation","Physical Memory","CPU Speed"
$xl = createobject("excel.application")
if not @error
    $ = $xl.Workbooks.Add
    $xl.Range("A1:C1").Value = $header
    $xl.Range("A1:C1").Font.Bold = 1
    $xl.Range("A1:C1").Orientation = 90
    $ = $xl.Range("A1:C1").AutoFilter
    for $i = 0 to ubound($machines)
        $xl.Range("A"+($i+2)+":C"+($i+2)+"").Value = $machines[$i] 
    next
    $xl.Range("A1:B1").Cells.ColumnWidth = 15
    $xl.Range("C1:C1").Cells.ColumnWidth = 8
    $xl.Range("A1:C1").EntireColumn.HorizontalAlignment = 3
    $ = $xl.Range("A2").Activate
    $xl.ActiveWindow.FreezePanes = 1
    $ = $xl.ActiveWorkbook.SaveAs("$xlFile")
    $xl.UserControl = 1
    $bye = $xl.quit
else
    exit 0
endif

et voila, all Workstations results in one Excel file.

Basically the same as you do but a bit different [Wink]

_________________________

Top

#40737 - 2003-07-16 11:14 AM Re: Writing to an Excel File
codacs codacs Lurker Registered: 2003-07-15 Posts: 4 Loc: the Netherlands	Woohooo!!! Thanks a million, Jochen...It works like a charm!! Looking very good. thanks again! I'll be looking at this script closely to learn! _________________________ the trouble with doing nothing, is that you can't stop to take a rest.
Top

#40738 - 2003-07-16 11:19 AM Re: Writing to an Excel File
Jochen Jochen KiX Supporter Registered: 2000-03-17 Posts: 6380 Loc: Stuttgart, Germany	Right on! Feels good to help someone and get such a feedback _________________________
Top

Page 1 of 1

Previous Topic View All Topics

Index Next Topic

Moderator: Jochen, Allen, Radimus, Glenn Barnas, ShaneEP, Ruud van Velsen, Arend_, Mart

Hop to:

Shout Box

Search the board with:
superb Board Search
or try with google:

	Web kixtart.org