Script Request - KiXtart.org

You are not logged in. [Log In] KiXtart.org website » Forums » KiXtart » COM Scripting » Script Request

Page 1 of 2

Topic Options

#179430 - 2007-08-17 02:58 PM Script Request
Templar Templar Fresh Scripter Registered: 2002-04-11 Posts: 41 Loc: Cincinnati	I'm new to COM scripting adn am still trying to learn it I;m eally only looking for a script that will open up an existing Excel Pread sheet adn update specific cells on specific worksheets. One of the cells is a drop down menu but I think just the value will work. Is there anyone who might be able to assist with some code that could do what I am looking for? Script would do the following 1. Open a specific Excel file on a newtwork share 2. Edit specific cells on a spcific worksheet say Cell E10 and E11 on Worksheet Tuesday 3. Save the File
Top

#179432 - 2007-08-17 03:18 PM

Re: Script Request [Re: Templar]

Shawn

Administrator

Registered: 1999-08-13
Posts: 8611

Here's a quick script (user defined function) that may give you a "kickstart" for this:

Code:

Break On

$= SetExcelValue("c:\sheet.xls", "tuesday", "B5", "123")

Exit 1

Function SetExcelValue($path, $sheet, $range, $value)

 Dim $Excel
 Dim $WorkBook
 Dim $WorkSheet

 $Excel = CreateObject("Excel.Application")
 
 $WorkBook = $Excel.WorkBooks.Open($path)

 $WorkSheet = $WorkBook.Sheets.Item("Tuesday")

 $WorkSheet.Range($range).Value = $value

 $WorkBook.Save()

 $WorkSheet = 0
 $WorkBook = 0
 $Excel.Quit
 $Excel = 0

EndFunction

Top

#179433 - 2007-08-17 03:21 PM

Re: Script Request [Re: Shawn]

Shawn

Administrator

Registered: 1999-08-13
Posts: 8611

This one has better error checking ...

Code:

Break On

$= SetExcelValue("c:\sheet.xls", "tuesday", "B5", "123")

Exit 1

Function SetExcelValue($path, $sheet, $range, $value)

 Dim $Excel
 Dim $WorkBook
 Dim $WorkSheet

 $Excel = CreateObject("Excel.Application")

 If @ERROR = 0
 
  $WorkBook = $Excel.WorkBooks.Open($path)

  If @ERROR = 0

   $WorkSheet = $WorkBook.Sheets.Item($sheet)

   If @ERROR = 0

    $WorkSheet.Range($range).Value = $value

    $= $WorkBook.Save()

    $WorkSheet = 0

   Endif

   $WorkBook = 0

  Endif

  $Excel.Quit
  $Excel = 0

 Endif

 Exit @ERROR

EndFunction

Top

#179434 - 2007-08-17 03:42 PM Re: Script Request [Re: Shawn]
Shawn Shawn Administrator Registered: 1999-08-13 Posts: 8611	Two additional comments ... 1) If your going to be poking more than one range with values, maybe you can modify the function to except an array of range/value pairs - and update the cells in one batch. 2) In all your testing and modifying - keep an eye on TaskMgr and instances of the EXCEL process. These are COM servers, that run as detached processes - and if your not careful the processes can get "orphaned" ... if you start seeing a build-up of EXCEL processes - your doing something wrong (like, not releasing all your object handles etc) - just a caution.
Top

#179436 - 2007-08-17 04:09 PM Re: Script Request [Re: Templar]
Witto Witto MM club member Registered: 2004-09-29 Posts: 1828 Loc: Belgium	You could also grab script from the macro generator. Record wat you want to do with the macro tool. Per Example open a new Excel Workboor. Your Macro Editor will show Code: Workbooks.Add In a KiX Script you could write: Code: Break ON $objExcel = CreateObject("Excel.Application") $objExcel.Visible = -1 $ = $objExcel.Workbooks.Add ;Get $
Top

#179437 - 2007-08-17 04:10 PM Re: Script Request [Re: Shawn]
Templar Templar Fresh Scripter Registered: 2002-04-11 Posts: 41 Loc: Cincinnati	How would I address teh spreadsheet being password protected? Can I somehow input the password into the script? The script is exactly what I am looking for.
Top

#179439 - 2007-08-17 04:31 PM Re: Script Request [Re: Templar]
Witto Witto MM club member Registered: 2004-09-29 Posts: 1828 Loc: Belgium	The answers are on the net Google search Hey, Scripting Guy! How Can I Password-Protect an Excel Spreadsheet? Code: BREAK ON $objExcel = CreateObject("Excel.Application") $objExcel.Visible = -1 ;ChDir "C:\test" $ = $objExcel.Workbooks.Open("C:\test\Book1.xls",,,,"test") Get $
Top

#179440 - 2007-08-17 04:32 PM

Re: Script Request [Re: Witto]

Shawn

Administrator

Registered: 1999-08-13
Posts: 8611

I added an optional password protect param ...

Code:

Break On

$= SetOption("WrapAtEol", "On")

$= SetExcelValue("c:\sheet.xls", "tuesday", "B5", "123", "abc123")

? @SERROR

Exit 1

