win32::OLE::Excel and lists

[Dave]

We have an databse where i log into via ODBC and get a lot of stuff
after some sorting i throw this into an excel file as a list. I do this
via win32:OLE, the data to excel that is.

Now i would like to synchronize this data to an sharepoint server.
there is functinalitu for this in Excel 2003 that you can after you
have published the data you can synchronize it via a small button
called "synchronize list". pressing it manually works fine, but i would
like to push this button from win32::OLE::Excel, any body know how? I
recorded a macro for this feature and looked how it looks under VB and
it said:


ActiveSheet.ListObjects("List1").UpdateChanges xlListConflictDialog

so in my perl head that translates to:

$Excel->ActiveWorkbook->ListObjects('List1')->UpdateChanges('xlListConflictDialog');

this didn't work and i get an:

Win32::OLE(0.1502) error 0x80020003: "Member not found in
METHOD/PROPERTYGET "" at M:\23044822_eclipse\KIetc..


anybody got any ideas on how to press that button from win32::OLE?

//Dave

 

[A. Sinan Unur]

We have an databse where i log into via ODBC and get a lot of stuff
after some sorting i throw this into an excel file as a list. I do
this via win32:OLE, the data to excel that is.

Now i would like to synchronize this data to an sharepoint server.
there is functinalitu for this in Excel 2003 that you can after you
have published the data you can synchronize it via a small button
called "synchronize list". pressing it manually works fine, but i
would like to push this button from win32::OLE::Excel, any body know
how? I recorded a macro for this feature and looked how it looks under
VB and it said:


ActiveSheet.ListObjects("List1").UpdateChanges xlListConflictDialog

so in my perl head that translates to:

$Excel->ActiveWorkbook->ListObjects('List1')->UpdateChanges
('xlListConflictDialog');

this didn't work and i get an:

I have to admit I get confused very easily when dealing with Win32::OLE,
but here is something that struck me (I also do not have the requisite
programs to try this out).

xlListConflictDialog seems to me to be a constant. Now, if you have
imported Excel constants using:

use Win32::OLE::Const 'Microsoft Excel';

then, this constant will be available to you as a bareword, i.e. you
should not quote it to get the value.

See also:

<URL:http://msdn.microsoft.com/library/default.asp?url=/library/en-
us/dno2k3ta/html/odc_OfOfficeSysandWSS.asp>

which lists other constants you can use:

* xlListConflictDiscardAllConflicts indicates to accept the version of
the data stored on the SharePoint list
* xlListConflictError indicates to raise an error if a conflict occurs
* xlListConflictRetryAllConflicts indicates to overwrite the version of
the data stored on the SharePoint list

Sinan

 

[Dave]

Sorry for not replying sooner, had to do a bunch of other things!

Thanks for the link good stuff there.

Tried

$Excel->ActiveWorkbook->xlListConflictRetryAllConflicts();

didn't work same error.

I allready have created the list on the web so its just a matter of
updating it regulary, i know the data in the excel file is always
accurate, so it should always be correct and replace any data on the
sharepoint server. Seems i am in uncharted territory here

//Dave

 

[A. Sinan Unur]

Sorry for not replying sooner, had to do a bunch of other things!

Especially in this case, it would have been extremely nice of you
to actually quote an appropriate amount of context so I did not
have to go around digging for my response to you.

Thanks for the link good stuff there.

Tried

$Excel->ActiveWorkbook->xlListConflictRetryAllConflicts();

didn't work same error.

Well, here is what I suggested:

#> #>
#>> ActiveSheet.ListObjects("List1").UpdateChanges xlListConflictDialog
#>>
#>> so in my perl head that translates to:
#>>
#>> $Excel->ActiveWorkbook->ListObjects('List1')->UpdateChanges
#>> ('xlListConflictDialog');
#>>
#>> this didn't work
#>
#> xlListConflictDialog seems to me to be a constant.
...
#> should not quote it to get the value.
#>

In my opinion, that translates into

$Excel->ActiveWorkbook->ListObjects('List1')->UpdateChanges(xlListConflictDialog);

Sinan