VBA - Using GetObject function - get error.

I have an MS Access application and it opens a file from MS Word. Before I close the MS Access form, I need to programmatically determine if the MS Word application is running. I use the following sub and function:

Private Sub cmdbCloseCC_Click() On Error GoTo PROC_ERR
Dim wn As String
wn = "Word.Application"
If Me.IsAppRunning(wn) = True Then
wo.Quit
Set wo = Nothing
End If
DoCmd.Close acForm, "frmCC_Status"
Application.Quit PROC_EXIT:
Exit Sub PROC_ERR:
MsgBox Err.Description
Resume PROC_EXIT End Sub ___________________________________________ Function IsAppRunning(ByVal appName) As Boolean
Dim oApp As Object
On Error Resume Next
Set oApp = GetObject(, appName)
'Error here - Run-time error '429':
'ActiveX component can't create object
Debug.Print oApp
If Not oApp Is Nothing Then
Set oApp = Nothing
IsAppRunning = True
End If End Function

Sponsored Links:

Related Topics

Inside Access 2007 open Excel
I am looking for the VBA code that will let me open an excel workbook and run a macro in it then close the workbook.
I am transfering in data from excel, but it needs manipulation first.
Using Access 2007 and Excel 2007.

Private Sub Command8_Click() On Error GoTo Err_Command8_Click Dim xlsApp As Excel.Application Dim xlswkb As Excel.Workbook Dim f As Object Dim myfile As String Set xlsApp = CreateObject("Excel.Application") Set f = Nothing myfile = ""
Set f = Application.FileDialog(3) f.Title = "SELECT PBUS DATA FILE TO IMPORT" f.AllowMultiSelect = False f.Show With f
myfile = .SelectedItems(1) End With
Set xlswkb = GetObject([myfile])

This basic code right here continues to get hung up on the GetObject


Want function to get current function name
I am developing an all-purpose error handler. As I see it, the "ideal" function would look like this:

Public Function Foo()
On Error GoTo er
'Code goes here.
ex: 'Cleanup code, closing open objects, etc.
Exit Function
er:
erf 'An error handling function, which writes this function's name
'and error stats to a table, then returns here.
MsgBox Err.Description 'Allows stepping through code in this function.
Resume ex 'Gets cleanup code.
End Function

The only issue I'm having is having to pass the current function name in to my error handler. Does anyone know a way to get this systematically


Include Password in VBA for ODBC Connection
I have a query that pulls from an external database via an ODBC Connection. This is the VBA for running and closing the query:

Function RunSalesandOrders() On Error GoTo RunSalesandOrders_Err
DoCmd.OpenQuery "Step 1", acViewNormal, acEdit
DoCmd.Close acQuery, "Step 1" RunSalesandOrders_Exit:
Exit Function RunSalesandOrders_Err:
MsgBox Error$
Resume RunSalesandOrders_Exit End Function

In order to open the query it requires a password however. This is a password required to log on to the Progress OpenEdge Wire Protocol Driver.

My question is - does anyone know how to include the password in the VBA function? I can't use SetWarnings as the password is for an external source


Run-time '429': ActiveX component can't create object
I have the below function that always crashes on the highlighted line. I have the following libraries added.Help please.

Libraries:
Visual Basic For Applications
MS Access 14.0 Object Library
OLE Automation
MS Office 14.0 Access database engine Object Library
MS Excel 14.0 Object Library
MS Outlook 14.0 Object Library

Function IsExcelRunning() As Boolean
Dim xlApp As Excel.Application 'On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
IsExcelRunning = (Err.Number = 0)
Set xlApp = Nothing
Err.Clear End Function


Where to define VBA Functions?
Where do I define a user-defined VBA functions so that I can call them in the query?

Here is the problem. I am defining a VBA functions called DateDiffW which calculates the number of weekdays (i.e. excludes sat and sun) between 2 dates.

In access 2007, under the "create" tab I clicked "macro" -> "module". Then I defined the function there, starting with:

Function DateDiffW(BegDate, EndDate)

I then saved the module.

In the query, when I call the DateDiffW function I get the error:

"Undefined Function 'DateDiffW' in expression


Compile Error
I have a function in a standard module. When the function is called, I get an compile error 'Method or data member not found'. There is nothing wrong with the spelling of the function name when it is called.

I don't get an error when I manually compile the code. I get an error on the next function in the module. If I run the function in the debugwindow I get the compile error.

I've compacted and repaired the dB. I've checked the referenced modules.


Passing Errors from Functions to Containing Sub
I'm using MS Access 2003. I have some validation code that I'd like to make into a function because the code will be used in multiple places. I'm thinking that if an error happens in the validation function, I want the calling sub to know about that.

So what is the best way to let the container sub (that called the function) know about the error that happened in the function? And should the error message be generated in the function or the container sub?

I cannot seem to get the container sub to see the error generated by the function.

Or is there a better way?


Compile Error: Argument Not Optional
I am trying to create the ACCDE front end but I am running into errors creating it. This led me into compiling my VBA code and I get the "Argument Not Optional" error highlight "Month" in the Function below. This function is used in Access while running a make table query.

