访问VBA -在Excel中向单元格添加编号

Question

我正在导出到Excel，我需要在单元格前面插入数字，这些单元格中都有数字。这是我的完整代码：

Private Sub cmdExport_Click()

Dim Results  As Recordset
Dim Numbering  As Integer

Dim FileName As String
Dim FilePath As String

Dim wb As Excel.Workbook
Dim XcelFile As Excel.Application

FileName = "TEST" & Format(Date, "dd/mm/yyyy") & ".xlsx"

FilePath = CurrentProject.Path & "\" & FileName

 Set XcelFile = New Excel.Application

 Set wb = XcelFile.Workbooks.Add

 Set Results  = Forms![MyForm].Form.RecordsetClone

   With wb

      XcelFile.ScreenUpdating = False

      For Numbering = 0 To Results.Fields.Count - 1
      XcelFile.Cells(1, Numbering + 1).Value = Results.Fields(Numbering).Name
      Next Numbering

      Results.MoveFirst
      XcelFile.Range("A2").CopyFromRecordset Results

     For Each cell In XcelFile.Range("A:A")
     cell.Value = "100" & cell.Value
     Next cell

      .SaveAs FileName:=FilePath, FileFormat:=51
      XcelFile.ScreenUpdating = True

   End With

  wb.Close
  Set XcelFile = Nothing

  End Sub

如你所见，我试过这样做：

For Each cell In XcelFile.Range("A:A")
         cell.Value = "100" & cell.Value
         Next cell

但不幸的是，什么都没发生。我怎么能解决这个问题？

Answer 1

For Each cell In XcelFile.Range("A:A")

应该是

Dim sh As Excel.Worksheet
Set sh = wb.ActiveSheet
For Each cell In sh.Range("A:A")

我觉得。

这适用于您现在拥有XcelFile.Cells的所有其他地方，请将它们更改为sh.Cells。

注意：

Dim XcelFile As Excel.Application

真是个让人困惑的名字，IMHO。