Hi not sure if this will help, but I do something similar but will a few subtle changes.
I do all of any workbook manipulation from the excel code modules (generally speaking though you shouldn't actually need to ever select or activate a range or workbook).
I would pass any variables from access to excel and allow excel to do the heavy lifting by doing this.
IN EXCEL
Code:
Sub Test (WorksheetName as string, FileName as string)
debug.print worksheetname
debug.print filename
end sub
IN ACCESS
Code:
Sub ExcelCodeRunTest
dim xl object
set xl = createobject("Excel.Application")
xl.run "Test", "TestWorksheetName", "TestFileName"
End Sub