Excel open/close with access 2016

[gg80]

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.

[June7]

Apparently forum is dropping \ from between quote marks. What is .ExcelFile?

[gg80]

The code is in a module. .excelfile refers to a form's text box that contains the name of the excel program. The \ somehow got lost on my paste . This code works fine in Access 2007 and 2003. The problem is making it work with 2016

[June7]

Using the . without prefix should error, unless you wrap code block in a With Me qualifier.

Me.ExcelFile

I use Access 2010 and no issue opening xls or xlsx or xlsm files. Would not expect 2016 to be any different.

[Micron]

It isn't. Trying to troubleshoot a procedure with only a few lines certainly diminishes the chance of success. As for forum-messed up code, more than likely you can prevent such issues by using code tags. I'm surprised if no one has mentioned this to gg80 yet - aside from my signature.

[gg80]

I do have a with statement. And, code works perfectly in Access 2007 and 2003. only fails with 2016.

[gg80]

I don't understand your reply. Especially "forum messed up code". All the code that I have gotten from help in the forum has been great! The "few lines" are all the lines for opening and closing the excel file. I also showed a variation.

For some reason, the "" wasn't included in the paste or I somehow deleted it. I don't know about code tags-will research it. My main concern is that a procedure that works perfectly in Access 2007 fails in Access 2016

[June7]

"forum-messed up code" refers to the forum dropping the \ character from your post.

CODE tags generated by the # button above the post editor. Post your code between the tags.

So far, we have no idea why 2016 fails with the procedure. Have you tried on another machine running 2016?

[gg80]

Good idea-I will find another machine and try. I may also tell my partner to install 2007. As I understand it, you can have both on your machine at same time.

[Micron]

Running 2 different versions requires certain conditions, and in some cases, is not possible. Better see
https://support.office.com/en-us/art...7-b78c513788bf
or similar.

Would help if you could upload files for the db and workbook here, showing some dummy data if necessary. As I noted, 2016 will open xlsm, so it has to be something related to the files or the code (which we haven't seen all of) or a pc issue.

[gg80]

I have to get permission from the excel program designer. If he doesn't give it, I will create something with dummy data.. Appreciate your help much.

[Micron]

Whatever you post make sure it exhibits the problem. I dont suspect the workbook as much as the db.

[gg80]

For some reason, not explained, Forum refuses to load the file. I will work on it some more

[Micron]

if files exceed the size limit you should see a warning. Usually zipping is necessary to get the size down. Maybe this will help
https://www.accessforums.net/showthread.php?t=70301

[gg80]

Thanks. In addition to zipping, I deleted all non-relevant tables, queries etc. Makes it work!

Just to review. Works fine in Access 2003 & 2007 The open-close excel file code in a the module doesn't work with Access 2016 with this same excel file.

I added some notes that will hopefully let you get to sources quicker. Let me know if want more.