来自Access VBA的Excel中的单元导航问题

Question

我有一个小项目，可以使用SQL查询从访问中获取数据并将数据写入Excel中。为此，我试图做以下操作。最终的输出是（6,5）中的" Hello"和（6,6）中的" Hello又一次"。"世界"迷失了。我几乎不确定如何正确导航。您在下面看到的大多数其他变量被声明为全局。

Sub enterDataInCell()
Dim Cell As Object
Set xlApp = CreateObject("Excel.Application")
xlApp.DisplayAlerts = False
Set wb = xlApp.Workbooks.Open("H:DocumentsMisc-WorkBUtest.xlsx")
Set ws = wb.Sheets(1)
irow = 6
icol = 5
Set Cell = xlApp.Workbooks(1).Activesheet.cells(irow, icol)
Cell.Value = "Hello"
Set Cell = xlApp.Workbooks(1).Activesheet.cells(irow, icol).offset(0, 1)
Cell.Value = "World"
Set Cell = xlApp.Workbooks(1).Activesheet.cells(irow, icol).offset(0, 1)
Cell.Value = "Hello Again"
wb.Save
wb.Close
xlApp.DisplayAlerts = True
Set wb = Nothing
Set xlApp = Nothing
Set Cell = Nothing
End Sub

Answer 1

，因为iCol的值没有更改（并且无论您使用多少次使用.Offset方法，都需要更改发送给.Offset方法的参数。<<<<<<<<<<<<<<<<<</p>

尝试：

Set Cell = xlApp.Workbooks(1).Activesheet.cells(irow, icol)
Cell.Value = "Hello"
Set Cell = xlApp.Workbooks(1).Activesheet.cells(irow, icol).offset(0, 1)
Cell.Value = "World"
Set Cell = xlApp.Workbooks(1).Activesheet.cells(irow, icol).offset(0, 2)
Cell.Value = "Hello Again"

或者您可以明确参考行/列位置，并完全避免.Offset：

Set Cell = xlApp.Workbooks(1).Activesheet.cells(irow, icol)
Cell.Value = "Hello"
Set Cell = xlApp.Workbooks(1).Activesheet.cells(irow, icol + 1)
Cell.Value = "World"
Set Cell = xlApp.Workbooks(1).Activesheet.cells(irow, icol + 2)
Cell.Value = "Hello Again"

另外，您可以定义Cell对象，然后使用Offset方法将其设置为新单元格。

Set Cell = xlApp.Workbooks(1).Activesheet.cells(irow, icol)
Cell.Value = "Hello"
Set Cell = Cell.offset(0, 1)
Cell.Value = "World"
Set Cell = Cell.offset(0, 1)
Cell.Value = "Hello Again"