After exporting to Excel I want to have distinct release numbers as rows starting from A2 and distinct test case name as columns starting from B1. There might be couple thousands records. Then each cell will be set to result tag. Additionally will need some fancy coloring/bordering stuff.
The question - is it possible to do this using VBA in Access and if yes what is the way to go? Any hint, sample, example, resource would be appreciated. I've googled but the most thing I came accross is DoCmd.TransferSpreadsheet or DoCmd.OutputTo which I believe will not do what I want. Saw some examples with CreateObject("Excel.Application") but not sure what are limitations and performance using this way.
asked Apr 15, 2011 at 16:18 29.2k 36 36 gold badges 131 131 silver badges 222 222 bronze badgesI don't know if it would work for your case, but you might try adding the VBA code to an Excel document rather than the Access database. Then you could refresh the data from the Excel file and add the formatting there much easier. Here is one example:
Again, it may not work for your case, but it may be an option to consider. Essentially, instead of pushing from Access, you would pull from Excel.
answered Apr 15, 2011 at 16:30 8,987 6 6 gold badges 39 39 silver badges 68 68 bronze badgesIn my case it will not be the best solution unfortunately. but nice approach, will keep in mind for future. thx!
Commented Apr 15, 2011 at 16:35Yes, there are many cases when the DoCmd.TransferSpreadsheet command is inadaquate.
The easiest way is to reference the Excel xx.x Object model within Access (Early Binding). Create and test your vba export function that way. Then once you are satisfied with your output, remove the Excel object model reference, then change your objects to use use Late Binding using CreateObject. This allows you to easily have other machines that are using different versions of Excel/Access to use it just the same.
Here is a quick example:
Sub ExportRecordsetToExcel(outputPath As String, rs As ADODB.Recordset) 'exports the past due report in correct formattig to the specified path On Error GoTo handler: Const xlUP As Long = -4162 'excel constants if used need to be referenced manually! Dim oExcel As Object Dim oBook As Object Dim oSheet As Object Dim row As Long If rs.BOF And rs.EOF Then Exit Sub 'no data to write Else rs.MoveFirst End If row = 1 Set oExcel = CreateObject("Excel.Application") oExcel.Visible = False 'toggle for debugging Set oBook = oExcel.Workbooks.Add 'default workbook has 3 sheets 'Add data to cells of the first worksheet in the new workbook. Set oSheet = oBook.worksheets(1) Do While rs.EOF = False oSheet.range("A" & row).value = rs.Fields("MyField").value 'increase row row = row + 1 Loop oBook.SaveAs (outputPath) 'tidy up, dont leave open excel process Set oSheet = Nothing Set oBook = Nothing oExcel.Quit Set oExcel = Nothing Exit Sub handler: 'clean up all objects to not leave hanging processes End Sub