Application level events and processes in Excel are restricted to a single instance of the application. In the past I have prevented users from opening more than one instance of Excel when my add-in is running using the following code. Private Sub KillDuplicateProcesses() Dim objWMIService As Object Dim colItems As Variant ...
I'm trying to loop through a set of cells using VBA in a worksheet and check if they contain any data. My spreadsheet is like: __A_____B_____C_____D____E_____F____G 1| 3122 -1 -1 3243 2| -1 -1 -1 3243 1 1 1 3| -1 -1 -1 3255 1 5| 4232 2132 -1 3259 6| 7544 1333 324 3259 7| -1 -1...
Hi, Since a few days I'm using MS Access 2007 in a certian process. In my VBA code I use: CreateObject("Excel.Sheet") In Access 2003 a document was created in 2000-2003 format. Now in Access 2007 VBA enforces the new format. This causes problems in some additional coding. Any suggestions how to enforce older format (2000-2003) through...
Hi, I have an Access application where everytime a user enters the application, it makes a temp table for that user called 'their windows login name'_Temp. In one of my reports I need to query using that table, and I can't just make a query and set it as the recourdsource of the report, since the name of the table is always different. ...
In my excel file, I have a table setup with formulas. with Cells from Range("B2:B12"), Range ("D2:D12"), and etc every other row containing the answers to these formulas. for these cells (with the formula answers), I need to apply conditional formatting, but I have 7 conditions, so I've been using "select case" in VBA to change their i...
Using Access 2003. On a subreport I have arranged a row of text boxes that will be populated with dynamic data. (These will appear as column headers on a master report.) Due to report constraints, the text boxes are tall and somewhat thin; some incoming strings will be longer than other strings; strings are expected to wrap when necessa...
Hello, I have a listbox in a Microsoft Access form. The MultiSelect property is set to simple. I want to know which item in the listbox was clicked. Keep in mind that an item may be clicked to SELECT or UNSELECT an item. Is there a simple way to do this? If not is there a complicated way to do this? I tried to use the SendMessa...
I am using VBA (in Access 2003) and I'd like to use the IFilter mechanism to extract the textual contents of files. I found some some nice C++ sample code which makes it look relatively easy, but at the moment I can't even get the DLL call to LoadIFilter to work: Declare Function LoadIFilter Lib "query.dll" (ByVal pwcsPath As String, _ ...
I have a worksheet with a large number of cells containing large amounts of text. Within any particular cell there may be some text that is coloured red. I want to remove that text and copy it to a different column. I have a VBA function that does this by checking the cell contents character by character but the spreadsheet is quite lar...
How to pass an array as a parameter for a user defined function in MS Excel VBA? Eventually I want to test that if a given date (dateDay) is in several ranges of dates (arrayVacation): Function CB_IsInRangeArr(dateDay As Date, ParamArray arrayVacation() As Variant) As Boolean ' Test that the array is in the form of 2 columns and n...
Hi guys, I have a problem with some excel code that I am having trouble getting my head around. Okay so I am using the application.evaluate command in excel vba, office 2007. If i have Evaluate("SIN(45)") it returns a nice predicted number. However if I do Evaluate("eq") the code crashes. eq is an equation i am reading in from excel....
I have a macro that add hundreds of lines of data to an excel spreadsheet. I call a procedure from a loop which inserts each line of data. I have been applying the formatting for the row each time that I insert that data. However, during my testing I have found that I can insert all of the data about 3/4 of second faster (3.3 sec vs. ...
I have two tables in my access database that I want to be able to export to excel. I can do it by opening the table and then doing File->Export... and then choosing the format and typing in the file name. However, this way the user actually has to type the name in so there is room for misnaming the file or for saving it as the wrong for...
Hi! When a user start an Access 2007 database that have macros and vba, a security warning is shown. I want the user to deal with this warning, so if the the content is't enabled, the user should not be able to use the database. Now I use a macro named AutoExec (opens a form that works like a menu), and that macro is run before the us...
I know about Application.CutCopyMode, but that only returns the state of the CutCopyMode (False, xlCopy, or xlCut). How do I return the address of the currently copied range in Excel using VBA? I don't need the currently selected range (which is Application.Selection.Address). I need the address of the range of cells with the moving bor...
I need to grab a list of names from Excel and insert them into a Word document, printing one document per name. The document has some text and a bookmark called "name". The code is below. First, I want to know if it's possible to detect how long is the list of names in the Excel spreadsheet and grab that, instead of hardcoding the numbe...
does anyone know how to use VBA to collapse subtotals (once created) to level 2 so I only see the summary? ...
I have a UserForm with some textbox entry fields on it that are enabled/disabled by a checkbox. When a checkbox is clicked to check it, I'd like to move the focus into the now-enabled textbox. The textbox is the next control after the checkbox in the tab order, so it seems like using the tab order to find the appropriate textbox would b...
I am writing a program in VBA that first compiles a Range from a series of cells and then later loops through the range to collect the data in the cells. The problem is that I need the range to maintain the order in which I add the cells so that I can then collect the data in the correct order when I go back through. If the data are in...
My Excel VBA takes ~300 XLS files and grabs 8 cells to deposit into their own row. (Office11) I have several subs and functions that use location constants for the sourceData and destination locations. Grand Total I have 23 constant locations with Column numbers, cell locations. Question: Any suggestions on how to clean this up for r...