Sign in to follow this  
Followers 0
randallc

Very Simple Excel Cell Read/ write

1 post in this topic

Hi, guys,

Here is simple example script; is it asking too much for someone to take over and do it better?

This reads a cell in a sheet, then adds to it, then changes content, then saves and exits Excel.

It won't check if the cells are a format that won't accept adding etc.

Function in second section.

Is this already written well as a discrete function? -I cannot find it in search.

dim $FilePath,$var

$FilePath="c:\winword\Excel\Book1.ls"

If Not FileExists($FilePath) Or Not StringInStr($FilePath, "xls") Then

  $FilePath = FileOpenDialog("Go - Choose your excel file as inbuilt one not exists", $FilePath, "Worksheet" & " (" & "*.xls" & ")", 1);+ $Recurse+ $Recurse

EndIf

_ExcelCOM($FilePath,1,"E",7,"Read",1,4,0,0); read cell "E7"

msgbox (0,"ReadCell=",$var)

_ExcelCOM($FilePath,1,"E",7,"Add",1,9,0,0); Add 9 to cell "E7"

msgbox (0,"After Add=",$var)

_ExcelCOM($FilePath,1,"E",7,"Into",1,"Hi",1,0); write 'Hi' and make visible cell "E7"

msgbox (0,"After Into=",$var)

_ExcelCOM($FilePath,1,"E",7,"Read",1,4,0,1); read cell "E7" and Exit Excel

msgbox (0,"After Exit=",$var)

msgbox (0,"Excel Process Exists=",ProcessExists ( "Excel.exe" ) )

Exit

func _ExcelCOM($sFilePath,$Sheet,$Column,$Row,$MEExcelCom,$Save,$ExcelValue,$Visible,$Exit)

If Not FileExists($sFilePath) Or Not StringInStr($sFilePath, "xls") Then

  ;msgbox (0,"$sFilePath=",$sFilePath)

  $sFilePath = FileOpenDialog("Go", $sFilePath, "Worksheet" & " (" & "*.xls" & ")", 1);+ $Recurse+ $Recurse

EndIf

$oExcel = ObjGet ($sFilePath) ; Get an Excel Object from an existing filename

If IsObj ($oExcel) Then

  If $Sheet > $oExcel.Worksheets.count Then $Sheet = $oExcel.Worksheets.count

  with $oExcel

  .Windows (1).Visible = 1; Set the first worksheet in the workbook visible

  .Worksheets ($Sheet).Activate

  .ActiveSheet.Visible = 1

  Select

    Case StringInStr($MEExcelCom, "Into")

    .activesheet.range ($Column & $Row).value = $ExcelValue  ; Fill cell  numbers +0

    $var = .activesheet.range ($Column & $Row).value

    Case StringInStr($MEExcelCom, "Add")

    $var = .activesheet.range ($Column & $Row).value

    .activesheet.range ($Column & $Row).value = number($ExcelValue) + $var ; Fill cell  numbers

    $var = .activesheet.range ($Column & $Row).value

    Case StringInStr($MEExcelCom, "Read")

    $var = .activesheet.range ($Column & $Row).value

    Case StringInStr($MEExcelCom, "Range")

    .activesheet.range ($Column & $Row & ":" & $Column2 & $Row2).value = $ExcelValue ; Fill cell  numbers

  EndSelect

  If $Save = "Save" Then

    .Save

  EndIf

  .Application.Visible = $Visible; Set the application invisible (without this Excel will exit)

  If $Exit = 1 Then

    .Save  ;d = 1

    .Application.Quit

  EndIf

  EndWith

Else

  MsgBox(0, "Excel File Test", "Error: Could not open " & $sFilePath & " as an Excel Object.")

EndIf

return $var

EndFunc

Share this post


Link to post
Share on other sites



Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0