Jump to content

ExcelCOM UDF


randallc
 Share

Recommended Posts

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
Link to comment
Share on other sites

  • Replies 242
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

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:

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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:

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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
Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...