Function SetExcelValue($path, $sheet, $range, $value, optional $password)

 Dim $Excel
 Dim $WorkBook
 Dim $WorkSheet

 $Excel = CreateObject("Excel.Application")

 If @ERROR = 0
 
  $Excel.DisplayAlerts = 0

  $WorkBook = $Excel.WorkBooks.Open($path)

  If @ERROR = 0

   $WorkSheet = $WorkBook.Sheets.Item($sheet)

   If @ERROR = 0

    If $password
     $= $WorkSheet.Unprotect($password)
    Endif

    $WorkSheet.Range($range).Value = $value

    If $password
     $= $WorkSheet.Protect($password)
    Endif

    $= $WorkBook.Save()

    $WorkSheet = 0

   Endif

   $WorkBook = 0

  Endif

  $Excel.Quit
  $Excel = 0

 Endif

 Exit @ERROR

EndFunction

Top

#179442 - 2007-08-17 04:40 PM Re: Script Request [Re: Shawn]
Mart Mart KiX Supporter Registered: 2002-03-27 Posts: 4673 Loc: The Netherlands	Oh man what a coincidence $\:o$ $\:o$ Alex needs the same stuff as I do (except for the password stuff). Got use for this right away. It’s almost time to evacuate the office for the weekend so I’ll do some testing next week. Sweet! _________________________ Mart - Chuck Norris once sold ebay to ebay on ebay.
Top

#179448 - 2007-08-17 07:10 PM Re: Script Request [Re: Mart]
Templar Templar Fresh Scripter Registered: 2002-04-11 Posts: 41 Loc: Cincinnati	Will that last set of code with the password in it open a spread sheet that is password protected?
Top

#179449 - 2007-08-17 07:12 PM Re: Script Request [Re: Templar]
Shawn Shawn Administrator Registered: 1999-08-13 Posts: 8611	When I was playing with, saw different "levels" of password protection (workbook, sheet) so without knowing - all I can say is - give it a try.
Top

#179451 - 2007-08-17 08:00 PM Re: Script Request [Re: Templar]
Templar Templar Fresh Scripter Registered: 2002-04-11 Posts: 41 Loc: Cincinnati	Also if I needed to edit another cell say I already am editing cell B5 but I need to edit both cell B5 and then add some other value into Call B6. Where would I add in the code to update B5 and B6?
Top

#179452 - 2007-08-17 08:03 PM Re: Script Request [Re: Templar]
Shawn Shawn Administrator Registered: 1999-08-13 Posts: 8611	Well, first a question. Will the "number" of these cell updates be fixed (say, you will always updating three cells) and will the cell ranges be the same location everytime ? Or you looking for more flexibility ?
Top

#179453 - 2007-08-17 08:07 PM Re: Script Request [Re: Templar]
Templar Templar Fresh Scripter Registered: 2002-04-11 Posts: 41 Loc: Cincinnati	The password is workbook. The script works like a champ but when it is opening the file I get a prompt for the password. I can manually type it in and the script does it's thing. If I can get that and add another cell to edit I'm golden.
Top

#179454 - 2007-08-17 08:13 PM Re: Script Request [Re: Templar]
Shawn Shawn Administrator Registered: 1999-08-13 Posts: 8611	I cant seem to get this kind of workbook protection going (it always lets me update the sheet no matter what) ... what steps (menu items) do you use to setup this workbook protection ?
Top

#179455 - 2007-08-17 09:12 PM Re: Script Request [Re: Shawn]
NTDOC NTDOC Administrator Registered: 2000-07-28 Posts: 11628 Loc: CA	We'll pretend we didn't hear Shawn ask how to protect a workbook $;\)$
Top

#179456 - 2007-08-17 09:14 PM Re: Script Request [Re: NTDOC]
Shawn Shawn Administrator Registered: 1999-08-13 Posts: 8611	Lol - well, I am selecting protection, pushing buttons and providing passwords - but no matter what I do it never prompts me for a password or stops the script from updating the value - idk. [edit] Just found a bug in my rss reader. The new bbs software isn't providing GUIDS in the XML feed (to id unique posts) hey doc ... can you ask your buddies over in UBB where the GUID went ? :=)
Top

#179457 - 2007-08-17 10:09 PM Re: Script Request [Re: Shawn]
Templar Templar Fresh Scripter Registered: 2002-04-11 Posts: 41 Loc: Cincinnati	The password is set when the xls file is saved. They went in to General options when they were savign the file and set a password upon open in the options. So if you were opening it normally it prompts you to type in the password or open it as read only. The cells will not change all I'm updating is two cells and only two cells. cells like E7 and E8 I figured out how to change it on my onw. Basicly this spread sheet is to track a systems up time. So each day we have to go into the spread sheet and mark if the system is up or down and leave initials. We do this each day and each month. I was able to igure out how to update the cells each day by using some of the Day and Day of Month veriables. Each of the worksheets is a month and year like AUG-07. I've got that down as well. I just need to get a second cell and the password thing taken care of.
Top

#179458 - 2007-08-17 11:15 PM Re: Script Request [Re: Templar]
Witto Witto MM club member Registered: 2004-09-29 Posts: 1828 Loc: Belgium	Well, Or you just wait for the password question, or you write the password in your script. You do not have to place the script in the same location as the sheet. You can also obfuscate the password by per example tokenizing (kix32 /t).
Top

#179459 - 2007-08-17 11:42 PM Re: Script Request [Re: Templar]
NTDOC NTDOC Administrator Registered: 2000-07-28 Posts: 11628 Loc: CA	Using UPTIME from Microsoft you can do UPTIME /S and it will give you a log of all up and down time and length and average, etc..
Top

Page 1 of 2

Previous Topic View All Topics

Index Next Topic

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

Hop to:

Shout Box

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

	Web kixtart.org