+ Reply to Thread
Results 1 to 14 of 14

MS Access 2016 ERROR 438 when exporting Query and creating Chart

  1. #1
    Forum Contributor
    Join Date
    04-22-2014
    Location
    Turrialba, Costa Rica
    MS-Off Ver
    Excel 2010
    Posts
    108

    Unhappy MS Access 2016 ERROR 438 when exporting Query and creating Chart

    Hello
    With the help of this Forum, I almost finish my Relational Database Project exercise.

    In my Db I have a Form with three textboxes and a one command button:
    txtbx_plot : user enter a value
    txtbx_from:user picks date1
    txtbx_to : user picks date2
    cmb: exports Query to Excel at "test2" carpet and creates a chart. This is my code:

    Please Login or Register  to view this content.
    Sometimes this code is spewing this error message: 'ERROR 438 Object doesn't support this Property or Method',
    and then it highlights in yellow this line:
    Please Login or Register  to view this content.
    To fix the error I open Task Manager to force quit all Excel Application and delete the exported query file in test2 carpet path. Therefore, any time I run the code,
    I must check that any Excel Application is not running in Task Manager and the same exported query file name is not in the test2 carpet.

    How can I avoid error 438 when running my code?
    How can I overwrite the exported query file name?
    In addition, if the user makes a mistake in any textbox in the Form, how can I avoid my macro stops?

    Trying to fix myself SUCH ERROR 438 I already studied the links below with no success
    http://dailydoseofexcel.com/archives...ivex-controls/
    http:////social.technet.microsoft.com...14?forum=excel
    http://answers.microsoft.com/en-us/o...d-e6589dec213a

    In spite of heavy googling, I am not able to find solutions to my problems.

    Any help would be greatly appreciated.

    Cheers

  2. #2
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,887

    Re: MS Access 2016 ERROR 438 when exporting Query and creating Chart

    You have an unqualified reference. You must use:

    Please Login or Register  to view this content.
    and not just

    Please Login or Register  to view this content.
    Rory

  3. #3
    Forum Contributor
    Join Date
    04-22-2014
    Location
    Turrialba, Costa Rica
    MS-Off Ver
    Excel 2010
    Posts
    108

    Re: MS Access 2016 ERROR 438 when exporting Query and creating Chart

    Καθαρτήριο
    Now with your help, the error is fixed.

    For my second question, If a file name already exists, any time I export a query I don't want to be prompted to open Task Manager to end excel application and to Delete file name.

    I wrote this lines of code in my Macro before End Sub:

    Please Login or Register  to view this content.
    and VBA displays: Compile Error: Method or data member not found”

    Any idea is welcome

    I also tried this lines if code:

    Please Login or Register  to view this content.
    and VBA displays: Run-time error '13' Type Mismatch

    I need to overwrite the existing file with any of the code lines indicated above, but correctly written. Therefore, is there a way to overwrite the existing file programmatically in the save as step at the bottom?

    I really appreciate your help.
    Cheers

  4. #4
    Forum Contributor
    Join Date
    04-22-2014
    Location
    Turrialba, Costa Rica
    MS-Off Ver
    Excel 2010
    Posts
    108

    Re: MS Access 2016 ERROR 438 when exporting Query and creating Chart

    Καθαρτήριο
    When I use:
    Please Login or Register  to view this content.
    My macro export the query and creates the Chart, however also displays this errors messages:
    VBA displays "Error 1004 - “Cannot access file” when opening workbook"
    Check if the directory exists
    Check if the directory with the file is not only for lecture.

    I am really confused.
    Any help is welcome.

  5. #5
    Forum Contributor
    Join Date
    04-22-2014
    Location
    Turrialba, Costa Rica
    MS-Off Ver
    Excel 2010
    Posts
    108

    Re: MS Access 2016 ERROR 438 when exporting Query and creating Chart

    Hello
    As I said, I am using Access VBA to export a query to excel and create a Chart. The procedure works fine.
    The one issue that I have is, if Excel Filename already exists, my code must over-write the file with no prompt.

    I have tried this syntax at the end of my Macro, but even the file does not exist it still asks for confirmation before over-writing.
    Please Login or Register  to view this content.
    What do I change to over-write the file?
    Also, I don't want to open open Task Manager to force quit all Excel Application and delete the existing excel file name

    Please any help is welcome.

  6. #6
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,887

    Re: MS Access 2016 ERROR 438 when exporting Query and creating Chart

    Please Login or Register  to view this content.
    should take care of existing files (assuming it's not open).

  7. #7
    Forum Contributor
    Join Date
    04-22-2014
    Location
    Turrialba, Costa Rica
    MS-Off Ver
    Excel 2010
    Posts
    108

    Re: MS Access 2016 ERROR 438 when exporting Query and creating Chart

    Hello

    I am using your lines of code at the end of my macro:
    Please Login or Register  to view this content.
    But even if the file does not exist VBA always displays "The filename already exists in this location. Do you want to overwrite it?".
    If i click yes VBA displays:

    Run-time error '70':
    Permission denied

    I think the problem is the at the beginning of may macro I have this lines of code:

    Please Login or Register  to view this content.
    Are these lines of code creating the conflict? If so how can I fix it?
    I really appreciate your help.

  8. #8
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,887

    Re: MS Access 2016 ERROR 438 when exporting Query and creating Chart

    No the problem is that those lines should go before the line that saves the workbook. After you save it, it is not surprising that there is a file there!

  9. #9
    Forum Contributor
    Join Date
    04-22-2014
    Location
    Turrialba, Costa Rica
    MS-Off Ver
    Excel 2010
    Posts
    108

    Re: MS Access 2016 ERROR 438 when exporting Query and creating Chart

    I tried as you said but VBA still displays ERROR 70.
    I am really confused.

  10. #10
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,887

    Re: MS Access 2016 ERROR 438 when exporting Query and creating Chart

    Please post the full code as it is now.

  11. #11
    Forum Contributor
    Join Date
    04-22-2014
    Location
    Turrialba, Costa Rica
    MS-Off Ver
    Excel 2010
    Posts
    108

    Re: MS Access 2016 ERROR 438 when exporting Query and creating Chart

    I really appreciate your reply.
    My macro works well the first time. However, when I run again VBA displays:
    Run-time error '1004':Method 'Columns' of object'_Global' failed"
    This ERROR adds yellow colour to this line of code: Columns("A:C").Select

    I have to tell you that I need to format the worksheet columns

    It is interesting If I Open Task Manager and stop Excel Application and delete the excel file already exported, the macro runs again
    At the end of my macro, you can see some lines of code as comments because those lines are generating the errors.

    Please Login or Register  to view this content.
    Thanks againg

  12. #12
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,887

    Re: MS Access 2016 ERROR 438 when exporting Query and creating Chart

    You have a whole load of new unqualified object references that you have introduced with your new code. When writing code in Access you can't simply use Excel objects like ActiveSheet, or Columns. You need to properly qualify them - so use ws instead of activesheet, and ws.Columns rather than just Columns, and avoid using Select and Selection like the plague.

    Also, I note your code appears to be late bound but you are trying to use enum values from Excel's object library (eg xlDown, xlContext etc), which won't work unless either you have a reference set (which makes late binding pointless) or you declare their values yourself.

  13. #13
    Forum Contributor
    Join Date
    04-22-2014
    Location
    Turrialba, Costa Rica
    MS-Off Ver
    Excel 2010
    Posts
    108

    Re: MS Access 2016 ERROR 438 when exporting Query and creating Chart

    I will do your instruction and I let you know the results.
    At the moment I have to study again early binding and late binding to see which is more efficient for my macro.
    Thanks, again.

  14. #14
    Forum Contributor
    Join Date
    04-22-2014
    Location
    Turrialba, Costa Rica
    MS-Off Ver
    Excel 2010
    Posts
    108

    Re: MS Access 2016 ERROR 438 when exporting Query and creating Chart

    rorya
    I am back.
    My code for the query export and chart creation now works fine.
    I decide to keep worksheet open to see query data and chart.
    Then, the user may save or close the workbook

    With your recommendation, I think all code is VBA for Access.

    Now, in the worksheet (from query), I am trying to open below the last row with data a Picture referenced to plot value.

    Thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 12-08-2017, 02:41 PM
  2. Error: exporting excel data to access
    By rupert.delfin in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-28-2015, 08:18 PM
  3. Exporting to Access-Error Message
    By markmerid in forum Excel General
    Replies: 0
    Last Post: 10-11-2006, 11:26 AM
  4. Exporting an Excel Chart into Access
    By T8RSP in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 03-01-2006, 10:13 AM
  5. Exporting to access but no error message
    By Matt in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-19-2005, 10:05 AM
  6. Replies: 0
    Last Post: 06-06-2005, 10:05 AM
  7. Error when exporting data into Access
    By Laurent M in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-18-2005, 05:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1