Office Automation :: Excel Automation - Cannot Use Office PIAs
Feb 14, 2009
It appears that when one creates an instance of Excel, using CreateObject, (Late binding), the suggested Excel closing function (with GC.collect etc) does not work. I have tried several suggestions, and the only one that works is process.kill method. Do you see any problem in using this? BTW: the reason we are using late binding is that we cannot use Office PIAs....not sure which version of Office would be installed on the client site. We work with O2003, but the client might still be at 2000 or 2002.
View 7 Replies
ADVERTISEMENT
Jan 25, 2011
I have written an automation addin in Excel with C# (addin.dll). To use the addin, I need to 1. register it using "regasm /codebase c:...addin.dll"2. select in ExcelOptionsAddInsExcel AddinsAutomation the respective addin from the list
I would like to automatize 1) and 2), so that the user just needs to run an exe-file in order to use the addin. 1) should be no problem, but how can one automatize 2)?
View 12 Replies
Apr 20, 2010
Ok basically what I need to do is the following:Read FirstExcelDoc (attached)For every row in that: Retrieve data from sql server db based on info in the Orange columns of FirstExcelDoc Let's say I retrieved data elements A, B, C, D, and E from sql server. Then I need to enter those same data elements A, B, and C in another excel spreadsheet (which has formulas etc) After inputing A, B, and C in that spreadsheet, read a couple of calculated fields and compare those results to data elements D and E retrieved originally from SQL Server DB. Finally, output the results in the Blue columns of the FirstExcelDoc spreadsheet for that row.Loop through and do the same thing for row 2 of the FirstExcelDoc and keep on until you see "END TEST" in the FirstExcelDoc.
I'm looking for a method of doing this efficiently. I have read several articles over the past few days as well as those from Mike R. but just need some direction to get started on this.To start of I'm looking for whether to read the entire FirstExcelDoc spreadsheet or just read the info for first policy number(2nd row) and process it and write out the blue column info for that row and then loop through and come back and read the second policy number (3rd row) etc... Please attach a code snippet for either way of reading the data.
View 19 Replies
Jun 17, 2009
I'm using excel via interop in application.Sometimes users kill application that created excel instance and that results in excel instance sitting in memory without any chance of disposing it.First I tried to tackle this using ROT(running object table) looks like not all instances of excel are registered there.Than I tried to somehow mark excel process after it is started via interop.[code]This works as long as excel is visible but in my case excel window is invisible.All I need is to somehow mark process that started via automation with the parent PID, so that later I can kill it if parent does not exist.
View 19 Replies
Dec 17, 2010
I have been pulling my hair out trying to fix this.
[Code]....
View 1 Replies
Jun 1, 2010
We have a vb program which is run on our server by scheduled task every day. The program opens about 30 excel files, refreshes the data connections, pivot tables and then saves the document in another location.
This may run fine for a couple days but sooner or later the process starts crashing, usually with this error: RPC_E_SERVERFAULT.
My question is, Is it expected that continually opening/closing/refreshing workbooks will inevitably cause the Excel object to crash at some point.
View 3 Replies
Mar 28, 2011
i was wondering if someone could give me the final explanation on what i might be doing wrong here.I spent a lot of time reading around the net and the forum about how to add Threading capabilities to a VB project.
Nos, i've got .NET 4 installed, and i've added the system.tsr and mscorlib.dll files as reference in, say, Excel 2007 (see screenshot).However, when i try to make a thread with anything as basic as "Dim Thread1 As System.Threading.Thread", i get a user-defined type not found error.Indeed, when i use the object browser to see what's in the System file, theres nothing remotely linked to any Threading, and in the mscorlib file, nothing under "Threading" either, just a few Thread related functions, but nothing defining a data type
View 2 Replies
Sep 25, 2009
I'm having an issue where my excel.exe process doesn't close until I close my .net application. I'd like to avoid using Process.Kill() if at all possible.
Code:
Private Sub WriteRowsToXls(ByVal rows As DataRowCollection, ByVal xlsPath As String, ByVal showStatus As Boolean, Optional ByVal AddTotals As Boolean = True)
Dim xlsApp As New Microsoft.Office.Interop.Excel.Application
[CODE]............
This method references a couple of other methods I use for repeated formatting tasks, I'll include those methods below. (I added the FinalReleaseComObject into the other methods after reading Mike's thread but the issue persists)
Code:
Private Sub SetRangeValidation(ByVal rng As Range)
With rng.Validation
.Delete()
[CODE]..............
Method SetRangeAllBorders does about the same as these other methods but my post is too long to add it. The only other things not shown in these code snippets are a few string variable that are defined in the class, studentWS, studentsWSPW, paymentsWS and paymentsWSPW My App creates an instance of the class, loads some data, runs the method above, and then sets the class to nothing. I'm using .NET 3.5 automating Excel 10
View 2 Replies
May 28, 2009
I am developing an application using excel automation.The worst thing is the client's side doesn't have excel installed.What is the solution for this case?
View 17 Replies
Apr 16, 2010
I have successfully automated a PowerPoint Metrics presentation with VB.Net. However, since there were many complicated calculations, it took quite some time to make the 120+ slide presentation - so I recently tried to revamp the application to be multi-threaded.
[Code]...
View 1 Replies
Jan 21, 2012
I've got VS2008 and Office2010, but I don't have Office2007. Therefore, I have the Word14 Object Library but not the Word12 Object Library. Will the code that I create for Office2010 automation also work where clients only have Office2007 or will they have to upgrade their office suites?
View 1 Replies
Aug 7, 2009
when the worksheet is built i get the error message "Object reference not set to an instance of an object.".Here is my code:
Code:
Dim myXLApp As Excel.Application
Dim myXLWbs As Excel.Workbook
Dim myXLSht As Excel.Worksheet
[code]....
View 1 Replies
Jun 19, 2012
I am creating a new software with visual studio 2010 using the program language visual basic .NET, well in that software I create a new file in excel 2007, my question is because I do not know how I can create a combobox with data of my database inside, [code]
View 2 Replies
Jun 9, 2010
I work for a financial firm doing vb.net work.I open an excel doc and populate it using calls to range...i however dont dim the range...I call xlsSheet.Range.ValueI have about 30 ranges i update in this method before printingdo i need to set the .Range.Value to Nothing in order to close the excel.exe
View 9 Replies
Dec 1, 2009
When connecting, I am getting the error 'Provider is not specified and there is no designated default provider.'[code]
View 4 Replies
Dec 7, 2010
I would like to keep part of an Excel workbook open to the user (including some VBA code) while protecting other code. What I hope to accomplish is to rewrite some of my code from Excel VBA in VB.Net and compile it into a DLL which I can then call from Excel VBA. I want end users to be able to modify most of the code in the module, but some of the process should never be touched. Say I have an array in VBA
ArrONE(1 to n, 1 to n)
I would like to pass this through into a DLL and return a new array back to VBA:
ArrTWO(1 to n, 1 to n)
1) What are the best resources for VB.Net?
2) What are some main differences between VBA and VB.Net code
3) I have read that VB.Net doesn�t handle Variant data types, which (as far as I know) is a requirement to perform matrix multiplication in Excel VBA. Any detail on this issue would be of use.
4) VB.Net test environment? I often use msgboxes or simple subs printing to a spreadsheet to test my code in Excel VBA as I go to make sure everything I code is working properly before advancing. What would be .NET equivalents.
5) How to compile VB.Net into a DLL
6) How to call a DLL from Excel VBA
7) What effect will this have on the speed of my procedure?
View 2 Replies
Sep 10, 2009
I am trying to write data to an excel spreadsheet. Simply just todays date and the value of textbox1. I can successfully write it to excel, but how do I get it so each time the application runs it writes the data to the next available row?? I know there must be some loop involved but am having trouble with the coding..
this is what I have so far.
Code:
Imports Microsoft.Office.Interop
Imports System.IO
Imports Excel
[Code].....
View 2 Replies
Mar 10, 2010
The following code is working with two exceptions: The ** are lines were a message box will appear saying that "book2.xls is already present. Do you want to save as? " I am not sure how to fix this. I would be interested to know if I am missing something or if there is a better way.[code]
View 7 Replies
Feb 15, 2011
I've developed shared addin for Microsoft Word. I distribute this addin with my application and using Inno setup for installation. The Inno setup installs my application and then runs "myAddin.msi" file in order to install addin.
how to uninstall this addin programatically?
Right now, in order to uninstall addin, the user has to to it manually through control panel.
View 2 Replies
Apr 22, 2010
I want to define a range in Excel, but I do NOT want to use "A" or "D" references for columns. I am making a table, and to set it's range I use:
Code:
.Range("A1:D4")
However, at the time i write the code I do not know how many columns will be involved, so I don't know if "D" is the end column. For rows this is fine, as i could write this:
Code:
.Range("A" & i & ":D" & j)
But how do I do this for columns? I really would rather not have to convert my integer number for the end column to text. If I know that the table should go from row 1 to 5 and column 1 to 10, how to I select this range?
View 3 Replies
Jan 22, 2009
I have created a C# .Net DLL add-in for Excel.When I try it on my home machine with WinXP and OfficeXP 2003 it works as expected. When I try it on a couple of machines at work with WinXP and OfficeXP2002 it does not work.I have spent 2 days solid reading up on this subject in general and I still cannot get to the bottom of the problem. As far as I can work out what I am trying to do should work with Excel 2002.
Some details:
- I am using Visual Studio EXPRESS 2008 (C#).
- The assembly is signed with a Strong Name Key File.
- I have installed the OfficeXP (2002) PIAs.
- I have created a class DLL in accordance with many MSDN and other internet posts.
- I have registered the DLL using RegAsm.exe and GACUtil.exe.
- I have added registry entries for Excel for CURRENT_USER so Excel should know about the DLL.
The DLL adds some Automation functions (Tools-Addins-Automation) which can be accessed in cells within Excel (for example you would enter =MultiplyFirstBySecond(2, 3) in a cell and get a result of 6) and also implements the IDTExtensibility2 methods so that when Excel is opened it creates a button on the toolbar which when pressed pops up a message box. It it this functionality (adding buttons with code behind them) that I want to expand on.I have attached the zips of the batch and registry files I have created to register the DLL in the GAC and in the registry. I have also attached a zip of the complete project. I would like to know if there is anything I should check with my machine's setup or my project or my registration/installation of DLLs, PIAs etc in order to make my DLL work on my Excel 2002 machine.
View 9 Replies
Aug 17, 2009
my program lets people pick their color to highlight different shifts.after data manipulation they can export the shifts to Excel.I want Excel to show up the same colors as the users picked so I know I need to add them to the workbook palette.the first color I import works fine but after that, all the colors get jumbled and messed up and put in random spots in the palette
WorkBook.Colors(paletteIndex) = RGB(dColor.R, dColor.G, dColor.B)
leads to
Excel.Cells(i, j).Interior.ColorIndex = paletteIndex
something must be wrong in these two lines. I get no errors, just the wrong set of colors.
View 7 Replies
Apr 25, 2012
We have created a dll to convert an excel spreadsheet to a csv file. At the moment we open the xls file, an eror occured (file doesn't exist / is used by an other user)
Used code:
Dim excel As New Microsoft.Office.Interop.Excel.Application
Dim wb = excel.Workbooks.Open("d:my.xls") '<<<<<<<<<<< not possible to open
wb.SaveAs("d:my.csv", Microsoft.Office.Interop.Excel.XlFileFormat.xlTextMSDOS)
wb.Close(SaveChanges:=False)
View 1 Replies
Jun 21, 2011
I've an excel sheet which is populated with thousands of rows of data, is there a way in vb.net I can pick out only selected cells/rows without having to go through every row?
View 2 Replies
Oct 19, 2009
How do I import data in Excel from a CSV file using C#? Actually, what I want to achieve is similar to what we do in Excel, you go to the Data tab and then select From Text option and then use the Text to columns option and select CSV and it does the magic, and all that stuff. I want to automate it.
[Code]...
View 2 Replies
Sep 2, 2010
Excel process will not terminate...
Code:
oExcel = CreateObject("Excel.Application")
oBooks = oExcel.Workbooks
oBook = oBooks.Open(Filename:="C:Jared FC Project Desktopfixture tracking.xls", UpdateLinks:=False, ReadOnly:=False)
oSheet = oBook.Worksheets(1)
[Code] .....
View 6 Replies
Nov 13, 2009
I'm trying to create an excel file and load headers into the first row and then save the file to the local computer for later use. All of that works fine, but for some reason the "EXCEL" process won't quit. I have read through the tutorial written by Mike R and I have even tried just copying and pasting the code from the tutorial into my project, but to no avail. I should also note that I have not yet tried the "Extreme Measures" listed in the post, though at this point I am tempted to.
Code:
Dim ExcelApp As Excel.Application
Dim ExcelWorkBook As Excel.Workbook
[code]....
View 5 Replies
Jan 10, 2011
I have formulas in certain cells and happens to be part of the Excel range that I pull into a vb.net array and when I write the array back out to the Excel Range, I loose all the formulas in it.Is there an option to retain formulas in a .net array?
View 3 Replies
Apr 28, 2009
I am using VB.net.I am importing a text file into Excel. Then I want to select everything and sort it based on a cell (A) in this case. So here is what I have so far.
Code:
xlApp.Workbooks.OpenText(sDump, , 1, , XlTextQualifier.xlTextQualifierDoubleQuote, , , , True)
xlSheetData = xlApp.ActiveSheet
rng = xlSheetData.UsedRange
rng.Sort(rng.Range("A2"), XlSortOrder.xlAscending)
It works but the problem is the direction that the sort is happening. Everything is being sorted horizontally. I want to Sort it by the selected Column.
View 1 Replies
Feb 11, 2011
trying to execute an .exe (another program) from excel, where shall i start?
View 1 Replies
