fails to create excel sheet diminically by using NamedRange.In

Programming and Web Development Forums - Microsoft OFFICE - Microsoft Office products

Skip to content

fails to create excel sheet diminically by using NamedRange.In

Microsoft OFFICE
Post a reply

    Next

  • 1. Does anyone know how to access options in the "Grid and Guides" di
    I'm using PowerPoint 2007, Vista and VSTO 2008 What I'm trying to do is following: In PowerPoint, user can manually chose snap settings. In PowerPoint, users would right click on the slide in normal view (slide pane) and then select "Grid and Guides..." from the context menu. In the new dialog "Grid and Guides" they can select one of the following Snap to --> Snap objects to grid --> Snap objects to other objects --> Grid settings --> Spacing Display grid on screen --> Guide settings --> Display drawing guides on screen I'd like to turn on or off the following settings from my code, that is at run time. >>> Snap objects to grid >>> Snap objects to other objects >>> Display grid on screen >>> Display drawing guides on screen Snap to grid is easy: --> Globals.My_Add_In.Application.ActivePresentation.SnapToGrid = True Display grid on screen is easy as well: --> Globals.My_Add_In.Application.ActivePresentation.Application.DisplayGridLines = True But I can't find out how to set the other options. Help is highly appreciated, I've already spent hours without success. A work around is fine too! Any suggestions? Thanks!
  • 2. Disabling activex/macros without notification for automation app
    Hi, I have an application which uses Word/Powerpoint/Excel 2007 for generating PDF files via automation which works well, using the PDF export plugin. Occasionally, I have a document with some activex / macros, and have found that via automation, invoking saveas to pdf can for example cause Word to enter what seems to be an infinite loop (runs forever with a lot of CPU consumed with no progress). I can't reproduce this when I perform the actions manually using the GUI. I discovered the culprit document had some activex/macro content. I changed my settings in the Word settings trust centre to unconditionally not run any activex/macro content and to not notify me about it. Interestingly, this fixed the problem - automation worked fine on this document again. Is there a way via automation to unconditionally not run any activex/macro content wihtout notification? My application will be installed on a lot of machines, and I'd prefer not to have to do this security change manually on each machine. Or is there another way outside of office to set this change? Thanks in advance for any advice.
  • 3. disable Excel AutoExec macro when opening via Access Automation
    How can I disable the AutoExec macro within an Excel workbook, when I open it via automation?
  • 4. Detecting Outlook shutdown
    Hi, I am doing some simple stuff with Outlook interop and got cought in something a bit silly. To get hold on the running outlook instance I use: Marshal.GetActiveObject("Outlook.Application") the problem is that sometimes that call returns a valid outlook instance but that instance is in the process of shutting down so I run into problems a couple of lines later. Is there anyway to know if the outlook application is going down and thus it cannot be considered as a running office application? something like a flag or a reference being set to null? Thanks, Federico.
  • 5. Extract Non-OLE attachments from OLE Files(Office Files)
    Hi there, I want to extract PDF, ZIP or any other file (non-ole) file embedded inside OLE File such as (DOC,XLS or PPT). but i am unable to do so. I have been using .NET and Office COM component. I can extract OLE attachments but cant extract Non-OLE objects, the only thing i can do is to activete them. I cant receive anything in OLEObject and also i am unable to copy it in clipboard and write it in disk. Is it possible to extract these objects or not? If yes how can it be done, If no, what can be the work around? Please help me miztaken

Re: fails to create excel sheet diminically by using NamedRange.In

Postby Y2hhbg » Sun, 05 Oct 2008 09:50:00 GMT

ello Cindy:

1-it was some of typo in last post i am sorry for that.
let us to clearify some of the quetions :(i use pinkyonline:)

-- cindy :Did you upgrade a VSTO 2003 solution to work with VSTO 2005 +
Office 2003? And now you're trying to migrate it to Office 2007?

pinkyonline: yes exactely ,
By running a legacy Excel template let application that is using VSTO
sheet object. Which is required to run solutions built using VSTO 2005 this
runtime supports solutions built for Microsoft Office 2007 system. The
creating sheets in run time dynamically are failing to completion properly.

Scenario

GeoDSStemplate projects are creating sheets by using
theicrosoft.Office.Tools.excel dynamically which is VSTO sheet object so
that we can access the named range controls we created in the sheets
programmatically. The project absolutory runs fine in Microsoft Office 2003
system by using VSTO.
After replace VSTO sheet objects with excel Interop sheet objects and
Interop range objects using icrosoft.Office.Interop.excelin office 2007
system it fails the creating sheet to create sheets dynamically at runtime .

Note pleae kindly find the common code in this Blog below setion.

pinkyonline: we are subspecing :

The issue occrued in office 2007 migration now.the creating a sheet in
runtime falis to instance a NamedRange control by using Excel =
Microsoft.Office.Interop.Excel;
This template project is working fine with is task when office 2003 is by
using ExcelVSTO = Microsoft.Office.Tools.Excel;

