Sign in to follow this  
Followers 0
randallc

Excel COM func

2 posts in this topic

#1 ·  Posted (edited)

Hi, guys,

I've had no answer to this on support; anyone here interested?

Can anyone please help with improving this script to check if it will give errors (ie wrong cell format, locked cells, read only file, etc in Excel?)

Here is simple example script;

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

Function in second section.

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

Best, Randall

Here is orig example; but extra parameter added now (so file lines with tabs can be selected to go "to Columns" instead of rows if needed)

Note also automatic calc is now turned off till a "save" is performed

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,0,0); read cell "E7"

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

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

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

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

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

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

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

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

Exit

EDIT -updated to take tab-delimited strings (extra parameter than before for "wanting to find "lastcell"/ lastrow +1 address Edited by randallc

Share this post


Link to post
Share on other sites



I know you're frustrated that this isn't drawing much interest here, but there's good reason for it I think. The membership of this forum is 100% AutoIt, 10% COM and 1% Excel. If you were having trouble converting a working VBScript example to AutoIt you'd get a lot of help here, but what you're asking for has little to do with AutoIt and nearly everything to do with Excel.

I'd suggest you'd get a lot more interest from an Excel forum helping you with a VBScript version of this that you could later convert to AutoIt. You can find such a forum here: Microsoft Excel Community Forum

Dale


Free Internet Tools: DebugBar, AutoIt IE Builder, HTTP UDF, MODIV2, IE Developer Toolbar, IEDocMon, Fiddler, HTML Validator, WGet, curl

MSDN docs: InternetExplorer Object, Document Object, Overviews and Tutorials, DHTML Objects, DHTML Events, WinHttpRequest, XmlHttpRequest, Cross-Frame Scripting, Office object model

Automate input type=file (Related)

Alternative to _IECreateEmbedded? better: _IECreatePseudoEmbedded  Better Better?

IE.au3 issues with Vista - Workarounds

SciTe Debug mode - it's magic: #AutoIt3Wrapper_run_debug_mode=Y Doesn't work needs to be ripped out of the troubleshooting lexicon. It means that what you tried did not produce the results you expected. It begs the questions 1) what did you try?, 2) what did you expect? and 3) what happened instead?

Reproducer: a small (the smallest?) piece of stand-alone code that demonstrates your trouble

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