Re: Excel automation problem - Visual Basic Discussion Boards

ah if only there was a course that told me to use
process instead of application or program - sigh!!

Martin

life is a bowl of cherries
go on take a byte

I did & it loaded fine. My problem is, none of the controls to change [select voice],[pitch],[speed],&[volume] had any affect on the audio output. What am I doing wrong?

Robert, still trying

Your best source of information is going to be the manufacturer of the component.

It's very unlikely anyone who frequents CP and answers questions is going to have any knowledge of such a narrow-market product. Though it looks like it wraps MS Speech API 4.0[^].

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007

Greetings all.

I've made a service which creates an Excel workbook automatically using the Excel COM object.

I have however got a problem that when I try to enter a formula containing the sheet names to sum across all sheets that the service crashes with a "Exception from HRESULT: 0x800A03EC" error and I'm at my wits ends right now.

I know the formula is correct, because I can copy it from the code and past it into the workbook afterwards and it runs fine, but when trying to do so automatically - it fails.

I create a cell range for the cell I want to write this formula in, and use the
"Formula" property to set the value, and here it crashes.
I've made other formulas without issues, and if I do not contain sheetnames, I can write the formula easily enough (I substituted my SUM for a SUM(1+1)) in the relevant cellrange.

So the issue is solely with a formula containing sheet names for me.
Has anybody else experienced something similar, and possible have a solution or a heads up where to go for one?

---------------------------
127.0.0.1 - Sweet 127.0.0.1

It looks like that's a nice "generic" error returned by Excel when there is a problem with just about anything using the automation model.

What does the code look like that's setting this formula?

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007

The code is something along this line (simplified to avoid writing unnecessary function call et al)

myExcelObject.CreateRange("myCellRange")
myExcelObject.CellRange.Formula = "=SUM('SheetName'!Cell;'SheetName'!Cell)"

I can post the more exact code if needs be, but this is the gist of it.
As mentioned, I write other formulas without problems, although these do not span multiple sheets - including array formulas - so there can't really be much at fault with my base code. The problem seems to lie somewhat deeper then just the normal issues, which is why I turned to this forum.

I was thinking the issue might be connected to the fact that I need to "cheat" Excel to think it is a US-EN system by changing the culture when I create the excel objects needed (Workbook, Sheets etc), and when I then try to write the SheetName's in the formula that it has an localization issue or something.
But if this is the case, then I'm really without much of a hope - it just really annoys me cause I can take the fomula from the code, paste it into the generated Excel document and have it work. I just can't print it out automatically Frown | :(

---------------------------
127.0.0.1 - Sweet 127.0.0.1

A bit more exact would help, I mean a copy'n'paste of the real thing.

The error that's returned is usually caused by an automation failure. Probably due to the code doing something it's not allowed to do. What your doing by putting the formula in a range of cells is not what your code is doing.

I don't do Office Interop projects, so my experience is very limited.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007

With mObjExcel<br />
 .CreateRange(.GetExcelColumnName(EnuColumn.UnitPrice + mIntColumnCountFactor) & mIntColRow(intCounter) & ":" & .GetExcelColumnName(EnuColumn.UnitPrice + mIntColumnCountFactor) & mIntColRow(intCounter), mStrSheetNames(intCounter))<br />
 .CellRange.NumberFormat = "#.##0"<br />
 .CellRange.Formula = "=SUM(" & strFormular & ")"<br />
 .CellRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, , mObjBlack.ToInteger)<br />
End With

The "GetExcelColumnName" is a function which just returns the Excel Column name such as A1, B2 based on a column and row number. Nothing special - just one I made to help myself.

The strFormula variable is the formula mentioned which looks like 'Sheetname!Cell;Sheetname!Cell' and is build dynamically based on which actual sheetnames and cells are used.
The formula works when I copy it into the generated excel document so there is not a problem with the formula as such. Just the generation of it automatically

Inside my mObjExcel the CreateRange looks like

  <br />
