QueryTables on non-ActiveSheet range

I've been putting `QueryTables` results in the `ActiveSheet`, but now I want to put them off to the side in a different sheet. In the following code the commented out line is what I had originally, the following "with" line is my new attempt. Dim QuerySheet As WorkSheet Dim ttt As Integer Set QuerySheet = ActiveWorkbook.Worksheets("ZZ$Temp$") QuerySheet.Cells.Clear QuerySheet.Cells(1, 1) = 1234 QuerySheet.Cells(2000, 50) = 5678 ttt = QuerySheet.Cells(1, 1) ' With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=DataSheet.Range(Cells(row_stock + iMax, 27), Cells(row_stock + iMax + 199, 27))) With QuerySheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=QuerySheet.Range(Cells(row_stock + iMax, 27), Cells(row_stock + iMax + 199, 27))) .BackgroundQuery = True .TablesOnlyFromHTML = False .Refresh BackgroundQuery:=False .SaveData = True End With I know the sheet exists and I define contents at `1, 1` and `2000, 50`. I get the error `Run-time error '1004' Method 'Range of object '_worksheet' failed.` I also tried activating `QuerySheet` and with the original `With`. How do I get `QueryTables` to put stuff in `QuerySheet`?

以上就是QueryTables on non-ActiveSheet range的详细内容,更多请关注web前端其它相关文章!

赞(0) 打赏
未经允许不得转载:web前端首页 » excel

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

前端开发相关广告投放 更专业 更精准