vba

Passing Object reference in Visual Basic (VBA)

How to pass Form TextBox reference to method? Following code is issuing exception. Private Sub DoSmthWithTextBox(ByRef txtBox as TextBox) txtBox.BackColor = vbRed End Sub Private Sub TextBox1_Change() DoSmthWithTextBox Me.TextBox1 End Sub Problem appears when'DoSmthWithTextBox Me.TextBox1' passes string from TextBox1 insead ...

exporting access tables to excel

Hi I am trying to use the following code to export tables from access to excel I am getting error object does not support property or method at the end Set objexcel = New Excel.Application objexcel.Visible = True If Dir("C:\reports\Data_Analysis1.xls") = "" Then objexcel.Workbooks.Add Set wbexcel = objexcel.ActiveWorkbo...

Can't instantiate a COM object written in C# from VBA (VB6 ok)

Using VS 2008, here is my COM object using System; using System.Collections.Generic; using System.Text; using System.Runtime.InteropServices; using System.Windows.Forms; namespace TestCom { [Guid("9E5E5FB2-219D-4ee7-AB27-E4DBED8E123E")] [ClassInterface(ClassInterfaceType.AutoDual)] [ProgId("Test9.COMINT")] public cl...

How do I call an xll addin function from vba?

I have a 3rd party XLL addin I'd like to wrap in my own custom vba function. How would I call the 3rd party function from my code? Thanks ...

Setting a font color in VBA

Hi. I want to set the font color of a cell to a specific RGB value. If I use ActiveCell.Color = RGB(255,255,0) I do get yellow, but if I use a more exotic RGB value like: ActiveCell.Color = RGB(178, 150, 109) I just get a grey color back. How come can't I just use any RGB value? And do you know any workarounds? Thanks. ...

What's the fastest way to walk through an Excel Range object backwards?

The title pretty much says it all. I'm using VBA in the Excel VBE, but c# or vb are fine. The concept should hold true across the languages. Thanks. ...

How to adjust macro?

Below was a macro provided, but I need to know how to do some adjustments to save it to the file name fixed to a specific worksheet, and specific cell with dates( eg. worksheet name is: AA on cell "B2" with ZZ ). So the filename will be ZZ AA 23122008.csv Thanks in advance. Public Sub SaveWorksheetsAsCsv() Dim WS As Excel.Worksheet D...

How to copy and paste worksheets between Excel workbooks?

Hi How do you transfer a worksheet from one excel app(1) to another(2) if you have two excel apps open using VBA? The problem is, the programmer uses javascript, and when you click on the button that transfers the web data to a xl workbook, it opens a new Excel app. I know part of the code would be: Workbooks.Add ActiveSheet.Paste ...

Is it possible to convert VBA to C#?

Hi, I have a few modules of block of code in vba to run on few access databases. I would like to know how should i proceed if i wanted to convert the coding to c# environment. And is it possible to implement and obtain the same results as i am getting now with access and vba. I am completely new to c# at this point. ...

Create a query dynamically through code in MSAccess 2003 [VBA]

Hi I need to create a query in MSAccess 2003 through code (a.k.a. VB) -- how can I accomplish this? ...

Form level event for when data is changed on a MSAccess form?

Well basically I am working on an MSAccess application and on the form where administrators will view and edit user data I want to be able to call an event with the old and new values of a field whenever it is changed. This function will add a record into the audit table to track changes. I have no problem creating the query to add the...

For each not working properly in VBA code

Well I am almost done finalizing the auditing portion of my application that I discussed here. The way I am doing it is looping through all text fields, drop down boxes and checkboxes and storing their values in the form_load event. Then I am doing the same thing in the form_afterUpdate event and comparing the two. If there is a diffe...

McAfee deletes code from VBA module

I am trying to program an Excel module where it dynamically inserts code in new objects in a form that is created at design time. I am using this code where "Code" contains a string with the actual code that should go into the DstrFiles object. Dim DstrFiles As Object Set DstrFiles = ThisWorkbook.VBProject.VBComponents("DistributeFile...

Class (Static) Methods in VBA

I wonder, whether it is possible to create class-methods in VBA. By class-method I mean methods that can be called without having an object of the class. The 'static'-keyword does that trick in C++ and Java. In the example below, I try to create a static factory method. Example: 'Classmodule Person' Option Explicit Private m_name As S...

String acting abnormally in a For Each loop in VBA code in MSAccess

Okay so I started with a question and have made a lot of changes to the code based on suggestions from this site and others so I figured I should create a new question. Even though my code is shorter and more efficient the same problem persists; I am using a string called strSQL that contains and INSERT statement that I want executed. ...

How to stop renaming of excelsheets after running the save macro

Below is a macro to save multiple sheets to different csv files BUT it keeps renaming and saving the original workbook, how to stop this. Private Sub CommandButton1_Click() Dim WS As Excel.Worksheet Dim SaveToDirectory As String Dim CurrentWorkbook As String Dim CurrentFormat As Long Dim myName As String myName = myName & Application....

Create an Excel macro which searches a heading and copy-paste the column

I am new to Excel macros. I have some columns with headings scattered in many sheets. I would like to type a heading in some column which has the cursor and have the column with that heading copy-pasted to the column with the cursor. Is it possible to do this by recording a macro? How? If not, how do I do it programmatically? ...

Dictionaries and Hash Tables in MS-Access 2007

I'm want to use either a hash table or a dictionary in my access program. Apparently, I'm supposed to be using the Microsoft Scripting Runtime Library for this, but it doesn't work. Dim Dict1 As Dictionary ' Create a dictionary instance. Set Dict1 = New Dictionary It can't find the methods ".compareMode" or ".Add": With Dict1 ...

Writing email messages to flat files in Outlook with VBA

I have written a VBA app that opens a folder in outlook and then iterates through the messages. I need to write the message bodies (with some tweaking) to a single flat file. My code is as follows... Private Sub btnGo_Click() Dim objOutlook As New Outlook.Application Dim objNameSpace As Outlook.NameSpace Dim objInbox As MA...

Quitting Excel using VBA causes run-time error 424

I have been writing a VBA macro that opens a HTML document within Excel (in order to perform various calculations on it). Excel will search for the HTML document within the current folder. If it can't find it there it will produce a file open box where the user can browse to the location of the HTML document manually. All good so far....