I am currently using Gembox to pass text to excel and I have a problem. I have imported the required resources but when I try to pass an item in a list box it comes to an error..here's my code
'checkrow is a number loaded from that same excel
Dim ef As ExcelFile = New ExcelFile
ef.LoadXls("C:\Users\DF\Desktop\registro_prestamos.xls")
[code].....
im usinf Gembox to read Excel files.I'm copying the fields to a datatable, so i have to add the columns to the datatable first.
Therefor im using this code:
For i As Integer = 0 To objWorksheet.Columns.Count - 1
objDataTable.Columns.Add(i, GetType(ExcelCell))
Next
But objWorksheet.Columns.Count is 0 even if theres data in 4 columns.
With vb.net I would like to select 4 cells in Excel and then pass those cell values along with 1 button tag to a formula in Excel.I press a button with a month, say "May" as text. I want to grab a cell in Excel labeled "May1t" the value of which is the number of hours until may. This is the code I'm using.
xlsWB.Worksheets(2).Range(Month1 & "1t").Value I am able to set a variable with this as the value but,
I want to pass this cell (which will be updating as the time counts down) and 3 other similarly specified cells along with the button tag to an Excel formula.I have something like this:
Private Sub F_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles F.Click
xlsWB.Worksheets(4).Range("B2").Formula = "=MyFunction()"
xlsWB.Worksheets(1).Range(Month1 & "1t").Select()
End Sub
The formula takes the parameters in the order: MyFunction(Cell,Tag,Cell,Cell,Cell).
How to pass values to a particular column in Excel
View 8 RepliesI have created a COM Object via VB .NET and exposed some functions that I wish to call in Excel using VBA. I am currently trying to pass an array of objects as a result to VBA but Excel is complaining the following:
'Function or interface marked as restricted, or the function uses an automation type not supported in Visual Basic.'
I have looked around on the internet and have seen similar issues but not to one I am trying to do.My VB .NET function, which I wish to call in Excel is declared as follows:
Public Function CallArray(ByVal serviceName As String, _
ByVal effectiveDate As String,
ByVal serviceParams() As System.Object,
ByRef ArrayRes() As System.Object) As System.Boolean
VBA is complaining about the System.Objects my question is how the heck are you supposed to pass and return arrays from .NET to VBA. Also the data that will be in the ArrayRes() is mixed type, its not just strings etc, its an array of arrays that can contain strings, integers doubles etc.I chose VB .NET because I assumed it would be possible to map to VBA easier.if System. Object and Arrays in VB .NET is not the way, ie maybe theres another way via collections or lists, then Im open to that. I just need to get this data into VBA.Also I could move over to C# if needs be.
I have a text box on a Powerpoint Slide. The text box contains text that I need to use in an Excel program. The Excel program will be run from a button on a toolbar within Powerpoint. I need to be able to pass the text box text, from Powerpoint to a variable in Excel, so I can use it in my Excel VBA code.
View 2 RepliesI currently need to build a Automation Add-in for Excel. This add-in will load a recordset from Database to Excel. I would like to have function A() that I input in Cell as a formular. This function A() call a the add-in to load the data from Database to Excel. The data should be a matrix. So that means I would like to set in Excel worksheet a range of data by just calling single function in a cell. (I am sure there should be some way to implement it)As I know if I develop a shared Add-in by VB(or VB.Net) and in the add-in, I can get an Object of Excel.Application. By this Excel.Application object, I can get full control of Excel inside the Add-in. That means if I define a sub that load datamatrix from database and using the Excel.Application object I can populate the data matrix to the Excel.
I have done much research on internet and I now have built a shared Add-in for excel. I should say this add-in works fine without invoke the Excel.Application Object that I have tested. But when I call the Add-in function that invode Excel.Application Object from Excel I got an error as below:
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Run-time error '2147467262'(*)
Unable to cast object of type 'System.String' to type Excel.Application
[code]....
I have a text box on a Powerpoint Slide.The text box contains text that I need to use in an Excel program.The Excel program will berun from a button on a toolbar within Powerpoint.I need to be able to pass the text box text, from Powerpoint to a variable in Excel, so I can use it in my Excel VBA code.
View 1 RepliesI have two tables:
table1
case_no flow result
tc_1 001 pass
tc_1 002 pass
tc_1 003 pass
tc_2 001 pass
tc_2 002 fail
tc_2 003 pass
table2"
case_no result
tc_1 pass
tc_2 fail
Table2 should be updates from table based on the results... If all results is PASS then the table2 should be updated as pass... if any result is FAIL then the entire case_no should be updates a s fail..
This code was working consistently, but now...This code works on the first pass.Then second pass it just hangs on Code:Dim tcpClient As TcpClient = tcpListener.AcceptTcpClient() for about 2 minutesThen the code will fork for another pass, then fail.....
Code:Imports SystemImports System.TextImports System.Collections.GenericImports System.XmlImports System.Xml.LinqImports System.Net.Sockets
[CODE]....
Just started VB programming this week and have found a wealth of information about what I'm trying to do. Problem is, some of it is more complete than others.Here's what I'm trying to do:In Form1 (my main form), I want to instantiate a class that contains a couple of properties (speed setpoint and position setpoint). When I click a button, I want to pass this data to Form2 and populate two textboxes on Form2 with the properties of this object. I want to manipulate the property values on Form2 and click an OK button which closes the dialog and returns the manipulated data, updating the property values of the object. Here's the algorithm I'm following:1) On Form1, instantiate the class2) On Form1's "Pass Data" button click event handler, instantiate a Form2 object and invoke the ShowDialog method, passing the object as a parameter.
3) On Form2, overload the ShowDialog method to accept the object as a parameter and modify the method so that it returns the manipulated class data.4) On Form2, in the ShowDialog method, populate the textboxes with the class data that was passed in.Here's where I get stuck. If I press the OK button on Form2 (DialogResult.OK), it returns me to Form1, but what hook do I have in Form1 to receive the manipulated class data that the ShowDialog method is returning?Here's an example of what I'm thinking about:
Code:
Public Class Form1
Dim clsController1 As New MotionController
[code].....
I'm sure it's probably a very elementary question, but every explanation I've found seems to be incomplete.
I need to access the current instance of MS Excel. To do that, I wrote the following code:
Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Diagnostics
Private _ExcelApp As Excel.Application
[Code].....
The code runs nice, but it has a problem: After the user close the Excel application (click button X - note: no other instance of Excel exists), if you open the Windows's task manager, its possible to see the process Excel.exe.
If I open and close the Excel application, there is no process Excel.exe, but if I run my code with the Excel application openned, after the user close the Excel application, the process Excel.exe isn't finished.
I am looking for some input. I am helping with a web application that is fairly large. We have some logic factored out into classes, not a true business logic layer (yet ). The classes are setup to pull values directly from the QueryString or Session variables and I'm not sure that that mehtod is proper or even a
The reason I would like some input is, there may be a chance to rewrite or at least refactor the code.
[Code]...
can I use Excel APi (Interop.Excel) without installing MS Excel.
View 2 RepliesI want to encapsulate the use of Excel Interop to make it easier to use somehow in a reuseable library.So far, I have written some tests that work good altogether, except that between each test, I force Excel to quite so that it does have handle on the test file no more in order to be able to delete.Once I quit the application instance between after each of my tests, Excel seems to be no longer responding, causing Windows to display "Excel has stopped working and is looking for a solution"
message. This message lasts a few seconds, meanwhile Excel is closing, causing the hang on the file to occur and my tests to throw an exception like "Cannot access file because it is being used by another process..."
[Code]...
I am using VS .net 2003 and developing a program that calls Excel. The development computer only has Excel 2003, and the target machine only has Excel 2007. Would it be possible to install Excel 12 Object Library in the development computer without installing Excel 2007?
View 4 RepliesI'm trying to upgrade a VB6 app to VB 2008. I have read the article in the tutors corner about automating Excel from VB but I cannot get it to work. Heres what I have done:
1) set a reference to the Microsoft Excel 12.0 Object Library
2) Added "Imports Excel = Microsoft.Office.Interop.Excel" to the top of the module
3) in a routine I have added: Dim X As New Excel.Application Here's where I get an error: Error 75 'Application' is ambiguous in the namespace 'Microsoft.Office.Interop.Excel'.
I am trying to retrieve the data from EXCEL 2003 spreadsheet from row 8 onwards because from Row 1 to Row 7 the row is merged columns from A1 to K1, A2 to K2, A3 to K3, A4 to K4, A5 to K5, A6 to K6, A7 to K7.
View 28 RepliesI am exporting data from vb.net to excel and it is dropping the leading zero when its displayed on excel. How can I avoid the dropping of leading zero? I read the solution of adding a single quote but it makes my excel sheet column ugly. Also users will complain if they see a single quote on zip code field.vb.net code
Response.AddHeader("content-disposition", attachment)
Response.ContentType = "application/vnd.ms-excel"
Response.Charset = ""
the stored procedure outputs XML and it is transformed by XSLT before it is displayed on EXCEL
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
<HTML>
[code]....
adding a single quote or manipulating excel sheet with column formatter (general/numbers ) etc. I don't want user to make any changes on excel to get the display properly when the excel is displayed from web page it should be all set with proper formatting. also we have no control over client excel software?
i Have this code which populates a number of combo boxes from an excel document, the problem is that it only populates the 1st three lines, does anyone know why this is??
[Code]...
I'm trying to export a datagridview to Excel and open the Excel spreadsheet (not SAVE the worksheet).
Public Sub ExcelRpt(ByVal DgvName As GridView, ByVal url As String)
Dim xlApp As Microsoft.Office.Interop.Excel.Application
Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
[code]....
I am writing a program that loops through all of the sheets in an Excel workbook and saves each sheet as its own workbook. It turned out to be a bit trickier than I expected, since the Sheet.Copy method creates a strange object (see here for the MSDN discussion that I believe is relevant: [URL]. Anyway, I found a Stack Overflow post [URL] that got me to where I am, which is essentially complete, outside of one hanging EXCEL.EXE process that is left after the program completes, which I believe is a result of the sheet.copy method creating a new workbook (potentially a new application as well?). The other thing I run into, which I believe is related to this, is that even though I have my application set to visibility off, when I run the program, excel still opens up and is visible going through the steps.
Here is my code:
Code:
Imports System.Data
Imports System.IO
Imports Microsoft.Office.Interop
[CODE]...
Now I think the problem comes from the end of the loop, where I try to close the export file and the new worksheet it creates:
Code:
'close excel and release com objects
System.Runtime.InteropServices.Marshal.ReleaseComObject(exportsheet)
exportsheet = Nothing
xlApp1.Workbooks(fileNames(counter - 1)).Close(False)
I can't figure out what to do to release the `ComObject` for the new worksheet that is created. I have been trying all sorts of things, but it always throws a COM error when I do it and if I try to define it as nothing (like I do with exportsheet) is says that it is read only by default, so I can't do it. It seems like it should be something as simple as: System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp1.Workboo ks(fileNames(counter - 1))) But that is not the case. I have tried a few variants of this and I am thinking it has to do with the MSDN link above, but I can't quite sort out what to do. So the code works for my purposes, outside of leaving one EXCEL.EXE after it is done. As far as a test file, I am just using an Excel file with 3 sheets and I put some information on each and change the sheet name, so it is easy to see if it is working or not.
I have been doing a lot of reading on this topic and most folks seem to agree that having Excel is required to use the COM Interop libraries. However they are never specific as to where that should be installed. Does it need to be installed on the machine I am developing on or does it need to be on every machine that I deploy to?
Edit: I should mention that this is desktop development/deployment targeted for all Windows machines.
I have an asp:table which I want to exported to excel. One of my fields are alpha numeric and when exported to excel the leading 0s are stripped off. After going through this thread: Validation (CSS 2.0): 'mso-number-format' is not a known CSS property name I would like to use the css method "mso-number-format:@;.But the css is not exported to excel. I just tried to test it with simpler css things like bold font etc but its not getting carried over. I can see that if I surround my asp:Label with tags this change gets carried over to the excel but not the css bold . Other solutions in other thread does not work for me as ="00111" shows up as desired in excel but in the web form it shows up as ="00111" which is not what i want.
View 2 RepliesI use the following code snippet
Dim exlapp As New Microsoft.Office.Interop.Excel.Application
Dim xlWorkBook_new As Microsoft.Office.Interop.Excel.Workbook
Dim strOriginal As String
strOriginal = Server.MapPath(Request.ApplicationPath & "/Attachments/") & "abc.xls"
xlWorkBook_new = exlapp.Workbooks.Open(strOriginal)
The code hangs in the last line. It works fine in the development environment. i deployed in windows server 2000 ,where I get this issue. the browser goes half way and throws the below exception after some time
[Code]...
I have set the identity impersonate to true in web config. This should be mostly an access rights issue.I have even provided access rights for the "everyone " user in the excel file and as well for the interop component. Please post ur valuable comments.
I have to provide an Excel file as an output. I have to color some of lines and make them bold.The input is based on a dynamic list (in memory) and it also could be based on CSV file that I'm already provide today (based on the same dynamic list).I'm wondering what would be the most efficient way (in terms of performance) in order to provide the colored Excel output?Should I export directly from dynamic list into Excel - or - Should I export from CSV into Excel ?
View 7 RepliesI have a VB.Net App that on ButtonRun_Click open Excel to get the "tab" and "column" then another buton that past content from textboxes. I get a "COMException was Unhandled" If I reopen excel it's in use.
Private Sub ButtonRun_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonRun.Click
Dim MyExcel As New Excel.Application
[Code]....
I am trying to create double and number format cells in excel using NPOI library. I used code like Dim cell As HSSFCell = row.CreateCell(j) cell.SetCellValue(Double.Parse(dr(col).ToString)) In excel numbers are aligning right but when I check format it is showing in "General" then I changed my code to like below
[Code]...
I have read some of the other posts on this and seem to be a little different than my situation. Trying to print out some reports into an excel document on the clients machine - of course it works fine on my local (heard that before right). I went in and set my references Ms.Office.Core and Ms.Office.Interop.Excel to 'Copy Local' = True so the .dll are in the servers bin folder.
[Code]...
I have a project that has a need to display a Excel file in second window, but because other controls are needed, simply launching excel is not sufficient. Right now I do not know if the target machines have excel installed but I know with certainty that if they do it would be 2010, is there any method or library that allows you frame your own form around an excel window?
View 1 Replies