Friend Sub CreateRange(ByVal range As Object, ByVal strSheetName As String)<br />
  SetCultureFixStart() ''Changes the current Culture to en-US, casue otherwise excel automation crashes<br />
  If Not mObjCells Is Nothing Then ReleaseComObject(mObjCells)''cleans up COM objects which might linger in memory<br />
  mObjCells = Nothing ''cleans up COM objects which might linger in memory<br />
  System.GC.Collect() ''cleans up COM objects which might linger in memory<br />
  mObjCells = mObjSheets(strSheetName).Range(range).Cells<br />
  SetCultureFixEnd() ''sets culture back<br />
End Sub

And CellRange is just a property which returns my mObjCells (which is an Excel.Range).

What strikes me as most annoying is that the above code works when I use 1+1 in the formula instead of Sheetnames!cell, or when I post out more complex formulas, even .FormulaArray work.
So basically so far, I've been able to do all I wanted to, except use Sheetnames in a formula :/

Thanks so far for the feedback/time.

---------------------------
127.0.0.1 - Sweet 127.0.0.1

I've made a much more simple example which also fails:

mObjExcel = New Excel.Application
       mObjWorkBooks = mObjExcel.Workbooks
       mObjWorkBook = mObjWorkBooks.Add
       mObjSheets = mObjWorkBook.Worksheets
       If mObjSheets.Count > 0 Then
           For i As Integer = mObjSheets.Count To 2 Step -1
               mObjSheet = mObjSheets.Item(i)
               mObjSheet.Delete()
           Next
           mObjSheet = mObjSheets.Item(1)
       Else
           mObjSheet = mObjSheets.Add
       End If
       mObjSheet.Name = "A"
       mObjSheet.Range("A1").Value = "1"
       mObjSheet = mObjSheets.Add()
       mObjSheet.Name = "B"
       mObjSheet.Range("A1").Value = "1"
       mObjSheet.Range("A2").Formula = "=SUM(A1;A!A1)"

This fails, but if I substitute the formula for =SUM(1+1) it writes correct.
The formula is taken directly from what Excel wrote when I made the SUM in there.

I found a workaround which only confirms my suspecision that it must be something culture specific issue.
I could instead of using a SUM formula add the cells together - and I was able to do this with the sheetnames in the addition, so instead of "SUM" I use "=SheetName!Cell+SheetName!Cell ...." and that looks to work when printed out as a .Value.

Wheee. Smile | :)

Thanks for the help otherwise

Hmmm...very weird. I wouldn't have thought that the culture would affect the sheetname.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007

I have no Excel automation experience, but maybe this idea could help:
you could enter the formula in Excel manually, then use automation to see how it looks...

Smile | :)

Luc Pattyn [Forum Guidelines] [My Articles]

this weeks tips:
- make Visual display line numbers: Tools/Options/TextEditor/...
- show exceptions with ToString() to see all information
- before you ask a question here, search CodeProject, then Google

The formula works, so that one is not a problem. I just can't generate the document automatically with this formula - but I can copy it into the document after generation where it would work.

---------------------------
127.0.0.1 - Sweet 127.0.0.1

I have a datagrid when i click on it at the starting of a row the whole row gets selected and the

datagrid.Columns(i).Text

will give each column's text individually.
Even when i click on a perticular column of any row the text in that perticular column gets selected. can i get that perticular text at runtime.
i mean i want to get the text of the perticular cell(x,y) on which i click.

KIRAN PINJARLA

This code doesn't make sense because the DataGrid class (Windows Forms) doesn't have a Columns collection.

The ASP.NET DataGrid does have a Columns collection.

So which is it? The Windows Forms DataGrid or the ASP.NET (Web Forms) DataGrid in the .NET Framework 1.x (Visual Studio 2002 and 2003)?? Or are you really using the DataGridView class in the .NET 2.0 (Visual Studio 2005)?

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007

Hi
I want to open a registry key for changing the owner , user has admin right

Dim User As String = Environment.UserDomainName & "\" & Environment.UserName

