ExcelCOM UDF

[randallc]

hi,

Thanks for all your persistence!

You are right, I ahd an error in my functions, where the default $Sheet was set to a number intead of a string (expected), so it was always looking at sheet number 1, I think (your test was adding to sheet 1);

thanks again; corrected UDF as well as your etst script $var1=

#include<ExcelCom.au3>

; none of these shorter commands are visible until you say "_XLshow"

dim $FilePath,$var1,$LastRow

$FilePath="C:\A3Test.xls"

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)

EndIf

$var1=_XLadd($FilePath,3,"E",7,30)

_XLshow($FilePath,3)

msgbox (0,"_XLadd=",$var1)

_XLexit($FilePath); Save changes and exit Excel

Best, Randall

[DaLiMan]

Thanks for all your persistence!

<{POST_SNAPBACK}>

You're welcome but I'm about to give up......

Now a problem you mentioned earlier is stopping the script.

Below is the error from SciTe.

>Running: (3.1.1.73):C:\Program Files\AutoIt3\beta\autoit3.exe "P:\Test\TestScripts\_ProbeerUit2.au3"   
C:\Program Files\AutoIt3\Include\ExcelCom.au3 (148) : ==> The requested action with this object has failed.: 
.activesheet.range ($Range).NumberFormat = "General" 
.activesheet.range ($Range).NumberFormat = "General"^ ERROR
>AutoIT3.exe ended.

Which I don't understand because I set no security options at all in my file/sheet.

I just created a new XL file and went from there......

But I'm glad the write function etc. works because that will be the most used I guess.

[randallc]

Hi,

Sorry to hear that!

I am presumably using the same script as you for sheet 3, with the Add function as above!-

And you have the latest Excel.com (I have put number 1_2_1 near top)?

And I am using the spreadsheet you sent!

Oh well, I'll keep at it;

Thanks again.

Randall

[DaLiMan]

Hi Randall,

What do you mean with

(I have put number 1_2_1 near top)

Do you have the same error or does it work as supposed to?

Pls let me now if you have any updates or ideas!

Thnx Daniel

[randallc]

Hi,

I have no error now, so am at a loss as to where to go.

I simply meant I have started putting the number of version of "Excelcom.au3" in the info data near the top so you can check we are running the same version.

Best, Randall

[DaLiMan]

I simply meant I have started putting the number of version of "Excelcom.au3" in the info data near the top so you can check we are running the same version.

<{POST_SNAPBACK}>

Right, hellloooooo....... :">

must be monday-morning......sorry.....

Thanx for the UDF (again)

I really like it!! Keep it up!!

( though not everyone is as enthousiastic as me like I read in another forum....)

Greetings,

Daniel

[randallc]

Yes, OK,...

Mon night here!

EDIT Sept12th 1_3_0 fixed row lines over column "Z" in fileread, tabbed lines

You still have same problem, though, I gather?...

best, Randall

Edited September 12, 2005 by randallc

[DaLiMan]

Yes, OK,...

Mon night here!

<{POST_SNAPBACK}>

Hey, you are living in the future from my point of view.

I'll check it out later today....

[DaLiMan]

Hi Randall,

Still won't work......

Still the same error: ( ; Version: 1_3_0 )

>Running: (3.1.1.73):C:\Program Files\AutoIt3\beta\autoit3.exe "P:\Test\TestScripts\_ProbeerUit.au3"    
C:\Program Files\AutoIt3\Include\ExcelCom.au3 (196) : ==> The requested action with this object has failed.: 
.activesheet.range ($Range).NumberFormat = "General" 
.activesheet.range ($Range).NumberFormat = "General"^ ERROR
>AutoIT3.exe ended.

Everything else works fine though!!!

[randallc]

why, why, why....?

I have run on numbers of machines, and worksheets, even vmware with excel97 - all with no errors!

Can we start from scratch?

1. which line - "add"?

2. both short and long commands?

3. check name or number of worksheet (in desperation, what about "sheet1", then 1, then "1"?)

4. what if you generate a macro in excel to change format of cell to general; is your macro the same as mine?

oh well..

best, Randall

[DaLiMan]

Can we start from scratch? - yes, we can....

1. which line - "add"?

2. both short and long commands? - both

3. check name or number of worksheet (in desperation, what about "sheet1", then 1, then "1"?) - tried it all, no luck

4. what if you generate a macro in excel to change format of cell to general; is your macro the same as mine?just tried it and it won't run. Another macro runs great and manually in Excel the "general" macro does too.....aaaahhhhh...

Don't understand one bit why, but maybe when I have time I'll try this at home this week.

I hope you can still sleep at night......

Going back to work now.......

[blitzkrg]

this is a great UDF..

thanks for making it.. it's going to be very usefull for me as i work heavily with excel.

[randallc]

Hi Blitzkrg,

Thanks for that... can you tell me?..

1. Do you agree that complex macros will best be just called by the "MacroRun" command rather than converting them.?

2. Have you been able to run "Add" commands without the trouble "Daliman" reports?

Best, Randall

[blitzkrg]

I dont use anything with macro's except on my end of year spreadsheets.. so it'll be a few more months before i have to mess with macros.

i am doing alot of reading and writing.. so far no problems..

i didnt use the ADD feature yesterday, but based on your note, i wrote a few lines in my script just to test it for you, and i didnt have any problems..

it added the info as i expected it to..

soo..

i cant say i had any problems..

[DaLiMan]

@blitzkrg

If it ain't to much trouble can you ZIP me your script and XL-file??

I can't seem to get mine to work and I like to test yours (which obviously works ) over here.

Thanx.

[blitzkrg]

create a new spreadsheet c:\blah.xls

and then run this

#include<ExcelCom.au3>
Dim $FilePath, $var13
$FilePath = "c:\blah.xls"
_XLshow ($FilePath, 1)
$var13=_XLadd($FilePath,1,"A",1,30)
Exit

it should put "30" in the 1st cell in row A

Edited September 14, 2005 by blitzkrg

[randallc]

Hi,

For completenes, i would "add" twice;

and I would always exit (or, new, "close", which ill leave Excel running) else you'll find later the script won't work as the worksheet is "already open" if you run it again;

$var13=_XLadd($FilePath,1,"A",1,30)

_XLshow ($FilePath, 1)

MsgBox(0,"$FilePath=",$FilePath)

MsgBox(0,"","")

_XLexit($FilePath,"NoSave")

Exit

[blitzkrg]

if he does "no save" and exit

it wont save his work, correct?

so when he opens the spreadsheet to see what it did, he'd find nothing.

if you just go and double click on it, it will say it's already running..

but that's ok.. since you only had a problem with "add" right?

[randallc]

Yes, OK!

the "show" and message box read the changes even if they are not saved; the exit without the "NoSave", or with a save as well would save too if needed.

Unfortunately, DaliMan has a problem I haven't sorted out yet, and cannot reproduce.......

Best, Randall

[randallc]

Updated Excel com to include fast import facility without having to use macro or Excel import function.

This is as quick as importing via Data import in excel (eg 388Mb, 23000x200 rows/cols in 2 minutes; [3000Kb=3Mb/sec?])

Simply opens the "csv" file as object, then copies "used range", then pastes where asked

column number just upperleft cell name only

see how it works; (last function in Excel.com)

Usage;

#include<ExcelCom.au3>

_XLcsvPaste(@ScriptDir&"\book1.xls",1,3,5,"Save",@ScriptDir&"\test1.csv")

Exit

Best, Randall Edited September 18, 2005 by randallc