I have an access 2007 file (saved as Acess 2003) that exports/imports to/from an excel 2003 xls file successfully. My partner has access 2016. She uses my Access 2003 program, but her Access 2016 requires an excel xlsm file file to export/import. We must open-close after export so that the calculation results can be imported (A link just takes to long to send results back). The reason we have to use excel is too long to discuss here.
The import/export action with the 2016 Access works, except for the fact that she has to open/close the excel file herself. The code just won't do it.
:
Here is my open close code (gotten from good people in this forum)
First one:
Dim xl As Excel.Application, wb As Excel.Workbook (works fine in access2003)
Alternate when above didn't work (also gotten on this forum)
Dim xl As Object
Dim wb As Object
then:
Set xl = CreateObject("Excel.Application")
Set wb = xl.Workbooks.Open(CurrentProject.Path & "" & .ExcelFile)
wb.close True
xl.Quit
I have checked several sites and other parts of this forum, but no answer as to why Access 2016 doesn't open/close the xlsm file. There is another problem when using xlsm file with Access 2007, but will discuss some other time.
Any help much appreciated.