Option Compare Database Option Explicit
Function LoadFactor1to12()
Select Case Month End Function


Module Error in Macro
I have created a module as below, which when I use Run in the VBA window does exactly what I want it to.
However when I then run this from a macro I constantly get a message box saying that
"the expression you entered has a function name that the [database] cannot find"

In the macro I have used Run Code with ChangeDataType() as the function name. The module is called
...
I'm stumped as I have tried every permutation of naming for the function I can think of, but still it sends an error every time.


VBA Compile error: Expected function or variable
I want the SQL code to get the last Reading_Number of a certain Disc_Size when the Disc_Size field is changed.

eg The last Reading_Number of a Disc_Size 2 is 33
The Disc_Size field is changed from 1.6 to 2 so now the value 34 will be automatically entered into the Reading_Number field.

I'm getting the following error at Private Sub Disc_Size_Change() :

"Compile error: Expected function or variable"

I have other VBA code that runs under certain conditions which I can post if it might be involved.


FileLen - Why do I get an 'Undefined Function' error?
I want to record the size of a file and have tracked down plenty of posts which suggest that 'FileLen' is the function that I need. However, whether I use it a query eg:

file_len_1: FileLen("C:\test.txt")

or in VBA eg:

Msgbox FileLen("C:\test.txt")

I get the undefined function error. How do I go about defining a function if necessary?!


Date function compile error
I have the "Date" function in my VBA code. It worked as of a few days ago, and I don't think I've changed any code. Now when I try and run it, I get a "Compile error: Can't find project or library" error. Does anyone know why I might be getting this


ActiveX Component Can't Create Object Error
My database works just fine on my hard drive but when I go to run it on the server, I receive the error:

'ActiveX Component Can't Create Object'

Here's the line of code that triggers the error:

Set appWord = GetObject(, "Word.Application")

Any suggestions for this? I don't own the server so I can't make any updates there


Problems with return statement
I'm new to VBA coding in Access and I ran into a problem with one of the functions I wrote. I tried to add a return statement to my function and the text gets highlighted in red, and when I run the Function I get a compile error that says "Type Mismatch". Here's my function:


Unable to copy Excel worksheets in VBA
Here is the code I am using in Access 2007 to manipulate Excel.
The sheets are renamed, but the workbooks remain hidden, and will not copy. It fails on the Copy After command.
I am using Excel 2007.
Any idea what is wrong?
DoCmd.TransferSpreadsheet acExport, 10, "tbl_A, "C:\A.xlsx, True
DoCmd.TransferSpreadsheet acExport, 10, "tbl_B", "C:\B.xlsx, True

Set MyXl = GetObject("C:\A.xlsx")
Set MyXl1 = GetObject("C:\B.xlsx")

MyXl.Worksheets(1).Name = "A_Sheet"
MyXl1.Worksheets(1).Name = "B_Sheet"

MyXl1.Worksheets("B_Sheet").Copy After:=MyXl.Worksheets("A_Sheet


Using vba variables in forms commands
I've been banging my head against the wall trying to get this to work:

I am using vba within MS ACCESS
I want to use passable parameters within a form command:

Here is my function code:
Function TESTFUN(FN, TB As String)
DoCmd.OPENFORM (FN), , , , , acDialog
Forms(FN).Text(TB).SetFocus
End Function

Here is my MACRO which calls the function:
TESTFUN("PARTA","010")

The PARTA form does open up OK, but the forms command does not work.
I'm getting a Run-time error '2450', Microsoft Access cannot find the
referenced form 'PARTA


Function Problem
I am trying to construct a row source for a text box in VBA using a DSum function. My problem is that in VBA I can put the double quote version of the quotation mark in the string and the function seems to require it. This one with the quotation marks works. Note the criteria section.


Returning a value via function
I'm using MS Access 2007 and having difficulty with VBA returning a value from a function. Here's my code, in a module:

Option Compare Database
Global CriteriaStr As String

Function Get_Criteria() As String
Return CriteriaStr
End Function
I get a compile error: Expected: end of statement
And it's indicating something is wrong with the "Return" line. What gives


Problem with query accessing VBA function
I have written some MS ACCESS 2003 VBA that allows a variable to be passed to a query, using a form to harvest criteria for the query.

Unfortunately, the variable does not seen to be passed on to the query, although the query function call is triggering the VBA - it just doesn't seem to be returning the value into the query.

I have tested the rest of the code and the value I am trying to get into the query criteria is working up to the point of the query calling the function,and then the criteria doesn't work in the query.

I have another function in the same query, set up the same way, and that one works fine for an integer value that I am using to return a unique event ID.


create a new table in Word
Using Access 2007, the following codes produces the following error:
.
Dim objWord As Object
Set objWord = GetObject("", "word.application")
objWord.Documents.Add
objWord.Visible = True
objWord.ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=1, NumColumns:=2, _
DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:=wdAutoFitFixed
.
Error:
Run-time error '424'
Object required
.
Note:
it's the objWord.ActiveDocument.Tables.Add statement that's causing the error.