How do you get Excel to refresh data on sheet from within VBA?
How do you get spreadsheet data in Excel to recalculate itself from within VBA, without the kluge of just changing a cell value? ...
vba
Sorting ADO recordset text field as numeric
Using VBA i have a set of functions that return an ADODB.Recordset where all the columns as adVarChar. Unfortunately this means numerics get sorted as text. So 1,7,16,22 becomes 1,16,22,7 Is there any methods that can sort numerics as text columns without resorting to changing the type of the column? Sub TestSortVarChar() Dim strBef...
How can I send an HTTP POST request to a server from Excel using VBA?
What VBA code is required to peform an HTTP POST from an Excel spreadsheet? Accepted Answer Note: For greater control over the HTTP request you can use "WinHttp.WinHttpRequest.5.1" in place of "MSXML2.ServerXMLHTTP" ...
Easiest way to migrate Word 2003 custom macro toolbars into Word 2007?
I have a series of macros and toolbars that I developed for Word 2003. Now that my office is upgrading to Word 2007, I need to migrate them. The macros themselves migrate with zero effort, but the toolbars are a different issue. A random subset of the toolbars show up in the "Add-Ins" ribbon tab, but I haven't found a way to control w...
How can I enumerate the list of DSN's set up on a computer using VBA?
I have an Excel application in which I want to present the user with a list of the Data Source Names (ie: DSN's), whereby s/he can choose what data source to use. Hopefully once I've got the list, I can easily access the DSN properties to connect to the appropriate database. Please note, I do not want to use a DSN-less connection. ...
How to convert a DB2 date-time string into an Excel Date
I'll regularly get an extract from a DB/2 database with dates and timestaps formatted like this: 2002-01-15-00.00.00.000000 2008-01-05-12.36.05.190000 9999-12-31-24.00.00.000000 Is there an easier way to convert this into the Excel date format than decomposing with substrings? DB2date = DateValue(Left(a, 4) + "/" + Mid(a, 6, 2) + "/"...
How can I GZip compress a file from Excel VBA using code in an .xla file only?
I need to be able to GZip compress a file in an Excel VBA function. Specifically I need to be able to use the 'deflate' algorithm. Is there a way to do this without having to exec a command line application? With no dependency on external tools the code will be more robust. Ideally the code would make use of pre-installed VBA or COM li...
How can I get the currently logged-in windows user in Access VBA?
I found this via google: http://www.mvps.org/access/api/api0008.htm is this the best way to do it? ...
How do I base64 encode a string efficiently using Excel VBA?
I need to encode a 100KB+ string as base64 in VBA. Are there any built-in functions or COM objects available which will do this as a pure VBA approach is either complex or doesn't scale well at these volumes (see links from dbb and marxidad)? ...
What are the differences between using the New keyword and calling CreateObject in Excel VBA?
What criteria should I use to decide whether I write VBA code like this: Set xmlDocument = New MSXML2.DOMDocument or like this: Set xmlDocument = CreateObject("MSXML2.DOMDocument") ? ...
Excel detect inactivity
I want to take an action in an Excel workbook macro after a period of inactivity (hide/protect some worksheets). What is the best/simplest way to achieve this? Í'm assuming I'll use Application.OnTime to periodically check if the user has been active. But what events should I handle to see if the user was "active" (i.e. has does somet...
Can Generic Type Mapper (MSSOAP toolkit) be persuaded to handle empty arrays
I'm having the problem described here: http://groups.google.com/group/microsoft.public.xml.soap/browse_thread/thread/029ee5b5d4fa2440/0895d73c5c3720a1 I am consuming a Web Service using Office 2003 Web Services Toolkit. This generates classes for all the data returned by my web service: one of the classes has a property that is an arr...
How do I slice an array in Excel VBA?
What function can I use in Excel VBA to slice an array? ...
Dynamically add sender email to Message Header rule in Outlook 2007 via right-click
I would like VBA code for Outlook 2007 to right-click a message in any mail view, choose a rule to add it to, and have the sender email be added to the Message Header contains value for that rule. Only particular rules use Message Header as a criteria, so only those applicable rules should show when I right-click a message. ...
What is the best way to create user help documentation for an Excel VBA solution?
VBA solutions can vary widely in size. I would like to add user help documentation to all solutions but the level of effort to create and deploy the help needs to match the size of the solution. ...
OnClick in Excel VBA
Apart from being horribly annoyed with Excel VBA ... I have this question: Is there a way to catch a click on a cell in VBA with Excel? I'm not talking about the Worksheet_SelectionChange event, as that won't trigger multiple times if the cell is clicked multiple times. BeforeDoubleClick doesn't solve my problem either, I don't want to ...
Creating a compressed (or zipped) folder
Is there a way to programmatically create a compressed folder in Windows? I can't see a way to do this using the FileSystemObject (although there is the 'Compressed' attribute). I've seen zip dll's but I'd prefer to avoid having to re-distribute a dll if possible. Windows XP natively supports compressed folders after all. ...
How can I make use of .NET objects from within Excel VBA?
Can VBA code instantiate and use .NET objects? The specific class I'm interested in is System.IO.Compression.GZipStream. For Info GAC is the .NET Global Assembly Cache ...
Access date bug- dd/mm automatically got changed to mm/dd
I coded something using Date statement in ms-access vba. It was working fine till the start of this month. But now I am seeing a very peculiar bug in access vba that the Date has automatically changed the format from dd/mm/yyyy to mm/dd/yyyy. Has anyone else encountered the same problem ...
How to stop Excel from prompting to reopen a workbook?
I have a readonly Excel workbook containing a VBA application. The application saves any data that needs to be saved in a database, and the workbook is always closed without saving (by setting ThisWorkbook.Saved = True in BeforeClose). I have the following problem: User double-clicks on the workbook in Windows Explorer, workbook open...