I am using access 2007 that exports and imports data from excel. When I use an 2003 excel format with fileName.xls all works fine. (The excel file includes code that prevents "do you want to save" message when opening and closing). When I use the same code with an xlsm file I get an error message. My partner, with Office 2016 has to use xlsm because of the vba in the excel file.
The code that generates the message is for opening and closing the excel file. (need to open/close or don't get calc results)
Code:
Dim xl As Excel.Application, WB As Excel.Workbook
Set xl = CreateObject("Excel.Application")
Set WB = xl.Workbooks.Open(CurrentProject.Path & "" & .ExcelFile)
With an xlsm file, the "Dim xl as excel.application" portion of the code generates this error message:
"Compile Error
Can't find project or library"
Even if I switch back to the filename.xls excel file, get the same message. If I go back to the original Access program
and the xls file, it works fine.
My partner, using access 2016 gets other problems-they have to manually open and close the xlsm file to
get results. Are there some changes in vba necessary when running this code in an Access 2003 or 2007 file in Access 2016?
Very frustrating. Any help much appreciated.