Forum Discussion
Regarding macros and function in Microsoft access
Hi,
I have created one function in Microsoft access and I am trying to run that function using autoexec macro, but I am getting the error as shown below even if I am using the same function name that I have created.
Idk what is the issue with this
Any help would be appreciated.
- Are you sure its a function and not a sub?
Do you have more than one function with that name - if so delete or rename any duplicates.
The function needs to be in a standard module (not a form module) and needs to be public (not private).yes, it is a function not a Sub
and I am using standard module not a form module
And the function I am using is :
Function FetchData()
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean' Change this next line to True if the first row in EXCEL worksheet
' has field names
blnHasFieldNames = False' Replace C:\Documents\ with the real path to the folder that
' contains the EXCEL files
strPath = "C:\Users"' Replace tablename with the real name of the table into which
' the data are to be imported
strTable = "data"strFile = Dir(strPath & "*.xlsx")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames, ""' Uncomment out the next code step if you want to delete the
DoCmd.DeleteObject Table, "data"
' EXCEL file after it's been imported
' Kill strPathFilestrFile = Dir()
LoopEnd Function
Hi,
You didn't tell how you refer to the function in the macro. Maybe you just didn't add the parenthesis, i.e. in the Function Name property of the RunCode action you should have: FetchData()
Servus
Karl
*********
Access-Entwickler-Konferenz: https://www.donkarl.com/?AEK
Access DevCon: http://AccessDevCon.com
Access FAQ: https://www.donkarl.com
- Try changing to acSpreadsheetTypeExcel12xml - this is needed for .xlsx files
- Try defining the function as 'Public' and see if it's then visible to the autoexec macro.
Public Function FetchData()
.....(stuff).....
End Function
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/function-statementFor info, functions are always public unless specifically labelled as Private. The quote below is from the article that you linked
Also, the OP has already confirmed the function is Public
