Excel Automation Docs ?

Hello guys,

Im trying to get this Excel automation working on Windows but i dont seem to find any details about it any docs or something related to the methods , properties and so on .

Basically what i`m trying to achieve is to open a excel sheet, to count the total columns and rows it has filled, then to get the first row which is the header, match them all in the code and db and do a full import of all the rows based on the header details.

So far i managed to get the sheet name but still more than that nothing .

I did tried to see the data with this code but it crashes the app, once it reaches to the sheet.Columns.Count.ToText , it says [quote] Not automation Server present [/quote] .

Xojo 2017R2

[code]Dim excel As New ExcelApplication
Dim sheet As ExcelWorksheet

If Excel.Workbooks.Count > 0 Then
sheet = excel.ActiveSheet
lblSheetName.Text = sheet.Name
lblTotalColumns.Text = sheet.Columns.Count.ToText
lblTotalRows.Text = sheet.Rows.Count.ToText

End If

[/code]

Any idea and help on this ?

Sheet is constantly updating on a shared drive until the app is ready so i need to be able to open it frequently and to fetch data from it for processing purposes.

It will be great as well to know how do i open a specific excel file in the drive , how to select the desired sheed and how to get the data from it .

Thanks a lot in advance.

@Aurelian Negrea
this is what you need.

[quote=349213:@Joost Rongen]@Aurelian Negrea
this is what you need.[/quote]
Thanks , got it, looking over it. It would be nice if XOJO had that thing as well on their docs.

Thanks again.

The Office Automation overview page is here:

http://developer.xojo.com/office-automation-overview

Example projects for Word, Excel and PowerPoint are here:
Examples/Platform-Specific/Windows/Office Automation

Webinar is here:
http://developer.xojo.com/webinar-office-automation

For full details on all the Office Automation commands you’ll need to refer to Microsoft’s Office object model and dev docs.

Eugene’s book (linked by Joost) has lots of Xojo examples.

1 Like

And even MBS and Einhugur provide plugins for creating Excel sheets from Xojo code without the Excel necessarily installed.

Well it seems that there is something wrong with the latest Xojo in regards to the office automation, just deleted all the versions and keep just the latest one and i cannot use excel anymore, code that before worked now it does not anymore.

It seems that the references are missing, i will download back the old versions and see where it crashes and update.

Thanks.

The Office plugins are not installed by default any more. Copy them from Extras\Office Automation into the Plugins folder. (And restart Xojo.)

Well i have that added in the first place but it seems that still does not work, something is wrong there. I checked under About , plugins and i have all of them but still no autocomplete on it.

I get only this no matter what i do .

You said it works in previous versions of Xojo in the past, but not anymore ?

Did you had any windows updates lately. Could be Windows has reset it self not to allow running COM by default. Or your virus checker

[quote=350756:@John Hansen]You said it works in previous versions of Xojo in the past, but not anymore ?

Did you had any windows updates lately. Could be Windows has reset it self not to allow running COM by default. Or your virus checker[/quote]
Hi John,

Well the code in a way works, i guess as i can test it on windows and get results, what is hard and annoying are the auto complete parts as you start to type and not having any specific documentation to look for it`s hard to look for all the functions.

I looked on that .rbx file and it seems that all it`s there but for some reasons no auto complete is happening which reduces the programming speed a lot.

Im writing on mac and testing on windows via debugger so maybe that could be an issue as im not on windows while coding but i don`t think so.

Any ideas are more than welcomed.

Thanks.

[quote=349225:@Paul Lefebvre]The Office Automation overview page is here:

http://developer.xojo.com/office-automation-overview

Example projects for Word, Excel and PowerPoint are here:
Examples/Platform-Specific/Windows/Office Automation

Webinar is here:
http://developer.xojo.com/webinar-office-automation

For full details on all the Office Automation commands you’ll need to refer to Microsoft’s Office object model and dev docs.

Eugene’s book (linked by Joost) has lots of Xojo examples.[/quote]
Hi Paul,

I got the book but unfortunately still issues due to the autocomplete part which i wish it was there . as for the documentation on the xojo website does not help to much and all the urls are confusing and a lot to look for. I assumed that the purpose of that plugin is to offer you the details for the office part so that you doit faster but it seems that its getting more confusing. i`ll start to dig into that plugin to see what i can get from there as references and see how i can work with it.

Thanks.

I would suggest you are testing your code straight on windows first, to make sure this is not the case. At least you have removed one concern from the problem.

Hello Aurelian,

From my experience with the plugin, it seems like autocomplete is a bit of hit-and-miss, where some autocomplete exists, and some functions do not appear. The good news is that you can type the command and run it to see if the function works.

Autocomplete become more complicated when trying to use different functions in the many versions of Microsoft Office. Some new functions exist and work on new Office versions while other functions work on older Office versions.

Autocomplete needs many updates, and the good news is that the functions work well on old and new versions of Office, so you can get your work done. :slight_smile:

[quote=350788:@Eugene Dakin]Hello Aurelian,

From my experience with the plugin, it seems like autocomplete is a bit of hit-and-miss, where some autocomplete exists, and some functions do not appear. The good news is that you can type the command and run it to see if the function works.

Autocomplete become more complicated when trying to use different functions in the many versions of Microsoft Office. Some new functions exist and work on new Office versions while other functions work on older Office versions.

Autocomplete needs many updates, and the good news is that the functions work well on old and new versions of Office, so you can get your work done. :)[/quote]
Thanks Eugene,

Unfortunately ODBC does not work as well, i guess its more a restriction than a issue, so i will go old way, export that in .csv and work with that as it will take ages to ask IT to troubleshoot and enable the needed apps so ill see how it goes.

As for the plugin part, indeed its more like guessing than doing it so i will try to avoid using that now, maybe when it will be more updated and have proper display, but seeing the interest for this being so low i dont see Xojo updating that fast.

Thanks.