View Full Version : Is it possible to transfer data from ms access to excel using vba code?
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:friends:
Hi wedd,
there are some of way to transfer data to xls...
1,
DoCmd.OutputTo
2, write via excel app
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
CreganTur
02-01-2011, 06:44 AM
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.