Page 1 of 3 123 LastLast
Results 1 to 15 of 39
  1. #1
    sjlevine34's Avatar
    sjlevine34 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Sep 2020
    Posts
    39

    Opening Excel File in Access does not bring up file on screen

    I am using the following code to open and view an Excel file through MS Access:
    Code:
    Public Function fOpenWorkbook(pstrPath As String) As BooleanDim lboolOpenWorkbook As Boolean
    
    
        Set xlApp = excel.Application
        Set wbWorkbook = xlApp.Workbooks.Open(pstrPath)
        xlApp.Visible = True
        xlApp.WindowState = xlMaximized
    
    
        lboolOpenWorkbook = True
        
    ExitOpenWorkbook:
        fOpenWorkbook = lboolOpenWorkbook
        Exit Function
        
    ErrOpenWorkbook:
        MsgBox Err.Description, vbInformation, "Error in fOpenWorkbook"
        Resume ExitOpenWorkbook
        
    End Function
    When I execute this function on a file that has not been previous opened in the access session, the excel icon flashes orange on the taskbar (see attachment).
    Click image for larger version. 

Name:	Results of opening excel file.jpg 
Views:	31 
Size:	140.5 KB 
ID:	43167
    I have to click on the flashing icon to bring up the file, which it does normally.



    Is there a way to do this that automatically brings up the file, like it does if the file has previously been opened in the access session? (I can't share the file because it has sensitive information in it).
    Last edited by sjlevine34; 10-09-2020 at 06:15 PM. Reason: Wanted to change attachment

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Tested your code and it works for me.

    Doesn't matter if Excel file has never been opened, it is visible.
    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.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    maybe for some reason (e.g. application version) you need to activate the app window first.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    sjlevine34's Avatar
    sjlevine34 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Sep 2020
    Posts
    39
    How do I activate the app window?

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by sjlevine34 View Post
    How do I activate the app window?
    Well I'm not really sure because like June7 seems to be saying, I haven't had this issue. See if this helps, and note the Methods link near the bottom of the page. After reading that, I might simply try activating the sheet, which I don't see you referencing. I'm assuming this workbook does contain at least one sheet and there is no code in the workbook that runs when it opens and makes it minimized.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    sjlevine34's Avatar
    sjlevine34 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Sep 2020
    Posts
    39
    Tried the following code changes, which included making the worksheet in the workbook visible and activating the active window. No change in behavior.

    Code:
    Public Function fOpenWorkbook(pstrPath As String) As BooleanDim lboolOpenWorkbook As Boolean
    
    
        Set xlapp = Excel.Application
        Set wbWorkbook = xlapp.Workbooks.Open(pstrPath)
        xlapp.Visible = True
        xlapp.WindowState = xlMaximized
        wbWorkbook.Worksheets(1).Visible = True
        xlapp.ActiveWindow.Activate
         
        lboolOpenWorkbook = True
        
    ExitOpenWorkbook:
        fOpenWorkbook = lboolOpenWorkbook
        Exit Function
        
    ErrOpenWorkbook:
        MsgBox Err.Description, vbInformation, "Error in fOpenWorkbook"
        Resume ExitOpenWorkbook
        
    End Function

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I think you would want that addition before xlMaximized, otherwise you're trying to maximize first then activate.
    Maybe you'd have more luck with the GetObject or CreateObject method - it has always worked for me.

    If that doesn't help, then I can only suggest to test if it might be a timing issue for you. You can call a little pause function in between steps to allow other things to happen, such as opening an application or making something active and see if it helps.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    I would say that works fine for me as well, so thank you for that, in case I ever need it. (y)

  9. #9
    sjlevine34's Avatar
    sjlevine34 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Sep 2020
    Posts
    39
    I tried the following code. No change in behavior:

    Code:
    Public Function fOpenWorkbook(pstrPath As String) As Boolean
    Dim lboolOpenWorkbook As Boolean
    
    
        'Set xlapp = Excel.Application
        On Error Resume Next
        Set xlapp = GetObject(, "Excel.Application")
        If Err.Number <> 0 Then
            Set xlapp = CreateObject("Excel.Application")
        End If
        Err.Clear
        On Error GoTo ErrOpenWorkbook
        
        Set wbWorkbook = xlapp.Workbooks.Open(pstrPath)
        xlapp.Visible = True
        sSleep 1
        wbWorkbook.Worksheets(1).Visible = True
        xlapp.ActiveWindow.Activate
        xlapp.WindowState = xlMaximized
             
        lboolOpenWorkbook = True
        
    ExitOpenWorkbook:
        fOpenWorkbook = lboolOpenWorkbook
        Exit Function
        
    ErrOpenWorkbook:
        MsgBox Err.Description, vbInformation, "Error in fOpenWorkbook"
        Resume ExitOpenWorkbook
        
    End Function
    Code:
    Option Compare Database
    Option Explicit
    
    
    Private dbCurrent As DAO.Database
    
    
    Private vitem As Property
    Private insert_statement, fld_val, psource, errmess
    
    
    #If Win64 Then
        Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)
    #Else
        Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
    #End If
    
    Public Sub sSleep(pintSeconds As Integer)
    
    
        Sleep pintSeconds * 1000
    End Sub
    Do you all mind if I try to post this question on some other sites?

  10. #10
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    There is no issue with cross-posting providing you supply a link to the post at each of the sites.

    However, before you do so, have a look at the attached example app which allows you to open an excel file within Access using a web browser control.

    For example:

    Click image for larger version. 

Name:	ExcelFileInAccess.jpg 
Views:	20 
Size:	164.2 KB 
ID:	43169
    Attached Files Attached Files
    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

  11. #11
    sjlevine34's Avatar
    sjlevine34 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Sep 2020
    Posts
    39
    I went ahead and posted the question on stack overflow to see what they have to say https://stackoverflow.com/questions/...file-on-screen. Meanwhile, I am going to play with using the web browser control in a form.

  12. #12
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Quote Originally Posted by sjlevine34 View Post
    I went ahead and posted the question on stack overflow to see what they have to say https://stackoverflow.com/questions/...file-on-screen. Meanwhile, I am going to play with using the web browser control in a form.
    It would be polite to advise them that your question had been cross posted here?, possibly with a link to this thread?

  13. #13
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If you can post a zipped db copy here we could test your build rather than just reproducing your code in our own Access versions. Who knows, maybe something would turn up.
    I have to commend you for asking about posting elsewhere - that sort of consideration is very rare and all too lacking.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

    Zipped database attached

    Here is a zipped copy of a test database that exhibits the same above behavior on both of my laptops running Office 365 with both Access and Excel 365, and windows 10. I appreciate your help.

    TestOpenExcelFile.zip

  15. #15
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    After fixing Excel reference library for 2010, ran your code. Excel opens visible. So issue must be with Office 365.
    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.

Page 1 of 3 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