Page 2 of 2 FirstFirst 12
Results 16 to 21 of 21

Excel open/close with access 2016

  1. #16
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    5,894
    I have zero idea on what to do to even try to replicate the issue.



    OK, I have found your notes. Going, forward, you should assume that anyone opening an unknown db will at least try to prevent auto loading of forms or AutoExec macros from running.

  2. #17
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    5,894
    I might be losing sight of the issue here - that the excel file for one user doesn't open? I cannot see how you'd know, as
    a) you don't make it visible
    b) you close it immediately after opening it and without any changes being made:
    Code:
    Set xl = CreateObject("Excel.Application")
    Set WB = xl.Workbooks.Open(CurrentProject.Path & "\" & .ExcelFile)
    
    WB.close True
    xl.Quit
    I must be missing something - I can't see how the opening of this Excel file could be apparent to anyone.

    Other observations
    should never start object names with numbers (00)
    don't need record indicators on single record forms
    should not let objects retain names like Command115. Makes trouble shooting more difficult

    Your methodology seems somewhat convoluted (e.g. passing a whole form to a procedure when it seems the procedure could reside on the form itself).

  3. #18
    gg80 is offline Competent Performer
    Windows 7 Access 2007
    Join Date
    Jul 2010
    Posts
    280
    The excel file is opened and closed because that is the only way you can get the results of excel calculations imported back to Access. If you just export then import without open/close, the results of the exports won't be available. It seems that the open-close code isn't working in Access 2016 since the user with Access 2016 has to close and open manually to get the calculated results imported. Code works fine with access 2007 or 2003. Why?

  4. #19
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    5,894
    Well, I'm not able to follow your instructions, or at least I don't know how to validate the results and know if I'm experiencing the same failure to update. When I open the query
    q_Excel_Export_2 and the 2 forms are opened (that you directed to be) the query produces no records. That suggests the query isn't putting any data into the spreadsheet - other than the headers.

    One thing that you could try is to change the spreadsheet type. You've specified version 9, which is 2010. Not sure what happens to 2007 and earlier users in that case, which is what you're telling me others are using. If changing the version doesn't fix this, you'll have to tell me why the query has no records. Too much data removed?

  5. #20
    gg80 is offline Competent Performer
    Windows 7 Access 2007
    Join Date
    Jul 2010
    Posts
    280
    Not sure why you don't see anything in the export query. The member form must be open for the query to show the selected member. I put a note on the member screen saying that the proper result for phrfDw was twice the value of P which is displayed on the upper part of the screen. I made the P box green so easy to detect.

    Which version of Access are you using? If 2016, you ae verifying my problem. 2007 and 2003 work perfectly.

    I contacted a friend with 2016 today and he tried things out. Real mess that I haven't sorted out yet. I think that the export worked, but not the import. Didn't have much time, have to play with it more.

  6. #21
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    5,894
    At the least, I would suggest you never retain the subform control name created by Access, whether by wizard or not. This should be a rule regardless of what wizard you use, or what process you use whereby Access creates names. Your subform control and your subform have the same name. When I can't get data from the query is during code execution (I put a break on the transferspreadsheet line and then run the query) and AFAIK, the transfer function won't tell you if the query returns no data. If I remove the subform control reference from the query and use 7767 instead, it works in break mode. If the form is open and you run the query (don't push the button) the query works with the form reference.

    I would explain that oddity thusly: there is a difference between the vba side and the Access side when it comes to how queries and other things are handled. Without getting technical, I'll say that a query might run fine from one side but not the other due to how the query is interpreted. It's my guess that your form reference is ambiguous to the transfer function and that can be version dependent because of updates to dll libraries and such. Try replacing the query subform reference with a valid ID (such as 7767) and check on the problem pc. Regardless of whether or not that fixes it, you really should do the following things:

    -change the subform control (or the subform, or both) name; see
    http://access.mvps.org/access/general/gen0012.htm or
    https://access-programmers.co.uk/for...d.php?t=225837

    - then modify the query reference to suit. The syntax for subform control reference is like
    [Forms]![frmYourFormName]![subformCONTROLname].[Form]![txtYourControlName]**
    where subformCONTROLname is the name of the control holding the subform NOT the subform
    **this is how I discovered the potential ambiguity; the form control reference in the query didn't look correct

    - you use 1 for transferspreadsheet parameter for headers. The correct values are -1 (true) or 0 (false), not 1

    - compile your code and you will find that there are a lot of errors. If you don't fix them, prepare for unwanted results.

    That should keep you busy for a bit.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    Sometimes I talk to myself - like when I need expert advice.

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Compact and repair/ close access 2016
    By Simonhtc4 in forum Access
    Replies: 8
    Last Post: 04-20-2017, 11:15 AM
  2. Replies: 0
    Last Post: 01-05-2017, 11:47 AM
  3. Why does Excel close when I open Access.
    By Bmadigan2000 in forum Access
    Replies: 1
    Last Post: 12-23-2013, 11:11 AM
  4. Using Access to open and close Excel
    By murfeezlaw in forum Programming
    Replies: 4
    Last Post: 04-04-2012, 07:58 PM
  5. VBA to open excel, import on close of excel
    By bdaniel in forum Programming
    Replies: 0
    Last Post: 03-20-2010, 02:45 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums