VBA - Using GetObject function - get error.
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.
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.
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:
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.
Visual Basic For Applications
MS Access 14.0 Object Library
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
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
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:
or in VBA eg:
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"
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
Here is my MACRO which calls the function:
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
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
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.Visible = True
objWord.ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=1, NumColumns:=2, _
Run-time error '424'
it's the objWord.ActiveDocument.Tables.Add statement that's causing the error.