Is it possible to transfer data from ms access to excel using vba code?

[wedd]

Hi everyone, I wanted to know if it is possible to use vba code or a method for transfering data from ms access into ms excel? If so, how? And would you have examples of code to do this? This would be very helpful towards my mastery of understanding vba coding.lol


Thanks for your contributions

[L@ja]

Hi wedd,
there are some of way to transfer data to xls...
1,
[vba]DoCmd.OutputTo
[/vba]

2, write via excel app
[vba]
Set xl = CreateObject("Excel.Application")
Set wb = xl.workbooks.Add

'write headers
'osz=column
For osz = 0 To actrs.Fields.Count - 1
xl.activesheet.Cells(1, osz + 2) = actrs.Fields(osz).Name
xl.activesheet.Cells(1, osz + 2).Interior.ColorIndex = 15
If actrs.Fields(osz).Type = 135 Then
xl.activesheet.Cells(1, osz + 2).EntireColumn.numberformat = "yyyy/mm/dd;@"
End If
Next osz

'actrs=recordset
'sor=rowID

For sor = 2 To actrs.RecordCount + 1
For osz = 0 To actrs.Fields.Count - 1
If Not IsNull(actrs(osz)) Then
If Not Len(actrs(osz)) > 1023 Then
xl.activesheet.Cells(sor, osz + 2) = actrs(osz)
Else
xl.activesheet.Cells(sor, osz + 2) = Left(actrs(osz), 1023)
End If
End If
Next osz

actrs.MoveNext

Next sor
wb.activesheet.Cells.EntireColumn.AutoFit
'saveas2 tmp
fpaccs = Environ("TMP") & "\anythingname.xls"
xl.application.displayalerts = False
X = wb.SaveAs(fpaccs, True)
xl.application.displayalerts = True
wb.Close
Set xl = Nothing
[/vba]

[CreganTur]

L@ja's example will work for you, but it's really overkill when you have the TransferSpreadsheet method built into VBA. Just use VBA help to get all the details on this.