Calling a MS SQL Server scalar UDF from vba
I am programming an Access 2007 (accdb, not adp) frontend at connect to a SQL Server 2005 backend. How can I call a scalar user defined function from my vba code? ...
vba
VBA: Difference in two ways of declaring a new object? (Trying to understand why my solution works)
I was creating a new object within a loop, and adding that object to a collection; but when I read back the collection after, it was always filled entirely with the last object I had added. I've come up with two ways around this, but I simply do not understand why my initial implementation was wrong. Original: Dim oItem As Variant Dim ...
Copy rows in excel and increment a column
I am trying to create an excel macro that will take a spreadsheet that has n number of rows in it and copy each row as many times as a number located within one of the cells. Also it would increment one of the numbers within a cell. For example I have a layout like the following: Column1 Column2 Column3 Column4, etc.. Data-a ...
MS Access 2003 - Message Box: How can I answer "ok" automatically through code
So a couple silly questions: If I include this in some event: MsgBox " ", vbOkOnly, "This little message box" could I then with some more code turn around and 'click the ok button. So that basically the message boox automatically pops up, and then automatically goes away? I know its silly because you want to know, why do you want th...
Update access table rst with named cell values from excel
I have got a table in access that I loop through using a DAO recordset. For every recordset I take a bunch of data to an excel spreadsheet and run it through a model in excel. This produces a bunch of results in excel which are calculated in named cells. I want to be able to update the current recordset in access with these results but ...
MS Access 2003 - Option Group frame: can I add text boxes that are part of the frame instead of rad button options?
Ok so this maybe a simple/silly question but I don't know so here goes: In access let's say I want to have a frame control, so I click the option group button and add it to the desgin surface. However, I am not wanting to use this as a option group with radio button selection, instead I would like to add text boxes instead the frame, so...
VBA - Create ADODB.Recordset from the contents of a spreadsheet
Hello, I am working on an Excel application that queries a SQL database. The queries can take a long time to run (20-40 min). If I've miss-coded something it can take a long time to error or reach a break point. I can save the results to a sheet fine, it's when I am working with the record sets that things can blow up. Is there a way ...
VBA How to find last insert id?
I have this code: With shtControleblad Dim strsql_basis As String strsql_basis = "INSERT INTO is_calculatie (offerte_id) VALUES ('" & Sheets("controleblad").Range("D1").Value & "')" rs.Open strsql_basis, oConn, adOpenDynamic, adLockOptimistic Dim last_id As String last_id = "select last_insert_id()"...
Pros and Cons of automating Excel using VBA vs .Net
I've been tasked with creating a financial planning tool in Excel that would benefit from some custom functions/macros. My initial reaction was to use VBA. I've used it to drive Excel before (say 5 years ago). But I then began to wonder if I would be better off using VSTO. Has anyone has experience using both techs and can list the pro...
MS Access 2003 - Formatting results in a list box problem.
So I have a list box that displays averages in a table like format from a crossyab query. It's just what I need the query is right, there is just one thing. I had to set the field properties in the query as format: standard..decimal:2. Which is exactly what I needed. However..the list box will not pick up on this. First I typed the cro...
Access 2003 VBA: Return only the index of the last item selected in a ListBox
I will preface this with saying, this is my first time using listboxes and earlier posts were criticized for lacking detail. So, all help is greatly appreciated and I hope this is enough information without being overkill. Currently, I have a listbox updating a junction table with an on click event (iterates through selected items and i...
Hidden pitfalls of VBA
This question is meant to augment the Hidden Features question at http://stackoverflow.com/questions/1070863/hidden-features-of-vba What are the biggest, most common, or most annoying pitfalls of VBA? Please list anything that might be described as programmer beware VBA behavior that requires painful and constant workarounds. Thin...
Excel 2003 VBA : how to paste a shape after selection
Just wondering how I can paste an object after I have selected it: sheet1.shapes("MyShape").select With Selection basically jsut wondering how to duplicate a shape object, or any object really. Eventually I am looking to use code to copy a shape object like above from Excel, and paste it into an access form automatically. Thanks! ...
Exporting Access Query to Excel
I've got an Access 2007 database on which I have created around 15 SQL queries to process specific data, I have created a main frame navigation menu using menus in Access, I now need to extract all th queries to Excel using VBA code, I have managed to do this with the code below by creating a button and specifying this code to it. Priva...
Adding references from VBA Causing Password Prompt To Appear
EDIT: I WILL GIVE A 300 rep BOUNTY FOR THIS:-) I have run out of ideas. I have a very simple macro that adds references. It looks like I have the same problem as this http://www.eggheadcafe.com/software/aspnet/35651964/excel-prompts-for-vba-pas.aspx Sub testAddSolver() Call AddSolver() End Sub Sub AddSolver() Dim strSolverP...
Sending Email from Lotus Notes using Excel and having Attachment & HTML body
Right I'm trying to send an Email form an excel spreadsheet though lotus notes, it has an attachment and the body needs to be in HTML. I've got some code that from all I've read should allow me to do this however it doesn't. Without the HTML body the attachment will send, when I impliment a HTML body the Email still sends but the attach...
Why Do People Add a Reference To XLA's Dynamically?
Just wondering why projects like solver.xla get added via VBA I ve seen a number of projects do this in the past - What does it buy you that adding it via the menu does not. ...
Using query parameter in Access report
I have a very basic access database where a query returns the sales made within a set start date and a set end date. The dates are set with two pop up boxes for the user to enter, first the start date, and then the end date. I have a report running off the back of this. How can I (using VBA) get at these dates then display them on the...
ArcObjects - enumerating feature classes and datasets within a geodatabase
I'm trying to enumerate the contents (feature classes and feature datasets, not interested in tables, etc) of a file geodatabase using vba/arcobjects. I have the file GDB set as an IGxDatabase object, but can't find a way of getting further in. I've had a look at the geodatabase object model and tried using IFeatureClass and IFeatureDa...
Pasting formatted Excel range into Outlook message
Hi everyone, I am using Office 2007 and I would like to use VBA to paste a range of formatted Excel cells into an Outlook message and then mail the message. In the following code (that I lifted from various sources), it runs without error and then sends an empty message... the paste does not work. Can anyone see the problem and better...