access to excel help

[Eilleye]

Hi,

Thanks for taking the time to view this post.

I need export an access query to an excel file which accomplished using the below code. The export will be posted on a SharePoint site for our customers to see, so i need to make it more "Pretty". Is there a way to customize the export (i.e. column height, row color, font etc) before it opens in excel?

Thanks in advanced


Private Sub Button_run_report_Click()
On Error GoTo Button_run_report_Click_Err
DoCmd.OutputTo acOutputQuery, "IDAS_help", "ExcelWorkbook(*.xlsx)", "", True, "", , acExportQualityPrint

Button_run_report_Click_Exit:
Exit Sub
Button_run_report_Click_Err:
MsgBox Error$
Resume Button_run_report_Click_Exit
End Sub

[pbaldy]

You have more control over the output if you use automation, either to perform the export or to open the file after the export and manipulate it. Here's a primer:

http://support.microsoft.com/kb/247412

One trick I use when trying to figure out how to do something in automation is to record an Excel macro that performs the function, then examine/use that code in my Access code. It typically has to be tweaked, but gives you the guts of what you need.

[Eilleye]

pbaldy, thanks for the reply.

I record an Excel macro, but I am a VBA novice i have no clue how to get the code to work. can you help?

Sub test()
Cells.Select
Selection.ClearFormats
Columns("F:G").Select
Range("G1").Activate
Selection.NumberFormat = "m/d/yyyy"
Range("A1").Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$I$135"), , xlYes).Name = _
"Table1"
Range("Table1[#All]").Select
ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleMedium2"
Cells.Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("C7").Select
Columns("C:C").EntireColumn.AutoFit
Range("C8").Select
ActiveWindow.SmallScroll Down:=-12

End Sub

[pbaldy]

If you review that link, in general what's needed from outside Excel is the Excel or Sheet object created in your automation code. For instance, in the link:

oSheet.Range("A1").Value = "Last Name"

or this from an app I have open:

xl.Cells(50, 1).Select
xl.Selection.Interior.ColorIndex = 8