Consulting

Results 1 to 2 of 2

Thread: Error making workbook visible : Subscript out of range

  1. #1
    VBAX Regular
    Joined
    May 2015
    Posts
    87
    Location

    Error making workbook visible : Subscript out of range

    I have another issue with code that worked fine when it was .adp but now that this is am .accdb database in Access 2013 I get an error trying to make the workbook visible on this line

    objXLBook.Windows(1).Visible = True
    When I open the file OpenIssuesReport.xlsx there is a single worksheet titled OpenIssue3 at the bottom, so I know the data transfer worked and the location of the workbook is correct.

    Here is the complete code up until the line that errors out:

    Private Sub OpenIssues_Click()
    Dim strFilePath      As String
    Dim objXLApp         As Excel.Application
    Dim objXLBook        As Excel.Workbook
    Dim objXLSheet       As Excel.Worksheet
    Dim LastRow          As Long
    Dim i                As Integer
     
    'Export the open issues query results
    DoCmd.OutputTo acOutputQuery, "OpenIssue3", acFormatXLSX, "I:\Groups\ccuser\Finance\Collection Control\Open Issues\OpenIssuesReport.xlsx", False, "", , acExportQualityPrint
    
    'Sets strFilePath equal to the location of the excel file
    strFilePath = "I:\Groups\ccuser\Finance\Collection Control\Open Issues\OpenIssuesReport.xlsx"
    
    'Opens Excel
    Set objXLApp = CreateObject("Excel.Application")
    
    'Opens Workbook
    Set objXLBook = GetObject(strFilePath)
    
    'Opens the sheet
    Set objXLSheet = objXLBook.Worksheets(1)
    
    'for testing, make the application visible
    objXLApp.Visible = True
    objXLBook.Windows(1).Visible = True

  2. #2
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    This works for me
    Dim AppExcel As New Excel.Application
    Dim Wkb As Workbook
    Set Wkb = AppExcel.Workbooks.Open("C:\Excel\References.xls")
    AppExcel.Visible = True

Posting Permissions

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