Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419

    Access VBA open file run report however hidden


    This has always puzzle me and I have never solve this, Access VBA to open a excel file run program however the excel file is hidden.

    Meanwhile the program is running you open other excel file, however, it makes the program that is working on these file to be visible even though you hidden it.

    How do you resolve this that even if I open other excel file that the hidden excel file are still hidden ?

    Anyone have this issue or has resolve it ? Thx

  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
    Are you opening the second Excel file with code or manually?
    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
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    Actually I have two file open and close with code and is constantly open and close until end of file. The third or any other excel file is open manually.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Since it is the application that is hidden, try declaring and setting and controlling two sets of Excel object variables.
    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.

  5. #5
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    Quote Originally Posted by June7 View Post
    Since it is the application that is hidden, try declaring and setting and controlling two sets of Excel object variables.
    can you show me an example ?

    You meant ?

    wb0 = x0.workbooks.Add
    wb = x.workbooks.Add

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Are you using CreateObject code in each case so that you open two separate instances of Excel?
    GetObject should be used instead where you want to reuse the current instance of Excel.

    See https://docs.microsoft.com/en-us/pre...k08y5(v=vs.90)
    https://docs.microsoft.com/en-us/pre...28v%3dvs.90%29

    You can also combine these by using GetObject and trapping for error 429 if Excel isn't running
    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. #7
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    Thanks for the createobject and GetObject link and info. I tried it and it wasn’t effective and using the GetObject for existing files in the directory and createobject for new file not only gives me an error message when I run it but also I could still see file A after open new files while that file A was hidden.

    Could you should example that would work ?

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by johnseito View Post
    Thanks for the createobject and GetObject link and info. I tried it and it wasn’t effective and using the GetObject for existing files in the directory and createobject for new file not only gives me an error message when I run it but also I could still see file A after open new files while that file A was hidden.

    Could you should example that would work ?
    I don't have an example easily to hand for Excel.
    The following is the relevant part of code related to exporting to Outlook and the principle is the same

    Code:
    Public Sub ExportTimetableCalendar()
    
    On Error GoTo ErrorHandler
    
    'some code here ....
    
    'If Outlook open, bind to existing instance of Outlook
       Set objOL = GetObject(, "Outlook.Application")
      'If not, following code in error section is used to open it
      ' Set objOL = CreateObject("Outlook.Application")
      
    ... other code here
        
    
    ErrorHandlerExit:
       Exit Sub
     
    ErrorHandler:
        If Err.Number = 429 Then 'Outlook is not running; open Outlook with CreateObject
          Set objOL = CreateObject("Outlook.Application")
          Resume Next
        Else
            MsgBox "Error " & Err.Number & " in ExportTimetableCalendar routine: " & vbNewLine & " Description: " & Err.Description
            Resume ErrorHandlerExit
        End If     
       
    End Sub
    The idea is that the code first tries to use an existing instance of Outlook using GetObject.
    If Outlook isn't open, error 429 occurs but error handling then uses CreateObject to open a new instance of Outlook
    The Resume Next line returns the code to immediately after the error occurred and continues the rest of the procedure

    If you substitute Excel for Outlook it should run successfully in your procedure ... provided everything else is OK
    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

  9. #9
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    alright let me see again.
    What I am doing is different than your scenario. Don’t know if your example will apply.

    I am opening a new excel file.
    Example
    Set x = createobject (“Excel.Application”)
    Set wb = x.workbooks.add

    Set x2 = createobject(p + filename, false, false)

    Then I am opening existing excel file from a directory and copy what is there onto the new xls file I just open. I close and open and close until all xlsx files are copied and done.

    I am not checking to see if an excel file is already open and if not to use createobject, but if it is to use GetObject.

    I currently use create object to open the existing file and create a new file, however when the code is executing and someone opens an excel file, could be a new instance or what is in directory those file the code is working on becomes visible even though they are hidden.

    I’m trying to make it not be visible no matter what xlsx files are open.

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Is this what you want?

    Code:
    Sub MultiExcel()
    Dim xlx1 As Excel.Application, xlw1 As Excel.Workbook
    Dim xlx2 As Excel.Application, xlw2 As Excel.Workbook
    Dim xlx3 As Excel.Application, xlw3 As Excel.Workbook
    Set xlx1 = CreateObject("Excel.Application")
    Set xlx2 = CreateObject("Excel.Application")
    Set xlx3 = CreateObject("Excel.Application")
    Set xlw1 = xlx1.Workbooks.Open("filepath\namehere", , True)
    Set xlw2 = xlx2.Workbooks.Open("filepath\namehere", , True)
    Set xlw3 = xlx3.Workbooks.Add
    xlx2.Visible = True
    xlx3.Visible = True
    Debug.Print xlw1.Worksheets(1).Name
    xlx1.Quit
    End Sub
    You might want looping code that repeatedly sets and closes xlx1. I threw in a third set of variables just to expand example.
    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.

  11. #11
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    Close, but it does it in a loop.
    It opens the files in a directory and copy to the added workbook.
    But these are hidden, and because they are not visible, when another excel workbook is open or open from a directory how do
    you keep the ones the codes works on to stay hidden ?

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    In my example code xlx1 and xlw1 remain hidden, even when I remove the Quit line and manually open another instance of Excel.
    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.

  13. #13
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    Ok but that might be because the code is not running really long and you are only working on fewer files. It might not be that way if your code is running for say 10 or more mins and you are working with hundreds or more files.

  14. #14
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Maybe. Post your code.
    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.

  15. #15
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    Code:
    Dim x1, x2 as Excel.Application
    Dim wb, wb2 as Excel.Workbook
    Path = Application.CurrentProject.Path & "\"
    Set x1 = CreateObject("Excel.Application")
    Set x2 = CreateObject("Excel.Application")
    
    fx = "*xlsx"
    fr = Dir(P & fx)
    
    Set wb = x1.workbooks.open(Path + fx, false, false)
    set ws1 = wb.sheets(1)
    set wb2 = x2,workbooks.add
    set ws2 = wb2.sheets(1)
    
    Do 
    
         'copy open xlsx file in directory to new xlsx file
         'close the open file in directory after done copy
         wb.close save changes:= False 
         xl.Quit 
    
    Loop While (fr <> "")
    
    wb2.Saveas filename:=path & "\" test & ".xlsx"
    wb2.Close savechanges:=False
    x2.quite
    This is like the structural of my code that opens file from directory and do what it does
    and close and save it. However if is running for say 10 mins with huge number of files
    and I open a file in the directory (especially) or a new file, the file becomes visible even though
    I want it not visible.

Page 1 of 2 12 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: 2
    Last Post: 06-25-2014, 10:29 PM
  3. Replies: 1
    Last Post: 05-20-2013, 01:50 PM
  4. Replies: 1
    Last Post: 01-22-2013, 09:51 AM
  5. Replies: 1
    Last Post: 09-27-2010, 10:10 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