Access - Struggling with some basic VBA code

Question

Hi, I am trying to run some code in access to automatically refresh an excel workbook that is linked to sharepoint. I am using the following code in a module to do that:

---

Option Compare Database

Public Function RefreshExcel()

Dim appexcel As Object

Set appexcel = CreateObject("Excel.Application")

appexcel.workbooks.Open "FILEPATH"

appexcel.activeworkbook.refreshall

Set appexcel = Nothing

End Function

---

This functions properly when run as a module but once I try to put it in a macro or form using the RunCode command with the function name as RefreshExcel(), I get the error "The expression you entered has a function name that Microsoft Access can't find". I am very new to this and I'm sure there is an easy fix but I cannot find it. Any help is appreciated, thanks.

Answer 1

You'll also need to do some error handling.

Public Function RefreshExcel()On Error Goto ErrorHandler '>>> place this here'''End WithErrorExit:On Error Resume NextSet appexcel = Nothing '>>>Close/Destroy the object so that it does not stay active/hiddenExit FunctionErrorHandler:If Err.Number <> 0 thenMsgBox "Error: " & Err.Number & " - " & Err.Description , vbCritical, "RefreshExcel"End IfResume ErrorExit 'Exit Function gracefully to kill the Excel App/ObjectEnd Function

In the VBA Editor, go to Tools - Options - General Tab - Error Trapping - set to "Break on UnHandled Errors".

Answer 2

ciao Sean,

...mmmm...check your task manager if any excel process hung.... if any kill them, all them and try again to run the functions.

come back here to advise if helpful and fixed.

ciao, Sandro.

Answer 3

Hi Sandro,

Thanks for the help, this code seems to be working better. I can now actually run it with a macro although I get this error popping up from excel when I run it:

It functions properly when I press cancel but will not update when i press OK. Any idea why this is?

Thank you again

Answer 4

ciao Sean,

woud you mind having a look and a try to this solution :

Option Compare Database 

Option Explicit 

Public Function RefreshExcel() 

Dim appexcel As Object 

Dim filePath As String 

Set appexcel = CreateObject("Excel.Application") 

filePath = "C:\yourXlsFile.XLS"  '<<<=== enter here your xls workBook

With appexcel 

    .workbooks.Open filePath 

    .activeworkbook.refreshall 

    .activeworkbook.Close 

End With 

Set appexcel = Nothing 

End Function

remember to apply opciton explicit in your code it's helpful to declare correctly the variable.

HTH.

ciao, Sandro.