excel - How to copy values only using Sheets(Array()).Copy -

-

i have following code copies worksheets new workbook. copies sheets formulae etc. after loop though each worksheet in resultant workbook copy , paste values. there way create worksheets values first time round?

sheets(array("eq spot moves valuation", _                  "zari swap prll", _                  "zari bond prll", _                  "zar swap pdh", _                  "zar bond pdh", _                  "grid report eq", _                  "valuations", _                  "zar bond prll pdh", _                  "grid report fx")).copy   ' ...snip    each ws in exposureoutputwb.sheets    ' copy , past values on each sheet    next ws

there difference copy method if applied worksheets or ranges. ranges can use pastespecial [format] later on, must copy ranges beginning, in

sub copyallasvalues() dim wo workbook, worksheet           ' old dim wn workbook, sn worksheet           ' new      set wo = activeworkbook                   ' come     set wn = workbooks.add                    ' new sheet values      each in wo.worksheets         set sn = wn.worksheets.add            ' create new sheet         sn.name = so.name                     ' copy name         so.cells.copy                         ' copy cells clipboard         sn.[a1].pastespecial xlpastevalues    ' paste values @ a1         sn.[a1].pastespecial xlpasteformats   ' paste formats @ a1     next end sub

this code should illustrate principle, doesn't care correct positioning of sheets (use before or after parameters on worksheets.add)and not duplicate sheet names may occur if 1 of original sheet names match default names in newly created workbook on system (usually sheet1 .. sheet3)

hope helps

good luck


Comments

Post a Comment

Popular posts from this blog

SAP Web Service from .NET via WCF -

-
i'm trying consume sap web service .net via wcf. i've generated proxy , have configured app.config file. here test code: webservicesap.ztest_rfcclient mywcfservice = new webservicesap.ztest_rfcclient("myendpoint"); mywcfservice.clientcredentials.username.username = "<username>"; mywcfservice.clientcredentials.username.password = "<password>"; webservicesap.ztestrfc parameter = new webservicesap.ztestrfc(); parameter.testinput = "this simple test"; webservicesap.ztestrfcresponse response = mywcfservice.ztestrfc(parameter); console.writeline(reponse.testoutput); console.readline(); the ztestrfc sap method simple function accepts input string, , outputs: "result: <the input string>" when call ztestrfc method, got null value in variable response. soap messages seem fine. soap request <messagelogtracerecord> <httprequest xmlns="http://schemas.microsoft.com/2004/06/servic...
Read more

c# - Getting "Internal .Net Framework Data Provider error 30" error when column has NULL value -

-
i have simple .net console program uses odbcdatareader fetch rows advantage local server. using system dsn defined via odbc administrator on windows 7. data source uses advantage streamlinesql odbc driver v 10.00.00.03 , following params: database path: table type: advantage available server types: local server (als) the program works fine until encounters row has null value in column. exception: system.invalidoperationexception caught message=internal .net framework data provider error 30. source=system.data stacktrace: @ system.data.providerbase.dbbuffer.validate(int32 offset, int32 count) @ system.data.providerbase.dbbuffer.ptrtostringuni(int32 offset, int32 length) @ system.data.odbc.odbcdatareader.internalgetstring(int32 i) @ system.data.odbc.odbcdatareader.getvalue(int32 i, typemap typemap) @ system.data.odbc.odbcdatareader.getvalue(int32 i) @ system.data.odbc.odbcdatareader.isdbnull(int32 i) @ odbctest.program....
Read more

c++ - How to modify context menu of internet explorer using IDocHostUIHandler::ShowContextMenu? -

-
i trying implement custom menu internet explorer 7.0. have use idochostuihandler::showcontextmenu only. till able implement basic context menu 2 options. problem disabled default. sample code same is: hresult cwebeventhandler::showcontextmenu(dword dwid,point *ppt, iunknown *pcmdtarget, idispatch *pdispobject) { if (false) // put guard code here. of not consider return s_false; // show standard context menus. else { iolewindow* pwnd = null; hresult hr = pcmdtarget->queryinterface(iid_iolewindow, (void**) &pwnd); if (succeeded(hr)) { hwnd hwnd; if (succeeded(pwnd->getwindow(&hwnd))) { hmenu menu = ::createpopupmenu(); ::appendmenu(menu, mf_string, id_hello, l"&hello" ); // id_hello & id_world 2 menu resource items ::appendmenu(menu, mf_string, id_world, l"&world" ); ...
Read more