Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2014
    Posts
    10

    Unanswered: Need to manipulate already opened Workbook from Access

    Can't believe I'm stuck on this!

    I want set focus to an open workbook and then do stuff with it.
    Basically:

    Code:
    Workbooks("wb.xls").Activate  'This returns run time error 9, subscript out of range
    'Code here for editing the workbook
    What gives?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    First check that "wb.xls" actually is among the wokbooks. You can enumerate them using:
    Code:
    Dim wb As Excel.Workbook
    For Each wb In XLApp.Workbooks
        Debug.Print wb.Name
    Next wb
    Where XLApp is the variable instance of the Excel application open in Access.
    Have a nice day!

  3. #3
    Join Date
    Jun 2014
    Posts
    10
    Hi Sinndho,

    If I create a sub with the following code in Excel, then the workbook names are printed in the Immediate window. However, if I run the code then Access - nothing is printed.
    Code:
    'Dim xlApp As Object
    Set xlApp = Excel.Application
    Dim wb As Excel.Workbook
    For Each wb In xlApp.Workbooks
        Debug.Print wb.Name
    Next wb

    I'm afraid I'm still stuck on how to catch a certain workbook and from there edit the workbook.

    Through previous code I've already established what workbook to proceed with. Check out this thread where I got help listing the open workbooks in a listbox.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You cannot have access to the workbooks open in another instance of Excel than the one you created in your code, except by reopening the same workbook.
    Have a nice day!

  5. #5
    Join Date
    Jun 2014
    Posts
    10
    I'm close to giving up.

    What would the code be for:

    Activating a desired workbook (name of the workbook is pretermined as a string)
    Selecting cell A1 of the workbook

    ?

    I'm starting to think that this is not possible.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It's possible if you open the workbook in an instance of Excel created in Access:
    Code:
    Sub ToExcel()
    
        Const ExcelPath As String = "U:\Access\test.xls"
        Dim Benchmarks As DAO.Recordset
        Set Benchmarks = CurrentDb.OpenRecordset("Tbl_Orders")
    
        Dim XLBook As Excel.Workbook
        Dim Xl As Excel.Application
        Dim XLsheet As Excel.Worksheet
    
        Set Xl = CreateObject("Excel.Application")
        Set XLBook = Xl.Workbooks.Open(ExcelPath)
        Set XLsheet = XLBook.Sheets("User_Input")
        XLsheet.Range("AX2").Select
        Xl.Selection.CopyFromRecordset Benchmarks
    
    End Sub
    Have a nice day!

  7. #7
    Join Date
    Jun 2014
    Posts
    10
    Thank you for your guidance Sinndho.

    Your code worked, however...
    I should have mentioned that the workbook I'm trying to manipulate is not saved, so there is no existing file path. The excel file in question is generated through a website upon the user's request.

    Maybe I need to look into importing the workbook into a temporary table and then pull everything from there.

Posting Permissions

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