Excel VBA - 我可以操纵另一个工作簿上的数据吗？ [英] Excel VBA - Can I manipulate data on another workbook?

问题描述

每个月我都需要运行一个报告。但是，在我可以这样做之前，我必须将一些数据导出到excel中。一旦数据在excel中，我必须修改一些列，并将其格式正确，然后再附加到另一个文档的末尾进行一些分析。

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.

我想拥有我附加数据的文档以打开。然后，我会将数据从我的程序导出到excel（excel只是打开数据，并且不保存在任何地方），而从我的较大的文档中，运行一个VBA脚本来改变另一个工作簿（Book1）上的数据，以便以正确的格式可以将其复制到分析文档中。

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.

到目前为止，我想要做的是将所有单元格设置到正确的高度，以便于阅读。但是，当我运行这段代码时，我得到：

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:

运行时错误'9'：

Run-time error '9':

下标超出范围

Subscript out of range

代码我到目前为止：

Sub Data_Ready_For_Transfer()

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

它似乎与与工作簿（Book1.xlsm）有问题。工作表（Sheet1）部分代码。我也尝试过使用工作簿（Book1）。工作表（Sheet1），我已尝试使用打开的未保存的文档和工作簿的保存版本。

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?

推荐答案

book1 已在应用程序对象的另一个实例中打开。

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

还有一个问题，我已经将 Cells.RowHeight = 15 .Cells.RowHeight = 15 指定单元格属于工作簿 Book1 sheet Sheet1 。

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

这篇关于Excel VBA - 我可以操纵另一个工作簿上的数据吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！