Excel.Application SaveAs/Export...

[jennuhw]

I am trying to open an Excel spreadsheet and do a saveas after making changes.

Dim objXlApp, objXlSheet

' create the Excel object
Set objXlApp = Server.CreateObject("Excel.Application")

' open the spreadsheet file
objXlApp.Workbooks.Open server.mappath("sunhua.xls")
'objxlapp.usercontrol = true

' get the worksheet
Set objXlSheet = objXlApp.Worksheets("Quote Form")
' read/write from the worksheet
Response.Write(objXlSheet.Range("q4").Text)
objxlsheet.range("C6").value = now()
objxlsheet.range("D6").value = lors.fields("custid")
objxlsheet.range("E6").value = lors.fields("esppart")
objxlsheet.range("F6").value = lors.fields("partnumber")
objxlsheet.range("G6").value = lors.fields("drawingnum")
objxlsheet.range("H6").value = lors.fields("eau")
objxlsheet.range("K6").value = samples

'save workbook
nameoffile = "E:\Webs\Intranet\Global\sunhua\quotes\" & objxlsheet.range("B6").text & ".xls"
'response.write "<BR>" & nameoffile
'call objxlsheet.Export (nameoffile, 0)
call objXlApp.export (nameoffile) 'have changed to saveas with same error 
'call objxlapp.save does not give an error and saves file

I am getting the following error:

Microsoft VBScript runtime error '800a01b6' 

Object doesn't support this property or method: 'export' 

/global/sunhua/sunhuaquote.asp, line 78 

I have tried saveas with the same error.  If I change it to save, it works fine without any problems.  I have created new spreadsheets before, but never edited one and tried to save it under a new file name.

 

[Sheco]

Use the Object Browser in VB to check out the properties and methods available on the Excel Application, Workbook, and Worksheet objects.

 

[jennuhw]

I don't have VB installed. I have vb.net. Wouldn't that be different? Thanks.

 

[Sheco]

You can use the object browser in .NET

First make a new dummy throw-away project and then add a reference to the Excel Object Library. I found the one for Office 2000 under Program Files/Microsoft Office/Office/Execl9.OLB

Then fire up the Object Browser and you can see the classes, interfaces, and constants. You'll specifically be interested in the classes named Application, Workbook, and Worksheet.

 

[jennuhw]

I have been looking in the object browser in excel VBA. Thanks!

 

[jennuhw]

I tried the workbook object, but it didn't work. I ended up copying the file and working with the copied file instead of doing a saveas. Thanks.