Excel VBA - Can I manipulate data on another workb

Question

Background:

Every month I need to run a report. However, before I can do this, I must export some data in to excel. Once the data is in excel, I have to alter some columns and get the format correct before it is appended to the end of another document to do some analysis on.

What I would like:

I would like to have the document that I append the data to open. I will then export the data in to excel from my program (excel just opens with the data and it is not saved anywhere) and from my larger document, run a VBA script that will alter the data on the other workbook (Book1) so that it can be copied over to the analysis document when in the correct format.

What I have so far:

I have started basic. So far all I am trying to do is set all the cells to the correct height to make it easier to read. However, when I run this code, I get:

Run-time error '9':

Subscript out of range

The code I have so far is:

Sub Data_Ready_For_Transfer()

' Format all cell heights to 15
    With Workbooks("Book1.xlsm").Worksheets("Sheet1")
        Cells.RowHeight = 15
    End With
End Sub

It appears to be having issues with the With Workbooks("Book1.xlsm").Worksheets("Sheet1") part of the code. I have also tried With Workbooks("Book1").Worksheets("Sheet1") and I have tried this with the open, unsaved document and a saved version of the workbook.

Am I missing something obvious?

Answer 1

As follow up from comments, workbook Book1 was opened in another instance of Application object.

In that case this code should work (for already opened workbook):

Sub Data_Ready_For_Transfer()
    Dim wb As Workbook

    Set wb = GetObject("Book1")

    'if we get workbook instance then
    If Not wb Is Nothing Then
        With wb.Worksheets("Sheet1")
            .Cells.RowHeight = 15
        End With
    End If
End Sub

One more issue, I've changed Cells.RowHeight = 15 to .Cells.RowHeight = 15 to specify, that Cells belongs to workbook Book1 sheet Sheet1.