Hello,
I am trying to export a table of records into Excel. I have some code already but it's only importing the 1st row. Not sure why.
Code:
Sub GenerateOutliers_Click()
Dim MyDb As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim myExcel As Object
Dim myWb As Object
Dim myWs As Object
Dim rng As Range
Dim rowsToReturn As Integer
Dim iCol As Integer
strSQL = "SELECT * FROM tbl_Outliers"
Set rst = CurrentDb.OpenRecordset(strSQL)
rowsToReturn = rst.RecordCount
Set myExcel = CreateObject("Excel.Application")
Set myWb = myExcel.workbooks.Open(fileOutliers)
myExcel.Visible = True
Set myWs = myWb.worksheets("Outliers")
For iCol = 0 To rst.Fields.Count - 1
myWs.Cells(1, iCol + 1).Value = rst.Fields(iCol).Name
Next
Set rng = myWs.Cells(2, 1)
rng.CopyFromRecordset rst, rowsToReturn
rst.Close
Set rst = Nothing
Set myWs = Nothing
Set myWb = Nothing
End Sub
Thank you for your assistance