Running a Module in Access

Ah - you have mistakenly just copied in some code without putting it inside a procedure. 

First up we need to move that code into a procedure.  I'll use a Function as a function is callable from a macro but a sub is not.

So change your module to look like this:

' CODE STARTS HERE:

Option Compare Database

Option Explicit

Function DoImport()

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 = True

' Replace C:\Documents\ with the real path to the folder that
' contains the EXCEL files
strPath = "C:\Documents and Settings\myName\My Documents\Access Test\"

' Replace tablename with the real name of the table into which
' the data are to be imported
strTable = "tablename"

strFile = Dir(strPath & "*.xls")
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
' EXCEL file after it's been imported
'       Kill strPathFile

      strFile = Dir()
Loop

End Function

I would modify it further to make it generic but I fear it may confuse you too much at this point.

So to call this function, make sure you make the changes in the code to reflect the paths and names that are for your file and then you can call it from the macro by using

RunCode

and the argument is

DoImport()