Excel Template: Merging data and saving workbooks

Question

I am on my first foray into Excel Interop and after a flying start have hit a wall.

I have an Excel template that contains one sheet which is an assessment form and another which contains guidance notes for how to carry out the assessment.

I also have an XML file with the details of the projects being assessed.

I need to merge the project title, application number and company name into the first sheet and then save the sheet with the filename [Application No] - [Project Title].xlsx.

The first part is working fine. I have Loaded the XML and the code is putting the data into the form where it should be.

My problem is the saving part. I found the .SaveAs method and it creates a couple of files... but they won't open. I then get a HRESULT error 0x800A03EC - Searching the web has explained nothing about this. Something is telling me that this.SaveAs() is referring to the worksheet rather than the work book but I am just guessing there.

I am hoping I have done something stupid and it is an easy fix.

For reference here is my code but like I say I am not sure how useful it is.

    private void MergeData()
    {
        doc.Load(@"C:\XML Data\source.xml");

        XmlNodeList forms = doc.SelectNodes("//form1");

        for (int i = 0; i < forms.Count; i++)
        {
            XmlNodeList nodes = forms[i].ChildNodes;

            string refNo = nodes[0].InnerText.ToString();
            string companyName = nodes[3].InnerText.ToString();
            string title = nodes[1].InnerText.ToString();

            this.Cells[9, 4] = title;
            this.Cells[11, 4] = refNo;
            this.Cells[14, 4] = companyName;


            this.SaveAs(@"C:\Assessment Forms\" + refNo + " - " + title + ".xlsx");
        }
    }

Does anyone know how to save these files?

Thanks for reading

EDIT--

I have found this article

C# and Excel Interop issue, Saving the excel file not smooth

and changed the code to include its suggestion

Excel.Application app = this.Application;
Excel.Workbook wb = app.Workbooks.Add(missing);
wb.SaveAs(@"C:\Assessment Forms\" + refNo + " - " + title + ".xlsx");

But it is doing the same.

I am on a deadline so think I am going to have to start copying, pasting and saving manually :(

chris neilsen · Accepted Answer · 2011-04-12 11:40:54Z

1

Not sure what object this is here, but if you are correct in assuming this is a worksheet, try this.Parent.SaveAs

Alternatively if this turns out to be a range, try this.Worksheet.Parent.SaveAs

answered Apr 12, 2011 at 11:40

chris neilsen

53.3k10 gold badges91 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Slotty Over a year ago

Thanks for this. I went back to look at it and you were right I was coding on the sheet. Instead of using this.Parent.SaveAs I put the code under the workbook and that part of it is fixed.

Collectives™ on Stack Overflow

Excel Template: Merging data and saving workbooks

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related