Align the data in the center using vba

[aman]

Hi All

I have written the following code to print data in excel sheet.It works fine but as its orientation is landscape so I want to align all the rows in the center.And also I want to set the header bold,underline and 16 size.

Set qdf = CurrentDb.CreateQueryDef("qrytemp", strsql)
    Dim objXls As Excel.Application
    Dim objWrkBk As Excel.Workbook
    Dim xprtFile As String
       
   xprtFile = "C:\Documents and Settings\Amanpreet Kaur\Desktop\rescansys.xls"
    DoCmd.OutputTo acOutputQuery, "qrytemp", acFormatXLS, xprtFile, False
    
    Set objXls = New Excel.Application
    objXls.Visible = False
     Const xlLandscape = 2
    Set objWrkBk = objXls.Workbooks.Open(xprtFile)
    objWrkBk.Sheets("qrytemp").Select
    With objWrkBk.Sheets("qrytemp").PageSetup
       .centerheader="MI Report"
        .Orientation = xlLandscape
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
    End With
        
         objWrkBk.PrintOut
    objWrkBk.Close SaveChanges:=False
    Set objWrkBk = Nothing
    objXls.Quit
    
    Set objXls = Nothing

Can anyone please help me how to solve this.

Thanks
AMAn

 

[DCrake]

I have been following your threads closely and it appears you have not grssped the nettle. Just to give you a pointer in the right direction, if not to simply curt down on the number of posts you are making. Why not create a blank spreadsheet in the format you want, with the correct heads and column headings, formatting, fonts, orientation, etc. and save this as a template then use this as a matrix to populate later

 

[DreamGenius]

In the spirit of attempting to give back to this forum as much as I've already taken out of it, here's a subroutine that I wrote which does some basic formatting of an Excel spreadsheet that is created by exporting data from the database.

Public Sub FormatExcel(strFileName As String, strWorkSheet As String)
On Error GoTo Error_Proc
    Dim xlsExcel As Object
    Dim xlsSheet As Object
    Set xlsExcel = CreateObject("Excel.Application")
    Set xlsSheet = xlsExcel.workbooks.Open(strFileName).Sheets(strWorkSheet)
 
    With xlsExcel
        .Application.Sheets(strWorkSheet).Select
        .Application.Rows("1:1").Select
        .Application.Selection.Font.Bold = True
        .Application.range("A1").Select
        .Application.Selection.AutoFilter
        .Application.Cells.Select
        .Application.Selection.Columns.AutoFit
        .Application.range("A1").Select
        .Application.Activeworkbook.Save
        .Application.Activeworkbook.Close
        .Quit
   End With
Error_Proc:
    If err.Number > 0 Then
        MsgBox err.Number & " : " & err.Description
        Resume Exit_Proc
    End If
Exit_Proc:
    Set xlsExcel = Nothing
    Set xlsSheet = Nothing
    Exit Sub
End Sub

You pass two parameters to the subroutine when calling it, being the full file name with path and the sheet name to be formatted. As I'm exporting a query, the sheet name is the name of the query and is already held in a variable.

It then sets the first row to Bold, adds an Autofilter, autosizes all columns to fit the data they contain, sets Cell A1 as the cell with focus, saves and closes.

For what it's worth, I built the subroutine by recording a macro in Excel which did what I wanted to achieve, cutting the code from there into Access and then playing with it until it worked - Trial & Error.

You'll get more out of this forum if you post requests for solutions to problems you've experienced in attempting to do something than if you just post requests for people to deliver your solutions for you.

Hoping this helps some - my advice is go play, break it and work where and why.