Sign in to follow this  
Followers 0
randallc

ExcelCOM UDF

243 posts in this topic

#21 ·  Posted

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

Share this post


Link to post
Share on other sites



#22 ·  Posted

Thanks for all your persistence!

<{POST_SNAPBACK}>

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

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...... :whistle:

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

Share this post


Link to post
Share on other sites

#23 ·  Posted

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

Share this post


Link to post
Share on other sites

#24 ·  Posted

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

Share this post


Link to post
Share on other sites

#25 ·  Posted

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

Share this post


Link to post
Share on other sites

#26 ·  Posted

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....) :P

Greetings,

Daniel

Share this post


Link to post
Share on other sites

#28 ·  Posted

Yes, OK,...

Mon night here!

<{POST_SNAPBACK}>

Hey, you are living in the future from my point of view. ;):P

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

Share this post


Link to post
Share on other sites

#29 ·  Posted

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!!! :P

Share this post


Link to post
Share on other sites

#30 ·  Posted

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

Share this post


Link to post
Share on other sites

#31 ·  Posted

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...... :P:oops:

Going back to work now....... ;):mad2:

Share this post


Link to post
Share on other sites

#32 ·  Posted

this is a great UDF..

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

Share this post


Link to post
Share on other sites

#33 ·  Posted

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

Share this post


Link to post
Share on other sites

#34 ·  Posted

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..

Share this post


Link to post
Share on other sites

#35 ·  Posted

@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. :P

Share this post


Link to post
Share on other sites

#36 ·  Posted (edited)

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 by blitzkrg

Share this post


Link to post
Share on other sites

#37 ·  Posted

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

Share this post


Link to post
Share on other sites

#38 ·  Posted

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?

Share this post


Link to post
Share on other sites

#39 ·  Posted

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

Share this post


Link to post
Share on other sites

#40 ·  Posted (edited)

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 by randallc

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