Dim K As Microsoft.Win32.RegistryKey = Microsoft.Win32.Registry.LocalMachine.OpenSubKey("SOFTWARE\Microsoft\Windows\CurrentVersion\Component Based Servicing", RegistryKeyPermissionCheck.Default, Security.AccessControl.RegistryRights.TakeOwnership)

Dim RS As Security.AccessControl.RegistrySecurity = K.GetAccessControl

Dim NTA As New Security.Principal.NTAccount(User)

RS.SetOwner(NTA)

K.SetAccessControl(RS)

But .NET throws an exception : Requested registry access is not allowed

I can open Regedit.exe and take registry key's owner ship , why this code doesn't work ?

You haven't opened the key with the appropriate access and permissions. You've opened it for Read, but not Write.

Try changing the OpenSubKey line to:

...LocalMachine.OpenSubKey("...", RegistryKeyPermissionCheck.ReadWriteSubTree, Security.AccessControl.RegistryRights.FullControl)

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007

hi all

i am working on an website automation project.

on a web page their are some number of buttons
(value = "Modify" and type = "Submit")

each button opens another page but the url is as same as the current page.
i found that some hidden information also been sent. but the web page open when clicked on modify button the contents are different ( the page is used to modify the information in database )

their is a javascript function that invoke the submit method of the button being pressed.

How can i invoke that javascript functions in vb .net?
I'm using windows forms, webbrowser control (AxInterop.SHDocVw.dll)
i m trying to automate the work once done by the user ( such as downloading or uploading the files ( the addresses are specified in a file by the user), navigate to the pages as by the user in the same manner )

i have maintain a list of webpages visited by the user by picking up the myhtmldocument.url

but i have no idea how to send and get the information sent as post method.

any one have any idea about what should i read or anything else

thanks in advance

help everyone

read the bytes from the file.
you will find on google code.
good look

Hi all

Can any one tell me in my application i need to read a file from Pdf(file)
and write it into a text file. Can any one share the code pl. Pl mention the dll that we need for reference, I already added

IKVM.GNU.Classpath.dll
IKVM.Runtime.dll
PDFBox-0.7.2.dll
Regards

Prakash Mishra(Banglore,India)

Hi guys,

I'm having some problem with this. I'm writing a small tool that is supposed to parse out an HTML document and return my own implementation of an HtmlDocument class. I'm having a problem dealing with the way VS2005 formats an asp.net page by default (markup).

<body><br />
    <div style="height: auto; width: auto; overflow: auto;"><br />
        <table border="1" width="100%" style="height: 290%;"><br />
            <tbody><br />
                <tr><br />
                    <td style="width: 1%"><br />
                    </td><br />
                    <td><br />
                        <div style="width: 100%;"><br />
                            <iframe src="http://localhost/subtext/" style="height: 507px; width: 100%; overflow: hidden;<br />
                                vertical-align: top;" /><br />
                        </div><br />
                    </td><br />
                    <td style="width: 1%"><br />
                    </td><br />
                </tr><br />
            </tbody><br />
        </table><br />
    </div><br />
</body>

see the iframe tag? the Vertical-align attribute is shunted to a different line.

How would I rejoin it with its tag, in this case

TIA

<div class="ForumSig">There are 10 kinds of people in this world. Those who understand binary and those who don't...
______________________

"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning." - Rick Cook
</div>

There isn't sufficient information to answer your question, let alone understand what's your code is supposed to be doing.

Since CRLF's don't mean anything in HTML, I don't see this causing a problem with a browser.

Does your code parse this up and make a change to a line?? What does the code look like that splits and parses this?? What does the code look like that modifies this stuff and puts it back together??

Are you sure you're not looking at this in an editor, like Notepad, and have Word Wrap turned on??

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007

Dear all

I would like to have your best favor due to my little bit matter, I have a report which is generated under crystal report, I want to print all report directly without see the report preview first through Crystal report program.
do you know any visual basic .net command that will instruct the program to print any report directly no need to see the crystal report print preview ?

Thank you

ICE

try this...

