I have created an Excel file through Cognos Reportnet SDK. This Excel file has a reference to another htmlfile which has the data. So when I open the Excel File, I get this message:

Some of the files in this web page aren't in the expected
location. DO you want to download them anyway? If you're sure the
web page is from a trusted source, click Yes.

When I click on Yes, the Excel opens up with the data. I have Excel 2000. In my vb.net code I would like to save the Excel file as new.xls, which will now contain the data, without having to look at the external html file. My code is as below. I am unable to achieve this. ANy help would be appreciated. Thanks

Dim filename as string="c:\temp\test.xls"
Dim as system.object
Dim objExcelWOrkbook as New Excel.Application
objExcelWorkbook.DisplayAlerts=False
wa=objExcelWorkBook.Workbooks.Open(filename)
objExcelWorkbook.Activebook.SaveAs("C:\temp\new.xls")
objExcelWorkbook.quit

Recommended Answers

All 2 Replies

Hi,

I am a little bit rusted on MS Office COM objects but you can try to select some range or the whole Sheet1 and call the equivalent COM function of paste special with parameters specifying copy just the values and the formatting (not the formulas or the references) then you can paste it over the Sheet1 (or if this won't work paste it to Sheet2 and delete Sheet1) and save the workbook.

Loren Soth

Thanks very much. This is a very good solution.
I was able to resolve this by starting an independent thread in my vb.net application to respond to the Excel warning (using sendkeys). This way I am able to save the excel file with data.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.