Hi --
I've been banging my head up against this all morning. I feel like I'm really close to cracking it, but just can't get the last step.
I've managed to cobble together the code below. It exports the results of my query to an Excel workbook and plants it on my desktop. So far, so good. I've managed to open the file once it's created. I've even managed to set the row height for my results.
This is where I hit the wall. I want to set the vertical alignment of the rows to Top. I initially tried
but had no luck. I tried
and then a With Selection, but that wouldn't fly either.
I've since read something about not being able to use xl* constants with Late Binding, which is evidently what I've done here. What I think I understand is that I now need to define a specific constant within the Sub () that will stand in for xlTop, but the water gets a little deep after that. I took a couple of examples I found on various forums and tried to adapt them, but to no luck.
Once again, I turn to you guys for the final piece of the puzzle!
I've been banging my head up against this all morning. I feel like I'm really close to cracking it, but just can't get the last step.
I've managed to cobble together the code below. It exports the results of my query to an Excel workbook and plants it on my desktop. So far, so good. I've managed to open the file once it's created. I've even managed to set the row height for my results.
Code:
Private Sub CmdRunReport_Click()
'This exports the "Aging Purchse Orders" query to an Excel workbook and retains the original formatting
DoCmd.OutputTo ObjectType:=acOutputQuery, ObjectName:="Aging Purchase Orders", OutputFormat:=acFormatXLSX, OutputFile:="C:\Users\g.birkholz\Desktop\Aging POs " & Format(Date, "mm-dd-yyyy") & ".xlsx"
'This opens up the Excel workbook
Dim xlObj As Object
Set xlObj = CreateObject("excel.application")
xlObj.visible = True
xlObj.workbooks.Open "C:\Users\g.birkholz\Desktop\Aging POs " & Format(Date, "mm-dd-yyyy") & ".xlsx"
'This sets the height of the data-bearing rows to 50
xlObj.ActiveSheet.Rows("2:20").RowHeight = 50
End Sub
Code:
xlObj.ActiveSheet.Rows("2:20").VerticalAlignment = xlTop
Code:
xlObj.ActiveSheet.Rows("2:20").SelectI've since read something about not being able to use xl* constants with Late Binding, which is evidently what I've done here. What I think I understand is that I now need to define a specific constant within the Sub () that will stand in for xlTop, but the water gets a little deep after that. I took a couple of examples I found on various forums and tried to adapt them, but to no luck.
Once again, I turn to you guys for the final piece of the puzzle!
