时间戳

首页 > 解决方案 > 声明工作簿变量

excel - 声明工作簿变量

问题描述

VBA 正在返回一个

运行时 13 错误

当我将工作簿声明为变量然后引用变量来复制数据时

Sub CopyPnLData()

Dim PnLMonth As Workbook
Dim FilePath1 As String
FilePath1 = "C:\Users\mturkenburg\WHISPIR\Gareth Edlin - FP&A\Anaplan\P&L\Monthly Downloads\1.1 P&L OPEX Buckets - Month.CSV"
Workbooks.Open (FilePath1)
Set PnLMonth = Application.Workbooks("1.1 P&L OPEX Buckets - Month.CSV")

Workbooks(PnLMonth).Worksheets("Sheet 1").Range("A1").CurrentRegion.Copy
ThisWorkbook.Worksheets("1.AP Data - P&L").Range("c1").Paste

End Sub

标签： excelvba

解决方案

我已经修改了您的代码并添加了一些注释供您参考。

Sub CopyPnLData()
  Dim PnLMonth As WorkBook
  Dim FilePath1 As String

  FilePath1 = "C:\Users\mturkenburg\WHISPIR\Gareth Edlin - FP&A\Anaplan\P&L\Monthly Downloads\1.1 P&L OPEX Buckets - Month.CSV"

  ' check the variable first
  If Dir(FilePath1) = "" Then
    MsgBox "File not found. : " & FilePath1
    Exit Sub
  End If

  ' set your workbook variable when you open it like this
  Set PnLMonth = Workbooks.Open(FilePath1)

  ' use your workbook variable in code like this
  PnLMonth.Worksheets("Sheet 1").Range("A1").CurrentRegion.Copy

  ' not sure if this line is going to work, as you did not provide details about it
  ThisWorkbook.Worksheets("1.AP Data - P&L").Range("c1").Paste

  ' close it when you are done
  PnLMonth.Close False

End Sub

推荐阅读