Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 39
  1. #16
    sjlevine34's Avatar
    sjlevine34 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Sep 2020
    Posts
    39
    You may be right, June7. Thanks for looking at it.

  2. #17
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I've tested your code on 2 workstations in both Access 365 (32/64 bit) & Access 2010 (32-bit)
    Same result in both cases
    When first run, the Excel file flashes in the taskbar (as you get). However when the same or a different Excel file is then opened, it takes focus successfully
    Reopening the Access file, causes the above behaviour again

    Adding the xlapp.ActiveWindow.Activate line has no effect on the above.

    Some of the code is I think superfluous but removing it hasn't changed the behaviour:

    Code:
    Public Function fOpenWorkbook(pstrPath As String) As Boolean'Dim lboolOpenWorkbook As Boolean
    
    
        Set xlapp = Excel.Application
        Set wbWorkbook = xlapp.Workbooks.Open(pstrPath)
        xlapp.Visible = True
        xlapp.WindowState = xlMaximized
        xlapp.ActiveWindow.Activate
        'lboolOpenWorkbook = True
        
    ExitOpenWorkbook:
        fOpenWorkbook = False 'lboolOpenWorkbook
        Exit Function
        
    ErrOpenWorkbook:
        MsgBox Err.Description, vbInformation, "Error in fOpenWorkbook"
        Resume ExitOpenWorkbook
        
    End Function
    If not already fixed, I'll look up my own code later today but it probably won't be for a few hours.
    In the meantime, did you try the SampleFileViewer app.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #18
    sjlevine34's Avatar
    sjlevine34 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Sep 2020
    Posts
    39
    I did try SampleFileViewer. Still playing with it.

    Several other observations with my app.


    • If, after opening the app but before attempting to open an excel file through it, I open the vba coding window and then close it, the excel file then displays on the screen like it should . I don't have to make any change to the code, just open and close the VBA screen.




    • Tried it on more orthodox databases, with no user-defined properties in the database, and also with a table as a source for the form. No change.It is though I need something to bring the excel window to the front after it opens.


    It seems as though I need something to bring the excel window to the front after it opens.

  4. #19
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    A couple of observations on your app from me.
    1. The error handling in the procedures I looked at wasn't functional. It should be something like:

    Code:
    Function FunctionName()
    
    On Error GoTo Err_Handler
    
    .... your code here
    
    Exit_Handler:
       Exit Function
    
    Err_Handler:
       MsgBox Err & " " & err.description & " in FunctionName procedure",vbExclamation, "Error"
       Resume Exit_Hand;er
    
    End Function
    You have omitted the On Error GoTo ... line so errors will not be properly hanled

    2. The code is unnecessarily complex and it may be that which causes the issue you have
    The file selection code could be dramatically simplified using FileSystemObject code
    The file open code would be much simpler if you used GetObject/CreateObject. Nothing else is needed

    3. I recommend you change to using late binding. Doing so means the version specific Excel reference can be removed

  5. #20
    sjlevine34's Avatar
    sjlevine34 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Sep 2020
    Posts
    39
    I intentionally left off the on error goto so that I could better debug the code.

    I will try your suggestions. They will allow me to eliminate the Office and Excel references.

  6. #21
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Omitting the OnError line MAY have helped you but not any of us testing it!

    Example late binding code for opening Excel file:

    Code:
        Dim objXL As Object
        Dim objWB As Object
    
        On Error Resume Next
        Set objXL = GetObject(,"Excel.Application")
        
         On Error GoTo 0
        If objXL Is Nothing Then
            Set objXL = CreateObject("Excel.Application")
            objXL.Visible =True
        End If
    
        Set objWB = objXL.Workbooks.Open(Me.txtFileName) 'replace with your textbox name from the form
    
    
    I suspect if you use that and FSO code instead of everything you have now, the problem will be solved. Good luck!

    NOTE: The code editor played havoc with what I pasted in, removing spaces from most lines - hopefully now restored!
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #22
    sjlevine34's Avatar
    sjlevine34 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Sep 2020
    Posts
    39
    How do I get a file dialog box using the FSO?

  8. #23
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  9. #24
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I still cannot replicate issue. I open db and click buttons on form, select Excel file, open Excel file - file is visible.

    You already have code using FSO.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #25
    sjlevine34's Avatar
    sjlevine34 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Sep 2020
    Posts
    39

    Eliminated references to Excel and Office Libraries

    I eliminated the references to Excel and Office libraries. I set fDialog = Application.FileDialog(msoFileDialogFilePicker) with msoFileDialogFilePicker defined as a constant = 3 within the module, so the only libraries in use are those that are present when a database is newly created. I also cleaned up some of the code.

    Still exhibits same behavior. I can live with it. However, if anyone runs across someone else complaining of this behavior, I would be interested.

    TestOpenExcelFileLateBinding.zip

  11. #26
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I was able to replicate the behaviour once so far. I was stepping through the code so I'm not sure if that is relevant. Will play around some more.

    EDIT - is there a reason why you have the workbook code in a standard module? If there is only going to be 1 call to the function per button click, then my first thought would be to keep it all in the click event and see if it makes a difference. There might be a timing issue between the calls in the stack - button click, then 'remote' function, then back to the click event.

    EDIT2 - by any chance does this only happen to you if you are working with the vbe (code editor) window i.e. stepping through the code?

  12. #27
    sjlevine34's Avatar
    sjlevine34 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Sep 2020
    Posts
    39
    The application that I posted was distilled out of two larger applications that I use daily in my work and in which I encountered the problem. The modules in the posted app were adapted from modules in those larger apps. I use mdlOfficeFileDialogMod in its original form in several of my apps that I have developed to assist me in my work. I try to arrange my vba modules so that I can reuse them as needed in different apps without creating conflicts. In fact, I just divided one of the modules into two in one of the aforementioned apps so that I can port one of them, which contains key functionality for the app, into another that I have used for some time, thus eliminating the need for the separate app. My goal is to eventually develop these apps into something that both I and my colleague can use in our work.

    The app that I posted basically was one I especially created for this discussion, only containing the essential functionality pertaining to the question asked. Throughout my career, when I have encountered problems with software, I have tried to develop test platforms where only the functionality in question is demonstrated. Besides ruling out other factors, it makes it simpler to analyze the problem, whether it be the result of a deficiency in the microcode on the computer, or a bug in the software used to run the established where I worked before I retired.

    The answer to your second question is that I encountered the problem when not working in the VBE. In fact, in my hands, opening and then closing the VBE prior to opening the file seems to solve the problem.

  13. #28
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Then I'm afraid I've run into a wall. Obviously we don't have the same environment variables such as version of Office/Access, network structure or permissions, hardware, etc. so it's probably not something we can pinpoint given that 3 (?) of us can run it with no issues. The only time I can come close is if I'm following code in the vbe. My last kick at the can would be to suggest an API to try to activate an application window, assuming your users can't just live with clicking on the task bar. Who knows, you might find that if you take that approach, the offending factor might be removed on some sort of update. An example of an API:

    https://www.everythingaccess.com/tut...the-foreground
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  14. #29
    sjlevine34's Avatar
    sjlevine34 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Sep 2020
    Posts
    39
    Since I am the only user, I'll live with it. Thanks for your help.

    Incidentally, I try to avoid APIs these days, since they have to be coded for both 32 bit and 64 bit systems. I know how to use them, having been through the exercise several years ago with an app I wrote that was used where I worked for over 10 years. I had to port it to Access 7 and have it work on both types of systems. The API calls are often different and take some research to find which ones are applicable to 64 bit systems. Right now, the only App I use with an API call is the one that lists a database's properties and it is coded for both (#IF WIN64 Then...#Else...#End If).

  15. #30
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    NP. Good luck going forward.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 6
    Last Post: 09-14-2017, 07:31 AM
  2. Replies: 34
    Last Post: 08-09-2017, 09:57 AM
  3. Replies: 5
    Last Post: 11-01-2016, 09:54 AM
  4. Replies: 2
    Last Post: 05-24-2016, 04:54 PM
  5. Replies: 1
    Last Post: 06-10-2012, 03:41 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
  •  
Other Forums: Microsoft Office Forums