Access to Excel by VBA 1

[kennetha]

Hi all,

I would like to transfer an Access query to a workbook template in Excel.

Some VBA code appriciated.

Thanks in advance
Kenneth

 

[PHV]

Have a look to the DoCmd.TransferSpreadsheet method.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[matrixindicator]

Dim xlExcel As Excel.Application
   Dim xlWorkBook As Excel.Workbook
   Dim xlWorkSheet As Excel.Worksheet

   Dim dbs As DAO.Database
   Dim rst As DAO.Recordset

   dim strUrl As String
   strURL = "C:\exceltempl.xls"
   'Create the Excel Application, Workbook and Worksheet
   Set xlExcel = Excel.Application
   Set xlWorkBook = xlExcel.Workbooks.Open(strURL)

    Set dbs = CurrentDb()
    Set rst = dbs.OpenRecordset("query", dbOpenDynaset)

    rst.MoveFirst
    iRow = 1
    Do Until rst.EOF
        For iCol = 1 To (rst.Fields.Count)
            xlWorkSheet.Cells(iRow, iCol + 1) = rst.Fields
         Next
         iRow = iRow + 1
    rst.MoveNext
    Loop

code works in my application, however these parts of the total code could works but are not tested, modification required and adaptation to your situation and requirements.

 

[kennetha]

Thank you.

 

[PHV]

A quicker way is to use the CopyFromRecordset method of the Excel.Range object.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886