<br />
If PrintDialog.ShowDialog = Windows.Forms.DialogResult.OK Then<br />
   reportDocumentObject.PrintOptions.PrinterName = PrintDialog.PrinterSettings.PrinterName<br />
   reportDocumentObject.PrintToPrinter(PrintDialog.PrinterSettings.Copies, False, 1, 9999)<br />
End If<br />

Steve Jowett
-------------------------
Sometimes a man who deserves to be looked down upon because he is a fool, is only despised only because he is an 'I.T. Consultant'

I'm trying to insert the new date when a selected Product is being updated.
But I can't get it to insert the date.
Here is my code:

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
BackColor="White" BorderColor="#999999" BorderStyle="Solid" BorderWidth="1px"
CellPadding="3" DataKeyNames="PartNumber" DataSourceID="SqlDataSource1" ForeColor="Black"
GridLines="Vertical" Width="700px"
AllowPaging="true" PageSize="18" AllowSorting="True" PagerSettings-Mode="NumericFirstLast">
<FooterStyle BackColor="#CCCCCC" />
<Columns>
<asp:BoundField DataField="PartNumber" HeaderText="Part Number" ReadOnly="True" SortExpression="partnumber" />
<asp:BoundField DataField="PartName" HeaderText="Part Name" SortExpression="partname" />
<asp:BoundField DataField="Picture" HeaderText="Image" SortExpression="image" />
<asp:TemplateField HeaderText="Original Price" SortExpression="Orgprice">
<EditItemTemplate>
<asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("Orgprice") %>'></asp:TextBox>
</EditItemTemplate>

<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Bind("Orgprice", "{0:C}") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Price" SortExpression="price">
<EditItemTemplate>
<asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("price") %>'></asp:TextBox>
</EditItemTemplate>

<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Bind("price", "{0:C}") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>

<asp:BoundField DataField="Description" HeaderText="Description" SortExpression="description" />
<asp:TemplateField HeaderText="Date" SortExpression="date">
<EditItemTemplate>
<asp:Label ID="DateTxt" runat="server" Text='<%# Eval("Date", Date.Now) %>' />
</EditItemTemplate>

<ItemTemplate>
<asp:Label ID="DateLabel" runat="server" Text='<%# Eval("Date") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
</Columns>
<SelectedRowStyle BackColor="#000099" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
<HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="#CCCCCC" />
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:AeroModelConnectionString %>"
DeleteCommand="DELETE FROM [CloseOut] WHERE [PartNumber] = @PartNumber" InsertCommand="INSERT INTO [CloseOut] ([PartNumber], [PartName], [OrgPrice], [Price], [Description], [Picture], [Date]) VALUES (@PartNumber, @PartName, @OrgPrice, @Price, @Description, @Picture, @Date)"
SelectCommand="SELECT * FROM [CloseOut] ORDER BY [PartNumber]" UpdateCommand="UPDATE [CloseOut] SET [PartName] = @PartName, [OrgPrice] = @OrgPrice, [Price] = @Price, [Picture] = @Picture, [Description] = @Description, [Date] = @Date WHERE [PartNumber] = @PartNumber">

<DeleteParameters>
<asp:Parameter Name="PartNumber" Type="String" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="PartNumber" Type="String" />
<asp:Parameter Name="PartName" Type="String" />
<asp:Parameter Name="OrgPrice" Type="Decimal" />
<asp:Parameter Name="Price" Type="Decimal" />
<asp:Parameter Name="Picture" Type="String" />
<asp:Parameter Name="Description" Type="String" />
<asp:Parameter Name="Date" Type="dateTime" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Name="PartNumber" Type="String" />
<asp:Parameter Name="PartName" Type="String" />
<asp:Parameter Name="OrgPrice" Type="Decimal" />
<asp:Parameter Name="Price" Type="Decimal" />
<asp:Parameter Name="Picture" Type="String" />
<asp:Parameter Name="Description" Type="String" />
<asp:Parameter Name="Date" Type="dateTime" />
</InsertParameters>
</asp:SqlDataSource>

I appreciate any help and suggestion.
Thanks