范围类的数据系列方法失败

Question

我正在尝试运行一个执行的 VB 脚本

1. 向左添加新列(位置 A(
2. 输入 A1 的值作为"1"。
3. 为所有可用行(在 B 列中(以 1 递增的方式填充序列。

实际代码：

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWB = objExcel.Workbooks.Open("XLSPATH")
Set objSheet = objwb.Sheets("SheetName")
objSheet.Columns("A:A").Insert xlToRight
objSheet.Cells(1, 1).Value = "Record Number"
objSheet.Cells(2, 1).Value = 1
Set Range = objSheet.Range("A2:A"&objSheet.UsedRange.Rows.Count) 
Range.DataSeries xlColumns, xlLinear, xlDay, 1, False
objWB.Close True
objExcel.Quit

我的脚本在此命令中失败，引发错误代码"800A03EC">

Range.DataSeries xlColumns, xlLinear, xlDay, 1, False

但是通过宏尝试的类似操作工作正常。

Range("A2:A10").Select
Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
Step:=1, Trend:=False**

Answer 1

Range是VBA中的保留字。请为变量使用其他名称。看起来您正在使用 LateBinding。您是否在代码顶部声明了常量？试试这段代码(未经测试(

Option Explicit
Const xlUp As Integer = -4162 
Const xlToRight As Integer = -4161     
Const xlColumns As Integer = 2
Const xlLinear As Integer = -4132
Const xlDay As Integer = 1
Sub Sample()
Dim objExcel As Object
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Dim objWB As Object
Set objWB = objExcel.Workbooks.Open("XLSPATH")
Dim objSheet As Object
Set objSheet = objWB.Sheets("SheetName")
objSheet.Columns("A:A").Insert xlToRight
objSheet.Cells(1, 1).Value = "Record Number"
objSheet.Cells(2, 1).Value = 1
'~~> Find Last Row in Col A
Dim lRow As Long
lRow = objSheet.Range("A" & objSheet.Rows.Count).End(xlUp).Row
Dim rng As Object
Set rng = objSheet.Range("A2:A" & lRow)
rng.DataSeries xlColumns, xlLinear, xlDay, 1, False
objWB.Close True
objExcel.Quit
End Sub