All, I wrote a spreadsheet application which users load it by doubleclicking an icon I provide them. The problem is users have a lot of addins which slow Excel down. How can someone load Excel using command line switches to disable all add-ins ? The question applies to both Excel 2003 and Excel 2007. Many Thanks, MK ...
So this is a simpler form of my problem. Lets say I have 2 arrays. A= {1,2} and B={2,4,6}. If A and B share an element then delete that element from B. I know you can loop through and compare each element in A to each element in B, but there's got to be a better way! ...
I'm working with an Excel VBA "Sub" that contains a statement like: Application.Run ("Menu_SomeProcedure") which is calling some code or functionality which I need to inspect but I'm unable to find the implementation of "Menu_SomeProcedure" anywhere in the VBA module code. Where should I look to find it? I am using Microsoft Office ...
Right now I have a macro PopulateYearlyValues But it seems to me it's taking way too long Sub PopulateYearlyValues(ByVal Month As Range) Dim c As Double Dim s As Double c = Application.WorksheetFunction.Match(UCase(Month.Value), ActiveSheet.Range("AA5:AX5"), 0) s = (ActiveSheet.Range("AA5").Column - 1) With ActiveShe...
Do you know of any good doc on Excel PivotTables object model ? I have read "Excel 2007 Programmers reference", googled a bit, and found small bits here and there, but nothing global, coherent and complete. Preferably for version < 2007, so I keep upwards compatibility when developing. Thanks ! ...
Hi I have created a xlt excel template which works fine in Excel 2007 under compatibility mode and shows no errors on compatibility check. The template runs a number of Macros which creates pivot tables and charts. When a colleague tries to run the same xlt on excel 2003 they get a Runtime error 428 (Object does not support this prope...
I have a ocnnection to SQL Server set up in my vba code. The format is: strConn = "ODBC;Driver=SQL;Server=SQL1;Database=DB1;Uid=1;Pwd=1" I have this in 4 sheets, but there will be times when I will want to change it to call from SQL2 or SQL3, and instead of changing the code on each sheet 4 times, I want to change it only once. Is t...
I would like to create a excel Add in which creates some additional toolbars and Menu buttons. But I want this addin to load only when a specific workbook is opened. I dont want to load the Addin if anyother workbook is open. I would like to know what are the possible ways to solve this problem and what is the best approach to implement...
Hi A little help needed, I have a Macro automatically creating pivot tables and charts, this is all working fine but I am getting (blank) in my pivot table becuase my range is all the way to 65536. How do I automatically get the lastrow / column in my source data so I dont get any blanks. The data is changing constantly so this needs to...
I have the following macro which needs to loop though an Excel-2007 table. The table has several columns and I am currently finding the correct column position using the Index property columns. Using the index is the only way I could find to correctly index into the fName object. The better option I am hoping for is to access specific c...
I am designing a program behind excel and using VBA 6.5. I am having some problems with the built in dictionary object. I understand the basics and have found numerous examples on how to add simple data to them but I'm more concerned with custom objects. My problem: I have an object with several constructors - X, Y, Yf, A, P, etc. Now t...
I have an attendance sheet for employees. Day of the Week Mon Tue Wed Thu Fri..... Date 1 2 3 4 5 ...... Names Mr x Mr Y Mr Z Mrs P M.s q I want a macro that will ask the employee his/her name and employment number and type the word "Present, Time: " on the spreadsheet against that empl...
Using Excel 2000 and need assistance in programming an image to toggle between visible and hidden when a cell is active -or- a simulation of that event. Given a list of five items in column A, a separate image is associated with each item. On opening the file all images should be hidden except in the event the file is open with one of ...
I'm working on a project that is basically a hack on top of a hack on top of an excel sheet and unfortunately we don't have time to re-factor but the core code simply isn't fast enough. We have assemblies which are made out of sub-assemblies and parts. So an assembly is the super structure and a part is the smallest structure. We have ...
Hey. I urgently need some help on building up a complete table using VBA. I have got data from two seperate sources of data and i need to put them into same table based on one of the category "Car". I've uploaded an example here .http://www.speedyshare.com/files/23058842/abc.xls ...
Hi there, I have this: ActiveCell.Formula = "=COUNTIFS(G$4:G$" & (i - 1) & _ ", Sheets(ActiveSheet.Name).Cells(3, 25).Value ,F$4:F$" & (i - 1) & _ ",J4)" I want to use this: Sheets(ActiveSheet.Name).Cells(3, 25) so that I can make in loop to scan through column. as this reference cell must b...
I'm trying to use Excel VBA to write to a text file. I'm doing a few of these: MyFile1 = "C:\outputFromExcel1.txt" fnum1 = FreeFile() Open MyFile1 For Output As fnum1 and then writing to them like this: Print #fnum1, text All variables in the above are declared just with Dim. I'm writing hundreds of lines to the files and, very r...
I am making a spreadsheet that offers a column for user input. Each worksheet changes based on the month the user has given. If Target.Value = "January" Then ActiveSheet.Range("K7:K44").Value = ActiveSheet.Range("BA7:BA44").Value ElseIf Target.Value = "February" Then ActiveSheet.Range("K7:K44").Value = ActiveSheer.Range("BB7:BB4...
Hello, I am working on a table(in document A) which needs to be referred to another document(document B, not sheet 2 of document A). I used macro and VLOOKUP to link the data and it works perfectly well when both documents are opened. However, once i closed document B, the data on the table cannot be shown with a message " subscript out...
I often use MS Excel's Get External Data to create simple reports - running queries against databases and displaying nicely in Excel. Excel's great features like filtering and pivot tables and familiar interface for users make it quite good for this. However, one limitation with Microsoft Query is you can't add parameters to queries that...