Page 2 of 2 FirstFirst 12
Results 16 to 23 of 23
  1. #16
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    wondering if making the workbook the active object would have been the same effect as making it visible, as in
    wkbk.activate
    wkbk.Application.Run "test"



    It would already be activated if running it from Excel, though it doesn't explain June7's experience. Just thought I'd throw it out there since you said you'd rather not make it visible.
    Last edited by Micron; 12-10-2018 at 09:06 PM. Reason: clarification and correction
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  2. #17
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Hi Micron,

    This is what I currently have:

    Code:
        xlObj.Visible = True
        xlObj.worksheets("PasteSpecial").Activate
        xlObj.Range("i1").Value = sNewFile
        wkbk.Application.Run "BetterExcelDataToWord"
        wkbk.Close SaveChanges:=True
        xlObj.Quit
    is active different from activate?

    this process has so much code that its difficult to get a stable version. When I fix one thing something else goes wrong. But I'm getting there. Ill do some more work on it today and then if I get it to a working state ill do a backup and try your suggestion.

    Thanks a lot.

  3. #18
    Sanguineus is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Dec 2018
    Posts
    19
    Hi not sure if this will help, but I do something similar but will a few subtle changes.

    I do all of any workbook manipulation from the excel code modules (generally speaking though you shouldn't actually need to ever select or activate a range or workbook).

    I would pass any variables from access to excel and allow excel to do the heavy lifting by doing this.

    IN EXCEL
    Code:
    Sub Test (WorksheetName as string, FileName as string)
        debug.print worksheetname 
        debug.print filename
    end sub
    IN ACCESS
    Code:
    Sub ExcelCodeRunTest
        dim xl object
        set xl = createobject("Excel.Application")
        
       xl.run "Test", "TestWorksheetName", "TestFileName"
    End Sub

  4. #19
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    .Activate would make something active, provided the Activate method applies to the object in question. Without seeing your code, impossible to say if it's worthy of consideration. If the manipulation of a workbook is all done in a With ... End With block, then I agree, should not be necessary. However, your code might be causing it to become inactive which was the only possibility I could think of at the time, and that was because you said making it visible fixed the problem. You could post the full code along with identifying where any errors/issues occur, or post the db and a workbook.

  5. #20
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Quote Originally Posted by Sanguineus View Post
    Hi not sure if this will help, but I do something similar but will a few subtle changes.

    I do all of any workbook manipulation from the excel code modules (generally speaking though you shouldn't actually need to ever select or activate a range or workbook).

    I would pass any variables from access to excel and allow excel to do the heavy lifting by doing this.

    IN EXCEL
    Code:
    Sub Test (WorksheetName as string, FileName as string)
        debug.print worksheetname 
        debug.print filename
    end sub

    IN ACCESS
    Code:
    Sub ExcelCodeRunTest
        dim xl object
        set xl = createobject("Excel.Application")
        
       xl.run "Test", "TestWorksheetName", "TestFileName"
    End Sub
    thats pretty much how it works. The file name generated in access is pasted into a cell in excel that is used for the other code in excel.

  6. #21
    Sanguineus is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Dec 2018
    Posts
    19
    Have you tried using the basic code I posted in a dummy DB and Workbook?

    if it works in the dummy, it should then work in your production versions.

  7. #22
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Quote Originally Posted by Sanguineus View Post
    Have you tried using the basic code I posted in a dummy DB and Workbook?

    if it works in the dummy, it should then work in your production versions.

    The code is working as before. Its just a visability issue now (having the excel visable means there is potential for the user to interupt the process). I'll try today to resolve this. But as it stands now everything is working as it should be.

  8. #23
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    All working fine now with visability set to false.
    There does seem to be instances of excel still in task manager after this process (this is the same if visable or not).

    But these instances are not interupting the next time I run the code. Ive tested it many times and all seems good.

    Code:
     xlObj.Visible = False
        xlObj.worksheets("PasteSpecial").Activate
        xlObj.Range("i1").Value = sNewFile
        wkbk.Application.Run "BetterExcelDataToWord"
        wkbk.Close SaveChanges:=True
        xlObj.Quit
    thanks everyone for the help.

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

Similar Threads

  1. automation error message
    By Jen0dorf in forum Access
    Replies: 11
    Last Post: 12-10-2015, 11:39 AM
  2. Automation Error from form
    By gemadan96 in forum Forms
    Replies: 5
    Last Post: 05-21-2014, 12:27 PM
  3. IE Automation Error
    By bucko_oz in forum Programming
    Replies: 1
    Last Post: 09-19-2010, 11:28 PM
  4. automation error
    By ashiers in forum Forms
    Replies: 0
    Last Post: 04-16-2009, 11:38 AM
  5. Automation Error
    By aouellette in forum Forms
    Replies: 0
    Last Post: 09-12-2008, 08:00 AM

Posting Permissions

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