I have a template in Excel 2003 that copies in template sheets as needed. We are about to upgrade to Excel 2007, and I have found that when the sheets are added as in the past: Sheets.Add Type:="Z:\Investments.xltm" The buttons on the sheet move around and it drops random cells formatting. I am wondering if anyone else has encounter...
This is one of those things that I'm sure there's a built-in function for (and I may well have been told it in the past), but I'm scratching my head to remember it. How do I loop through each row of a multi-column range using Excel VBA? All the tutorials I've been searching up seem only to mention working through a one-dimensional ran...
The file exists, opening workbook works but Application.run gives 1004 Set wbtarget = Workbooks.Open(mypath + "\launcher.xls") Application.Run (wbtarget.Name & "!StartMerge") ...
In Excel 2003, I have a macro that accidentally tried selecting a page field item (New York) that didn't exist. This renamed the currently selected page field (Alabama) to the value I tried to select (New York). I now have 3 or 4 values that are now wrong. Is there a way to refresh these pivot table values to the correct values without ...
Is there a way to use VBA (excel) to generate a clock time with accuracy to a tenth of a second or less? eg: Sub test() MsgBox Format(Time, "hh:mm:ss???") 'not sure what this format should be... End Sub ...
I am interested if it's possible to do string encryption/decryption using Excel Visual Basic and some cryptographic service provider. I have found a walkthrough Encrypting and Decrypting Strings in Visual Basic, but it seems it's valid for standalone Visual Basic only. So would you suggest me another encryption method or show how the w...
We have a VSTO application-addin (not a document-addin) for Excel, and we want to expose an event to VBA code so that the VBA macro can do some action when this event fires in the addin. How can I get the VBA code to be able to subscribe to an event defined in the VSTO application-addin? I'd think that since the addin is loaded in the ...
Any suggestions for determining if a user-selected Excel file is the incorrect one? I am having the user select the excel file to be parsed and I want to refuse processing it if appears to be an incorrect format. ...
Hi Everyone, I wrote an Excel VBA macro to do conditional formatting using an intersect of two columns but I can't get it to work for some reason. If anyone has any idea on what I can do to fix it, I would sincerely appreciate it. I want to highlight both the source and the target columns in which there is a match or duplicate as foll...
Hi, I am using excel 2003 and I am having trouble attaching cells onto the body of an email. I got some of the code off http://www.rondebruin.nl/mail/folder3/mail4.htm but it does not work for me. What happens to me is that a spreadsheet would pop up that has Not Peer Review on it and an error message saying "runtime error '1004' PasteS...
Hey Everyone, I'm having a hardtime speeding up the processing of a very large textfile (~100 Meg or so). I've made caution to be very diligent using the redim preserve calls, and yet the function still takes 5 minutes or so to run. The textfile is basically sub reports which i'm trying to parse out. I only have access to the large f...
i'd like to write a batch file that would open a static Excel document and format it (from say 10 pages to 2 pages, taking out the empty columns) so that it is print ready. any ideas on how to format the excel document using my batch file? It could be a .VBS file as well I suppose. ...
Hi Guys, I need a function that can detect duplicates in a specified Excel column. I have this one but it does not work properly. It cannot distinguish between the value "46.500" and the value "46.5000". The countif function probably compares cells as numbers. These cells are formatted as text and I have even tried to add an apostrophe ...
I'm writing a program, with VBA, in order to manipulate Microsoft Project. But i have some troubles and i want to do some debug. How can I open a command line in order to see what's going on with some printfs which appear in the command line? ...
I have a function in VBA of the type Function MyFunc(Indx As Integer, k As Long, Rho As Range, A As Range) As Variant .... End Function which is called as a user-defined function from within the Excel worksheet. When called with the last two arguments being a range (i.e. Result = MyFunc(1,98,A1:A2, B1:B2)) it works fine. However, when...
This is probably a trivial question, but I'm curious as to what other Excel VBA programmers think. When I program excel I use Hungarian notation. Worksheet variables start with ws, workbook variables start wb, etc. When I use integers, I always use longs, because in the past I have exceeded the maximum value of an integer, and it take...
Hi Everyone, I am trying to use conditional formatting to highlight a row of cells containing key value pairs in another column when certain watch cells are yellow. I have a three columns (A,B,C) containing numeric digits and then two columns (key 1, key2) that is also numeric. Next to the two columns are sensor attribute data that is ...
I setup a "Name" to a column range, and I want to reference it later on: Dim r As Range r = Application.Names("Changes").Something I've tried a bunch of "Something" but cannot get this right. Thanks. ...
Currently there is a Excel 2002 spreadsheet writing/retrieving data from a Access 2002 database. IT is presently researching a upgrade to Office 2007 and when I upgrade both the spreadsheet and database the ADODB connection fails. The 2002 era VBA connection string is: Provider=Microsoft.Jet.OLEDB.4.0; Jet OLEDB:Database Password = st...
Hello again. I'm having a problem transferring a worksheet into another workbook. I've written several macros in my first workbook, as well as 1 or 2 forms, and I need to be able to move this worksheet that contains these macros and forms into a new workbook. If I just do a simple worksheet copy, the new version of the worksheet will ...