Working with Excel

[smoothdurban]

Hello,

Over the past few days I have began working with exporting data into an excel file.

I have noticed that I needed to generate an Object through the CreateObject command

Dim Excel Set Excel = CreateObject("Excel.Application") 

I have found how to open an existing file

Excel.Visible = True Call Excel.Workbooks.Open("file/location.xls")

write to close and save an excel file.

I was wondering if anyone has a list f the object commands associated with excel for example I can set and excel sheet number by using  

Set ExcelSheetNumber = Excel.Sheets(whatever)

but how do I check if the excel file even exists?  or how do I create one if one doesn't?  It appears to me the API allows me to link things as .Cells or .Open to the application object but I have no idea where I can find additional commands.

Any help would be appreciated.

[Brad_Turpin]

Hi Smooth,

I have also faced the same groping-around-in-a-black-box feeling when using Microsoft Office's ActiveX methods.  I don't have answers to those specific questions.  Ultimately these are questions whose answers should be documented by Microsoft at MSDN.  Whatever methods and properties are available with Microsoft's ActiveX layer can be used by DIAdem VBScript.

It is possible to add some intellisense from Microsoft's type libraries to the DIAdem-SCRIPT panel, but I warn you not to get your hopes up-- the intellisense usually ends way before you get to the item of interest.  You can select the DIAdem-SCRIPT menu "Script>>Type Libraries..." to add Microsoft Intellisense to DIAdem-SCRIPT.

Brad Turpin

DIAdem Product Support Engineer

National Instruments

[Pesmith8]

Mr Smooth

I have done quite a bit of Excel usage in DIAdem(As well as being a power user of Excel VBA).    I would suggest that you develop the commands in Excel vba (Where intellisense if operational, as well as having a good debugger).

These same commands can the be ported into DIAdem.

Regarding testing if a file is present.  DIAdem  Filex works well.

Paul Smith

PS Engineering

[AndreasH_Ha · Accepted solution]

Another option is taking a look at what Microsoft provides : http://msdn.microsoft.com/en-us/library/office/bb726436(v=office.12).aspx

The examples which are available for most of the commands can be really helpful. Here is the specifc link to the Excel Object Mode : http://msdn.microsoft.com/en-us/library/office/bb149081(v=office.12).aspx

[smoothdurban]

Thanks for the help. 

These links have pointed me in the right direction.

[Dia791]

I know that's an old topic, but it fits to my issue.

Is meanwhile any possibility available to get a list of the methods/properties for EXCEL communication?

Following code opens an existing template from the desktop. Then via "cells" I can fill it with info from DIADEM.

Dim DataPath
Dim oExcel, oExcelSheet
Set oExcel = CreateObject("Excel.Application")
DataPath = SHGetFolderPath(&H0000) & "Template_XLS.xlsx"
oExcel.Visible = False
oExcel.Workbooks.Open(DataPath)
Set oExcelSheet = oExcel.Workbooks("Template_XLS.xlsx").Sheets("Sheet1")

oExcel.ActiveWorkbook.Save                  
oExcel.ActiveWorkbook.Close                 
oExcel.Quit                                
'
Set oExcelSheet = Nothing                   
Set oExcel = Nothing

But what I miss are functions like CopySheet, RenameSheet or even CreateWorkbook (in best case with a check if it's already exists...)

At msdn-site of Microsoft some examples are given, but in VBA version. E.g. following works in XLS but not in Diadem:

Worksheets("Sheet1").Copy After:=Worksheets("Sheet3")

Thanks in advance!

[JDragon]

Hi Dia,

In general, you can perform most functions from VBA in VBS but the syntax is going to be slightly different because Objects in Excel are not automatically exposed in DIAdem. Here is a short article on converting VBA to VBS: http://help.mjtnet.com/article/19-converting-office-vba-to-vbscript 

I recommend looking through  the language reference on MSDN for documentation and examples. Like you mentioned, the examples will be in VBA but you should be able to get the same functionality in VBS.

Also, I want to point out that you will probably get more attention from the community and quicker replies if you create a new post instead replying on an old post.

Hope this helps!

[Dia791]

Thanks! Will check and try again 🙂