Jump to content

This site uses cookies. By continuing to browse the site you are agreeing to our use of cookies. Find out more here. X
X


Photo

ExcelCOM UDF


  • Please log in to reply
242 replies to this topic

#21 randallc

randallc

    Universalist

  • Active Members
  • PipPipPipPipPipPip
  • 1,902 posts

Posted 07 September 2005 - 10:10 PM

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







#22 DaLiMan

DaLiMan

    Universalist

  • Active Members
  • PipPipPipPipPipPip
  • 386 posts

Posted 08 September 2005 - 06:59 AM

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:
- My Auto-It File'sExplorer/ File Opener - Little tool to easy access the frequently used files and folders.Version: 1.0.3.3WinSlide UDF - A UDF to slide your GUI from A to B on the screen.DateCrackerDL - Little tool to fool some date limited trail software.Version: 0.5.1

#23 randallc

randallc

    Universalist

  • Active Members
  • PipPipPipPipPipPip
  • 1,902 posts

Posted 11 September 2005 - 06:44 AM

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

#24 DaLiMan

DaLiMan

    Universalist

  • Active Members
  • PipPipPipPipPipPip
  • 386 posts

Posted 12 September 2005 - 06:58 AM

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
- My Auto-It File'sExplorer/ File Opener - Little tool to easy access the frequently used files and folders.Version: 1.0.3.3WinSlide UDF - A UDF to slide your GUI from A to B on the screen.DateCrackerDL - Little tool to fool some date limited trail software.Version: 0.5.1

#25 randallc

randallc

    Universalist

  • Active Members
  • PipPipPipPipPipPip
  • 1,902 posts

Posted 12 September 2005 - 08:42 AM

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

#26 DaLiMan

DaLiMan

    Universalist

  • Active Members
  • PipPipPipPipPipPip
  • 386 posts

Posted 12 September 2005 - 08:58 AM

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
- My Auto-It File'sExplorer/ File Opener - Little tool to easy access the frequently used files and folders.Version: 1.0.3.3WinSlide UDF - A UDF to slide your GUI from A to B on the screen.DateCrackerDL - Little tool to fool some date limited trail software.Version: 0.5.1

#27 randallc

randallc

    Universalist

  • Active Members
  • PipPipPipPipPipPip
  • 1,902 posts

Posted 12 September 2005 - 09:38 AM

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 by randallc, 12 September 2005 - 10:14 AM.


#28 DaLiMan

DaLiMan

    Universalist

  • Active Members
  • PipPipPipPipPipPip
  • 386 posts

Posted 12 September 2005 - 09:41 AM

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:
- My Auto-It File'sExplorer/ File Opener - Little tool to easy access the frequently used files and folders.Version: 1.0.3.3WinSlide UDF - A UDF to slide your GUI from A to B on the screen.DateCrackerDL - Little tool to fool some date limited trail software.Version: 0.5.1

#29 DaLiMan

DaLiMan

    Universalist

  • Active Members
  • PipPipPipPipPipPip
  • 386 posts

Posted 12 September 2005 - 03:03 PM

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
- My Auto-It File'sExplorer/ File Opener - Little tool to easy access the frequently used files and folders.Version: 1.0.3.3WinSlide UDF - A UDF to slide your GUI from A to B on the screen.DateCrackerDL - Little tool to fool some date limited trail software.Version: 0.5.1

#30 randallc

randallc

    Universalist

  • Active Members
  • PipPipPipPipPipPip
  • 1,902 posts

Posted 13 September 2005 - 08:26 AM

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

#31 DaLiMan

DaLiMan

    Universalist

  • Active Members
  • PipPipPipPipPipPip
  • 386 posts

Posted 13 September 2005 - 09:34 AM

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:

- My Auto-It File'sExplorer/ File Opener - Little tool to easy access the frequently used files and folders.Version: 1.0.3.3WinSlide UDF - A UDF to slide your GUI from A to B on the screen.DateCrackerDL - Little tool to fool some date limited trail software.Version: 0.5.1

#32 blitzkrg

blitzkrg

    Polymath

  • Active Members
  • PipPipPipPip
  • 232 posts

Posted 13 September 2005 - 03:22 PM

this is a great UDF..
thanks for making it.. it's going to be very usefull for me as i work heavily with excel.

#33 randallc

randallc

    Universalist

  • Active Members
  • PipPipPipPipPipPip
  • 1,902 posts

Posted 13 September 2005 - 11:58 PM

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

#34 blitzkrg

blitzkrg

    Polymath

  • Active Members
  • PipPipPipPip
  • 232 posts

Posted 14 September 2005 - 01:19 PM

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

#35 DaLiMan

DaLiMan

    Universalist

  • Active Members
  • PipPipPipPipPipPip
  • 386 posts

Posted 14 September 2005 - 02:23 PM

@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
- My Auto-It File'sExplorer/ File Opener - Little tool to easy access the frequently used files and folders.Version: 1.0.3.3WinSlide UDF - A UDF to slide your GUI from A to B on the screen.DateCrackerDL - Little tool to fool some date limited trail software.Version: 0.5.1

#36 blitzkrg

blitzkrg

    Polymath

  • Active Members
  • PipPipPipPip
  • 232 posts

Posted 14 September 2005 - 06:08 PM

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, 14 September 2005 - 06:10 PM.


#37 randallc

randallc

    Universalist

  • Active Members
  • PipPipPipPipPipPip
  • 1,902 posts

Posted 14 September 2005 - 09:57 PM

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



#38 blitzkrg

blitzkrg

    Polymath

  • Active Members
  • PipPipPipPip
  • 232 posts

Posted 15 September 2005 - 02:24 AM

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?

#39 randallc

randallc

    Universalist

  • Active Members
  • PipPipPipPipPipPip
  • 1,902 posts

Posted 15 September 2005 - 04:26 AM

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

#40 randallc

randallc

    Universalist

  • Active Members
  • PipPipPipPipPipPip
  • 1,902 posts

Posted 17 September 2005 - 11:55 AM

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, 18 September 2005 - 01:35 PM.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users