Use vsto to copy and save some EXCEL cells (in the format) to another Excel file.

Source: Internet
Author: User

This article from: http://www.cnblogs.com/zhaojunqi/archive/2009/04/23/1442124.html

 

Recently, vsto was used to create a small project. One of the requirements is to save many areas with formats in an Excel worksheet to a separate EXCEL file, and retain the source format.
Although the requirements are simple, there are several technical points to understand:
1. Format replication, but only copying values and formats, without copying formulas, references, verification, and so on.
2. Save as Excel
First, I thought it was very simple at first. It was not that simple to directly use the selective paste and paste everything. Start using the followingCode:

Pastespecial
WS. get_range (begincell, pasterange). pastespecial

(Microsoft. Office. InterOP. Excel. xlpastetype. xlpasteall,

Microsoft. Office. InterOP. Excel. xlpastespecialoperation. xlpastespecialoperationnone,

Type. Missing, type. Missing );

 

However, we found that all the copied values were not copied in the format. So I studied Microsoft. Office. InterOP. Excel. xlpastetype and found that there were more than a dozen options. I tried them one by one and found that none of the enumerated values could meet my requirements. Later, I tried two copies to solve the problem, one copy value and one copy format! The Code is as follows:

Pastespecial
WS. get_range (begincell, pasterange). pastespecial

(Microsoft. Office. InterOP. Excel. xlpastetype. xlpastevalues,

Microsoft. Office. InterOP. Excel. xlpastespecialoperation. xlpastespecialoperationnone,

Type. Missing, type. Missing );
WS. get_range (begincell, pasterange). pastespecial

(Microsoft. Office. InterOP. Excel. xlpastetype. xlpasteformats,

Microsoft. Office. InterOP. Excel. xlpastespecialoperation. xlpastespecialoperationnone,

Type. Missing, type. Missing );

 

The second problem is not difficult. It is mainly because different object models use different storage methods (the WB object is Microsoft. office. interOP. excel. workbook object. I used Microsoft in my previous experiments. office. interOP. excel. applicationclass). After testing, the workbook object's savecopyas method is ideal because it does not pop up any dialog box and automatically overwrites existing files with the same name.
The complete code is listed below:

Commandbarbutton
Void Creatcommand_click (Microsoft. Office. Core. commandbarbutton Ctrl, Ref   Bool  

Canceldefault)
{
// Start a new Excel process, but it is not displayed
Clipboard. Clear ();
Globals. sheet1.application. screenupdating =   False ;
Microsoft. Office. InterOP. Excel. Application app =   New  

Microsoft. Office. InterOP. Excel. Application ();
App. Visible =   False ;

// Repeat and save it
For ( Int I =   1 ; I <   14 ; I ++ )
{
// Obtain the file name and copy area.
Globals. sheet10.range [ " O1 " , Type. Missing]. value2 = I;
String Filename = Globals. thisworkbook. Path + Utility. creatfilename

(Globals. sheet1.range [ " B5 " , Type. Missing]. value2.tostring (), globals. sheet3.range [ " B2 " ,

Type. Missing]. value2.tostring (), globals. sheet1.range [ " C5 " , Type. Missing]. value2.tostring

());
String Strpasterange = Utility. selectandcopyrange ( " B " , 5 , 9 , " N " , 22 );

// Save as Method
Utility. creatandpaste (app, strpasterange, " B5 " , Filename );
}

// Close the Excel Process
Globals. sheet1.application. screenupdating =   True ;
App. Quit ();

MessageBox. Show ( " Success! " );
}

 

The utility. selectandcopyrange method is used to copy the specified region. The code is simple and the core code is as follows:

Globals. sheet1.range [begincell + Begincellnamber. tostring (), strpasterange]. Copy

(Type. Missing );

 

The utility. creatandpaste method is saved as follows:

 

Creatandpaste
Public   Static   Void Creatandpaste (Excel. Application app, String Pasterange, String Begincell, String Filename)
{
// Use a new Excel process to add a new workbook
Microsoft. Office. InterOP. Excel. Workbook WB = App. workbooks. Add (type. Missing );
Excel. worksheet WS = (Excel. worksheet) WB. worksheets [ 1 ];

// Paste
ws. get_range (begincell, pasterange ). pastespecial (Microsoft. office. interOP. excel. xlpastetype. xlpastevalues, Microsoft. office. interOP. excel. xlpastespecialoperation. xlpastespecialoperationnone, type. missing, type. missing);
ws. get_range (begincell, pasterange ). pastespecial (Microsoft. office. interOP. excel. xlpastetype. xlpasteformats, Microsoft. office. interOP. excel. xlpastespecialoperation. xlpastespecialoperationnone, type. missing, type. missing);
ws. get_range (begincell, pasterange ). columns. autofit ();
// Save
WB. savecopyas (filename );

// Close a workbook
Clipboard. Clear ();
WB. Close ( False , Type. Missing, type. Missing );

}

 

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.