vba

Using an embedded Word document to create a new instance of that document.

For a variety of reasons that are immutable ... I have a Word document which contains a VBA application (the 'app document') which creates a new document based on another document (the 'template') which contains the framework for the new document. I want to embed the 'template' into the 'app document' so that I deliver one file and I kn...

MS-Access VBA: form_error vs on error

I am trying to set up error handling for a MS-Access application. The standard method to do this seems to be with an On Error statement in each subroutine/function. It seems simpler to me to use the Form_Error function to catch all the runtime errors in that form as opposed to an On Error statement for each sub/function called by an even...

Achieve Rails-style migrations in MS Access VBA application

This is a simple way to do Rails-style migrations in a VBA application. Just add additional migrations like migration(name, sql_string, database) to run_migratons and call run_migrations somewhere in the beginning of your execution. Function migrate(signature As String, sql As String, dbs As DAO.database) Dim rs As DAO.Recordset ...

Problem with Refreshing entire excel workbook

Hi, I'm trying to refresh a entire excel workbook using Application.CalculateFull function. However, I found that, the sheet does not get not updated ALL the time (sometimes it do). A random number of functions only get updated at sometimes. I'm using excel 2007, please tell me what's going on with Application.CalculateFull function. T...

How to call user define function when excel sheet being opened

Hi, I'm trying to call a function when a workbook is being opened. I used workbook_open() event. But I notice that before calling function which is inside workbook_open(), all the functions that already exists in the workbook are being called. How can I call my function to execute before calling any of functions in the workbook? Thank...

MS Access 2003 - VBA for altering a table after a "SELECT * INTO tblTemp FROM tblMain" statement

Hi. I use functions like the following to make temporary tables out of crosstabs queries. Function SQL_Tester() Dim sql As String If DCount("*", "MSysObjects", "[Name]='tblTemp'") Then DoCmd.DeleteObject acTable, "tblTemp" End If sql = "SELECT * INTO tblTemp from TblMain;" Debug.Print (sql) Set db = CurrentDb db.Execute (sql...

VBA error handling and MZ-tools

Thanks to reading about error handling on StackOverflow, I discovered Mz-Tools. However, I am wondering if there is a way to simultaneously update all the error handlers added by MZ-Tools. If I add an error handler with MZ-Tools and then change the default error handler (via Options|Error Handler on the Mz-Tools toolbar), is there any ...

Is there a way to take a screenshot in MS-Access with vba?

I want to use vba to take a screenshot (which will then be sent as an email attachment). Ideally, I'd like to take a screenshot of just the active form. Is there any way to do this? ...

Relative references in Excel VBA Formatconditions.Formula1

Hi All, I am having some trouble with the relative referencing, when trying to cycle through conditional formats in an Excel sheet. What I am trying to do is loop through the conditions, evaluate them to see if they are true, and if they are, applying the background color of that condition to another cell. I know that .formula1 has so...

vba excel: do something every time a certain variable is changed

im doing a bunch of stuff to the variable St For i = 1 To 30000 Randomize e1 = Rnd e2 = Rnd z1 = Sqr(-2 * Log(e1)) * Cos(2 * 3.14 * e2) z2 = Sqr(-2 * Log(e1)) * Sin(2 * 3.14 * e2) St = So * Exp((r - (sigma ^ 2) / 2) * T + sigma * Sqr(T) * z1) C = C + Application.WorksheetFunction.Max(St - K, 0) St = So * Exp((r - (sigma ^ 2) /...

How do I get the auto number after an INSERT in Ms Access?

I have query in vba like this: DoCmd.RunSQL "INSERT INTO table (value) VALUES ('example')" ( with an auto incremented primary key called id) How can I get the auto incremented id generated by this insert? ...

Determine whether a Canvas has been inserted into each page in Word

Hi, Currently i have a code which will print OMR Mark on each pages. Basically i insert a Canvas into each page and subsequently an OMR Mark Line Series are inserted into the Canvas. Recently i found an issue that somehow one of the canvas is placed out of a page and it appears at the previous page instead of the current page. Below i...

VBA + Send Mail from Word 2007

I got below code in my Word Document (office 2007) to send a mail with attachement It throws syntax error (file not found) at line .Attachement.Add "C:\abc.txt" Code: Private Sub CommandButton1_Click() Dim outlookapp As Object Dim item As Object Dim subject As String Dim msg As String Set outlookapp = CreateObject("outlook.app...

In subform of a MS Access form, how to hide/show the vertical scroll bars as needed?

I have a subform in an MS Access form which is not hiding/showing the vertical scroll bars as needed. In the example picture, the vertical scroll bar is showing even though there aren't enough records to warrant vertical scrolling. How do I get the scroll bars to show only when they are needed? Do I need to add an if-then condition to ...

concatenate multi values in one record without duplication

I have a dbf table like below which is the result of one to many join from two tables. I want to have unique zone values from one Taxlot id field. table name: input table tid ----- zone 1 ------ A 1 ------ A 1 ------ B 1 ------ C 2 ------ D 2 ------ E 3 ------ C Desirable output table table name: input table tid ----- zone 1 ------ A,...

Searching a document for multiple terms in VBA?

I'm trying to create a macro to be used in Microsoft Word 2007 that will search a document for multiple keywords (string variables) located in an external Excel file (the reason for having it in an external file is that the terms will often be changed and updated). I've figured out how to search a document paragraph by paragraph for a si...

Nested IF statements in Excel [Over the 7 allowed limit]

hey guys, i am trying to create a spreadsheet which automagically gives a grade to a student based on their marks they got. I've apparently hit excels nested IF statement limit which is 7. here's my if statement: =IF(O5>0.895,"A+",IF(O5>0.845,"A",IF(O5>0.795,"A-",IF(O5>0.745,"B+",IF(O5>0.695,"B",IF(O5>0.645,"B-",IF(O5>0.595,"C+",IF(O5...

Auto accept outlook VBA

Is there a VB macro or some sort of add-on out there that will allow me to auto accept invitations in outlook by sender or by folder? I was thinking about doing a VB script for this but I don't want to re-invent the wheel? ...

MS Access 2003 - text box calculation on a form

Lets say I have two text boxes on a form. the first returns a count value from a SQL statement, or a domain aggregate expression, etc. the second does the same with additional parameters. Now i want to have another text box (#3) that divides one by the other for a very simple percentage. like this for a controlsource: =[textbox2]/[text...

How to change the range of a chart in Excel using VBA?

Hi guys, I'm using an Excel sheet to keep track of a particular time series (my daily weight, if you must know). I created a macro that inserts a row, automatically adds today's date and calculates a moving average based on my input. There is also a chart to visualize my progress. I have tried recording a macro that updates the time s...