pinkonline:
when we replace the Microsoft.Office.Tools.Excel to
Microsoft.Office.Interop.Excel to for office 2007 imgration.
it is fail. one of error example :
In method calls between 2 libraries ExcelTemplateLibrary and
GeoDSSTemplateLibrary in runtime , the processing sometimes ( it ) have a
few errors for method overload errors with invalid augments. refer to
getRange, or AddNamed Range method related to NameRange control;
Error 21 The best overloaded method match for
'ExcelTemplateLibrary.clsGlobalTempla{*filter*}gine.AddNamedRange(string,
Microsoft.Office.Interop.Excel.Range, string)' has some invalid arguments
C:\cd2005\ExcelTemplate2007issue\offline report \

pinkyonline: nature of the design in Environment:

it is built in excel office 2003, vsto for office 2003, visaul studio 2005
team suite , project type: template , migrate to office 2007 , with upgrade
to VSTO 2005 se



pinkyonline: i need to know does any limitation or not-support by VSTO 2005
se for office 2007 to using:
is a corrct appoaching write code : to creating a sheet in runtime to
instance a NamedRange control by using Excel =
Microsoft.Office.Interop.Excel;
in office 2007 migration now with C#?


thank you advance:
Pink:)
i will post next 3 post for add the code. it is limited to 30000

Excel.Range rngRange;
Excel.Range rngDivRange;
DataRow[] arr_dRSheet;
DataRow[] arr_dRRange;
string[,] arr_strRRRange;
string strWSName = "";
string strFilter = "";
string strSuperRangeName = "";
string strSoldTo = "";
string strDivRangeName = "";
int iRRColsCount = 0;
int iRRRowsCount = 0;
int iRowIndx = 0;
int iStartIndx = 1;
string strUpdDim = "";
Top

Re: fails to create excel sheet diminically by using NamedRange.In

Postby Y2hhbg » Sun, 05 Oct 2008 10:03:01 GMT

