Forum Discussion

SuperUser20's avatar
SuperUser20
Copper Contributor
Nov 02, 2021

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.

 

 

access

12 Replies

Sort By
  • isladogs's avatar
    isladogs
    MVP
    Nov 17, 2021
    Try changing to acSpreadsheetTypeExcel12xml - this is needed for .xlsx files
    • tims's avatar
      tims
      Copper Contributor
      Dec 02, 2021
      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-statement
      • isladogs's avatar
        isladogs
        MVP
        Dec 02, 2021

        For 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

  • isladogs's avatar
    isladogs
    MVP
    Nov 02, 2021
    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).
    • SuperUser20's avatar
      SuperUser20
      Copper Contributor
      Nov 02, 2021

      isladogs 

       

      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

Resources