VBSript to save an Excel file from Diadem

[Franc]

Hi,

I realized a VBSript with DIADEM to pass data from Diadem to an Excel sheet.
All is OK but I don't know the function on Diadem to save automatically the xls file.
Thanks

[winner]

Hi Franc

Please try this script

Set Excel = CreateObject("Excel.Application")
' Make Excel visible. Until now Excel was not visible.
Excel.Visible = True
' Open prepared DIAdem-Table in Excel.
Excel.Workbooks.Open(AutoDrvLibr & "Diadem.xls")
' Save File
excel.ActiveWorkbook.SaveAs "c:\test.xls"

More information could be found in the example of the DIAdem help (Userinterface>>Interfaces) and the VBA-Help of Excel (keyword OLE)

[Franc]

Thanks a lot for your quick answer

[Fitz]

I'm having a bit of trouble with a simple task using the Excel OLE object in DIAdem.

I need to open a .csv (true comma separated) file to Excel

And then Save As .xls (true Excel 97-2003 format)

When I use the following code, the resulting file is named "result.xls" but is still comma separated inside. Is there a parameter for saving with a specified file format? I see there is such a parameter when the action is recorded in an Excel macro, but I couldn't figure out how to control that parameter in the OLE object.

Set oExcel = CreateObject("Excel.Application")
oExcel.Visible = True
oExcel.Workbooks.Open "C:\source.csv"

oExcel.ActiveWorkbook.SaveAs "C:\test.xls"
oExcel.ActiveWorkbook.save
oExcel.Application.quit

Using DIAdem 10.2/Excel 2007.

Thanks in advance for the help!

[winner]

Hi Fitz

The easiest way to find out the parameters you need is to record a macro in Excel. The parameter you are looking for ist the second parameter in the "saveas" command.

The second parameter must be xlnormal or  -4143. If you add the line autEdTypeLibAdd you can use the Excel constants by their name else you need the value.

' Microsoft Excel 11.0 Object Library

' C:\Programme\Microsoft Office\OFFICE11\EXCEL.EXE

Call AutEdTypeLibAdd("00020813-0000-0000-C000-000000000046", "1.5")

dim oexcel

Set oExcel = CreateObject("Excel.Application")

oExcel.Visible = True

oExcel.Workbooks.Open "C:\source.csv"

oExcel.ActiveWorkbook.SaveAs "C:\test.xls" , xlnormal

'call oExcel.ActiveWorkbook.SaveAs ("C:\test.xls" ,-4143)

oExcel.ActiveWorkbook.save

oExcel.Application.Quit

Hope this helps

Winfried

Winfried

[Fitz]

Winner,

Thank you for your fast reply.

-Fitz