Forum Discussion
Regarding macros and function in Microsoft access
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 strPathFile
strFile = Dir()
Loop
End 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.comI am using FetchData() only, still it not working
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