private void SetRangeData(ExcelVSTO.NamedRange nrDivRange, string
strDivRangeName, Excel.Range rngDivRange)
{
DataTable dTData = null;
DataTable dTGroup = null;
DataRow[] arr_dRGroup = null;
DataRow[] arr_dRData = null;
object[,] arr_objDivRangeCalCol = null;
string[,] arr_strDivRangeCols = null;
string strFilter = "";
DataTable dTUColumns;
DataTable dTUserData = null;
System.Array arr_RangeData = null;
System.Array arr_GroupData = null;
int iSubGroupColIndx = 0;
int[] arr_iSubTotalRowIndex = null;
int iGroupIndx = 0;
Excel.Range rngIns = null;
Excel.Range rngData = null;
Excel.Range rngCell = null;
int iDivRowCnt = 0;
int iDivColCnt = 0;

try
{
dTGroup = m_dSTemplateData.Tables[clsGlobal.STR_TBL_GROUP];
if (dTGroup != null)
{
dTUColumns = new DataTable();
dTUColumns.Columns.Add("DataRow");
dTUColumns.Columns.Add("Index");
dTUColumns.Columns.Add("Type");

try
{
rngIns = (Excel.Range)nrDivRange.Cells[2, 1];
}
catch
{
rngIns = (Excel.Range)rngDivRange.Cells[2, 1];
}
arr_objDivRangeCalCol =
GetCalcNamedRanges(strDivRangeName);
//Record Range column details
arr_strDivRangeCols = GetRangeColumns(nrDivRange, ref
dTUColumns, rngDivRange);
if (clsGlobal.HasUserData == true)
{
if (clsGlobal.RangeIndentifierColumns.Trim() == "")
{
iSubGroupColIndx =
GetSubGroupColumn(m_nrCurrent, m_rngCurrent);
dTUserData = GetUserData(nrDivRange, dTUColumns,
iSubGroupColIndx, strDivRangeName, rngDivRange);
}
else
{
dTUserData = GetUserData(nrDivRange, dTUColumns,
strDivRangeName, rngDivRange);
}
}
//Set Data to the range


if (m_dTSuperRangeData != null)
{
dTData = m_dTSuperRangeData;
}
else
{
dTData = m_dSTemplateData.Tables[m_strTableName];
}
arr_dRGroup = dTGroup.Select("", clsGlobal.STR_COL_ORDER);
arr_iSubTotalRowIndex = new int[arr_dRGroup.Length];
foreach (DataRow dRGroup in arr_dRGroup)
{
strFilter = clsGlobal.STR_COL_GROUP + " = '" +
dRGroup[clsGlobal.STR_COL_GROUP].ToString() + "'";
if (dTData.Columns[clsGlobal.STR_COL_ORDER + "New"]
!= null)
{
arr_dRData = dTData.Select(strFilter,
clsGlobal.STR_COL_ORDER + "New");
}
else
{
Top

Similar Threads:

1.fails to create excel sheet diminically by using NamedRange.Inner 

 topic: Excel Object Model Reference (Visual Studio Tools for Office)
NamedRange.InnerObject Property
Gets a Microsoft.Office.Interop.Excel.Range that represents the underlying 
native object for the Microsoft.Office.Tools.Excel.NamedRange. 

Namespace: Microsoft.Office.Tools.Excel
Assembly: Microsoft.Office.Tools.Excel (in microsoft.office.tools.excel.dll)

I have current issue migartion  of  old office 2003 template XLT project to 
office 2007 excle enviroment.  the project working fine in  Office 2003 sp1, 
net 2003, PIA 1.1, vstor for office 2005.
when this project link to using office 2007 ,  enviroment: 
NET 2005, PIA for office 2007, office 200 system, vstor se.
 for user use in office 2007 sys  , we change the dll .   replace the 
underlying native object for the Microsoft.Office.Tools.Excel.NamedRange to 
Microsoft.Office.Interop.Excel. sheet and namedRange controls. that 
issue :

 it fails to create sheet incremental in runtime ( dynimically)after to 
change to call the Microsoft.Office.Interop.Excel. sheet and namedRange 
controls .

please advice of this issue that related to Compatibilty of  office VSTO 
2003-2007 tools. dll.  thanks
pinky, chan


 XXXX@XXXXX.COM

2.Creating Excel sheet by using XPAPIs dll without office installed at server 

Code--


                objExcel = New Excel.Application


Error/Exception-- I have included all the apis
microsoft.office.interop.excel etc in bin
but it is not able to make object of excel class.

Please suggest, is it possible to create an excel sheet using above
COM objects, without MS Office installed.


Thanks
Anil

3.Unable to Create Multiple Excel Sheets Using OWC 

Hello

I am trying to export data from asp.net to excel spreadsheet, using office 
web components. I am able to export the data to a single excel sheet.

PROBLEM:
I am unable to create mutiple sheets in the created excel file(it always 
creates one sheet), also i want to export different data to differnt sheets 
in the exported spreadsheet.

TIA

Mentioned below is my code VB.net:
Dim xlSheet As New OWC.SpreadsheetClass

Dim oWBs As OWC.Workbook

Dim oSheet As OWC.Worksheet

Dim oSheet1 As OWC.Worksheet

cnSql = New 
SqlConnection(ConfigurationSettings.AppSettings("ConnectionString1"))

cmdSql = New SqlCommand("Select * From myTable", cnSql)

adSql = New SqlDataAdapter(cmdSql)

adSql.Fill(dsSql)

numCols = dsSql.Tables(0).Columns.Count

oWBs = xlSheet.Workbooks.Item(1)

oWBs.Sheets.Add()

oSheet1 = oWBs.Sheets.Item(1)

oWBs.Sheets.Add()

oSheet = oWBs.Sheets.Item(2)

oSheet.Name = "TestSheet1"

oSheet1.Name = "TestSheet2"

For i = 0 To numCols - 1

oSheet.Cells(iRow, i + 1) = dsSql.Tables(0).Columns(i).ToString

oSheet1.Cells(iRow, i + 1) = dsSql.Tables(0).Columns(i).ToString

Next

If dsSql.Tables(0).Rows.Count > 0 Then

For j = 0 To dsSql.Tables(0).Rows.Count - 1

For i = 0 To numCols - 1

oSheet.Cells(row, i + 1) = dsSql.Tables(0).Rows(j)(i)

oSheet1.Cells(row, i + 1) = dsSql.Tables(0).Rows(j)(i)

Next

row = row + 1

Next

End If

oSheet.Activate()

oSheet1.Activate()

Dim xlFileName As String = DateTime.Now.Ticks.ToString + ".xls"

xlSheet.Export(Server.MapPath(".") + "\\" + xlFileName, 
OWC.SheetExportActionEnum.ssExportActionNone, 
OWC.SheetExportFormat.ssExportHTML)

Response.ContentType = "application/x-msexcel"

Response.WriteFile(Server.MapPath(".") + "\\" + xlFileName)

RemoveFiles(Server.MapPath("."))

4.creating an excel sheet using code 

Hello all,

I have code that will create an excel spreadsheet from a customizable .xls 
template file that can be edited. I basically use merge fields manually, and 
create a sheet containing many part line-items, using code like the 
following:

mysheet.cells(DetailStart, PriceCol) = rs!PartNumber
mysheet.cells(DetailStart, DescCol) = rs!PartDescription

I'm setting variables for DetailStart, and the columns for various merge 
fields above, such as PriceCol, DescCol, etc.

My main question is: It's easy to insert text into the cells, using the code 
above, but is it possible to insert Images/Pictures if you now the path/file 
name of a given image? Is this even possible?

Thanks!

Andy

5.NamedRange - Variable Sheet 

Is there a way to set a named range, based on the active sheet?

Example:
Range1 =LOOKUP(2,1/('Sheet1'!$B$1:$B$5000<>""),ROW('Sheet1'!$B$1:$B$5000))

I want to be able to set this named ranged, based on the sheet I am in.  If 
on sheet 2 it would set the range as:
Range1 =LOOKUP(2,1/('Sheet2'!$B$1:$B$5000<>""),ROW('Sheet2'!$B$1:$B$5000))

etc...

6. Unhide sheet based on NamedRange?

7. Create Distribution List from Excel Sheet

8. Using a Macro in Excel to send an embedded sheet via Outlook

Post a reply

Return to Microsoft OFFICE

 

Who is online

Users browsing this forum: No registered users and 19 guest

Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group