I've got some 2003 Excel documents I'm programmatically (ASP.NET C#) reading using System.Data.OleDb after a user uploads them to a web site. The uploaded documents are derived from a template. I'd like to keep the derived sheets locked, but I can't seem to find a way to read them using OleDb. I'm currently unlocking the derived sheet...
I have a query that runs in an Excel Macro that has been changed and I can't figure out how to change the code in the macro. The original query is: With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _ "ODBC;DSN=MS Access Database;DBQ=" & strpath & ";DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout" _ ), A...
I have 2 separate sheets, lets call them sheet A, sheet B. I have data in sheet B which is also in sheet A. I want to find those rows that are equal and remove them from sheet B. I cannot combine the 2 sheets and use filters because I'm doing dynamic SQL to query different data. Each sheet has a unique key column I'm ok with VBA sugg...
I do an import data from a query I created in MS Query. Lets say the columns are in order A,B,C,D,E,F in the Query Editor. Once the I save the query, return data to Excel, the imported data has a new column order A,B,C,F,D,E -- note the F column was moved where D was. Any ideas on how to solve this issue? Thanks guys. Assume variabl...
What is the difference between adding a reference to Excel 11.0 from .NET vs COM? (In C# if that even matters) -Edit- To clarify, I am talking about the .NET and COM tabs in the Add Reference dialog, thanks for pointing that out Hans Passant. ...
Hi, I'm trying to copy an excel sheet with python, but I keep getting "access denied" error message. The file is closed and is not shared. It has macros though. Is their anyway I can copy the file forcefully with python? thanks. ...
I am using this macro: http://support.microsoft.com/kb/119826 to try and clean up hidden names in my excel file. It works for most of the hidden names, but not for a few _123Graph names. I'm not sure where these names came from, but when I try to delete them I get a 1004 automation error. The knowledge base mentions that names with sp...
Is there a way to copy an Access 2003 pivot table view (used to pivot 206,000 records) to Excel 2003 using VBA? ...
So I'm working on this chart in Excel, and the chart looks like two sides of a triange, like the picture at the link below: http://a.imageshack.us/img832/6207/triangle.png. I'd like to make a line (like, with an autoshape for example) that connects the 2 endpoints to form a triange; ie a line going from coordinates (4,1) to (4,5). I t...
I have in a Excel field (created by someone) a formula containing an Array: =PERCENTILE(Scale1, 0.5). I need to expand this array to other fields. Usually an Array is a range like A1:C52, so expanding it means modifying the formula to, let's say A1:C152. But this "Scale1", even if I can see its border in a blue color, I can't modify i...
Hi All, this is not the usedrange issue. For example in excel user selects a range (possibly empty) using mouse, let's say B4:C12. And let's say after this without deselecting the range user presses the macro, and macro should tell B4:C12. Can anyone show example? Thanks a lot in advance! the macro should be smth along the lines of the ...
I am new to VBA and i am struggling trying to make this work. I am in need of a macro that will process each cell\Column on Sheet1 and Put the Results on Sheet2. I'm sure this is pretty easy for those who are more advanced with VB code. It contains many columns.. Anytime we encounter a "—" or an empty cell we populate the cell with -999...
I develop for clients who are still using Excel 2003 on Windows XP systems. Therefore we develop within a Virtual Machine running XP and Office 2003. I often create menu or control sheets within files using Form Control objects, mainly button controls. Sometimes when I open these workbooks up, the controls resize themselves (bigger, sma...
Question How do I access a public constant defined in another Excel workbook? Setup Workbook.xls My VBA code runs in the current Excel workbook called "MyWorkbook.xls". It has some VBA code in it which reads a file name --"OtherWorkbook.xls in this case-- from cell "A1". Then, the code should read a constant defined in that other wor...
The following bit of VBA will highlight any cells in a sheet with data validation errors: Sub CheckValidation(sht As Worksheet) Dim cell As Range Dim rngDV As Range Dim dvError As Boolean On Error Resume Next Set rngDV = sht.UsedRange.SpecialCells(xlCellTypeAllValidation) On Error GoTo 0 If rngDV Is Nothing Then sht.ClearCircles E...
What is simplest/best way to achieve functionality in vba in Excel: - button "add person" which will add 4 rows in which user specifies name..., also " - button "delete person" appearing in new added entry - to delete it What is a workflow for doing that? Should I record that first? Should I write VBA only? How to keep the part that c...
I created a dropdown by dragging the combo box onto my sheet from the UserForm toolbar. I assigned some values to it from some cells in the book. Now I want some vba code to access the selected dropdown item's value in form of a string. My dropdown contains only text. Also how do I find the name of this newly created dropdown...it's n...
Hi, I would like to insert my wpf application in excel sheet just like an OLE object. Is it possible? If yes, how we can do this. In Excel 2003 when we click Insert->Object menu item, we get the 'Object' diloag. The 'Create New' tab of this dilaog contains varios OLE objects. I would like to display my DotNet exe application in this li...
Is there any way to extract the workbook name, but then extract only a part of it. Any version of excel would be fine preferably 2003. For example "Help_TicketID123456788.xls" "Help_TicketID563565464.xls" ... So then I'd like to extract the ID numbers and put them into a column on a master worksheet in another workbook. Additi...
I'm working on an Excel 2003 addin (users will upgrade to newer Office, but not any time soon), and am wanting to use functionality to automatically check for a newer version, and deploy it if there is one. I can't use ClickOnce as that is Office 2007 (so VSTO v3) or better. I'd like to have some functionality that does this kind of ta...