Forum Discussion
SuperUser20
Nov 02, 2021Copper Contributor
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 tha...
isladogs
Nov 02, 2021MVP
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).
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).
SuperUser20
Nov 02, 2021Copper Contributor
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 strPathFile
strFile = Dir()
Loop
End Function
- Nov 03, 2021
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- SuperUser20Nov 03, 2021Copper Contributor
I am using FetchData() only, still it not working
- Nov 03, 2021
Hi,
Another common reason for the message are security restrictions. Do you perhaps see yellow warning triangles on actions in the macro editor?
Is the database in a "trusted folder" or have you disabled macro security?
Servus
Karl
*********
Access-Entwickler-Konferenz: https://www.donkarl.com/?AEK
Access DevCon: http://AccessDevCon.com
Access FAQ: https://www.donkarl.com