none
Access Automation to format Excel Spreadsheet RRS feed

  • Question

  • If I want to have the following formats on a spreadsheet but don't have the ability to use a Macro within Excel how can I force the following formats?

    Thank you!!!

    Option Explicit
    
    Sub RDFieldTestPending()
    '
    ' RDFieldTestPending Macro
    ' 11x17, Landscape, Mar LR .45 TB .5 Columns L-N Currency, Repeat Col Width .4, 1.5, 3, .9, .9, .9, .9, 2, 1.55, .9, .9, .75, .75, .75
    '
    ' Keyboard Shortcut: Ctrl+f
    '
        Application.PrintCommunication = False
        With ActiveSheet.PageSetup
            .PrintTitleRows = ""
            .PrintTitleColumns = ""
        End With
        Application.PrintCommunication = True
        ActiveSheet.PageSetup.PrintArea = ""
        Application.PrintCommunication = False
        With ActiveSheet.PageSetup
            .LeftHeader = ""
            .CenterHeader = ""
            .RightHeader = ""
            .LeftFooter = ""
            .CenterFooter = ""
            .RightFooter = ""
            .LeftMargin = Application.InchesToPoints(0.45)
            .RightMargin = Application.InchesToPoints(0.45)
            .TopMargin = Application.InchesToPoints(0.5)
            .BottomMargin = Application.InchesToPoints(0.5)
            .HeaderMargin = Application.InchesToPoints(0.3)
            .FooterMargin = Application.InchesToPoints(0.3)
            .PrintHeadings = False
            .PrintGridlines = False
            .PrintComments = xlPrintNoComments
            .PrintQuality = 600
            .CenterHorizontally = False
            .CenterVertically = False
            .Orientation = xlLandscape
            .Draft = False
            .PaperSize = xlPaper11x17
            .FirstPageNumber = xlAutomatic
            .Order = xlDownThenOver
            .BlackAndWhite = False
            .Zoom = 100
            .PrintErrors = xlPrintErrorsDisplayed
            .OddAndEvenPagesHeaderFooter = False
            .DifferentFirstPageHeaderFooter = False
            .ScaleWithDocHeaderFooter = True
            .AlignMarginsHeaderFooter = True
            .EvenPage.LeftHeader.Text = ""
            .EvenPage.CenterHeader.Text = ""
            .EvenPage.RightHeader.Text = ""
            .EvenPage.LeftFooter.Text = ""
            .EvenPage.CenterFooter.Text = ""
            .EvenPage.RightFooter.Text = ""
            .FirstPage.LeftHeader.Text = ""
            .FirstPage.CenterHeader.Text = ""
            .FirstPage.RightHeader.Text = ""
            .FirstPage.LeftFooter.Text = ""
            .FirstPage.CenterFooter.Text = ""
            .FirstPage.RightFooter.Text = ""
        End With
        Application.PrintCommunication = True
        Columns("A:A").Select
        Selection.ColumnWidth = 4.29
        Columns("B:B").Select
        Selection.ColumnWidth = 18.43
        Columns("C:C").Select
        Selection.ColumnWidth = 37.57
        Columns("D:G").Select
        Selection.ColumnWidth = 10.71
        Columns("H:H").Select
        Selection.ColumnWidth = 24.86
        Columns("I:I").Select
        Selection.ColumnWidth = 19
        Columns("J:K").Select
        Selection.ColumnWidth = 10.71
        Columns("L:N").Select
        Selection.ColumnWidth = 8.86
        Selection.NumberFormat = "$#,##0.00"
        Selection.NumberFormat = "$#,##0.00"
    End Sub
    


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Tuesday, February 23, 2016 5:58 PM

All replies

  • Hi, KCDW

    First of all I want to confirm with you that in this thread is there any relation between excel spreadsheet and Access Application or you by mistake posted a excel thread in Access forum. Because you want to set properties of excel spreadsheet. But in title you mention that “Access Automation to format Excel Spreadsheet ” because you only want to set spreadsheet properties that is not related with access in any manner. 

    The other thing I want to confirm with you that what do you mean by saying that you are not able to use macro. Is there any problem with macro?

    You want to customize the spreadsheet with VSTO?

    Regards

    Deepak

    Wednesday, February 24, 2016 8:46 AM
    Moderator
  • Hi, KCDW

    First of all I want to confirm with you that in this thread is there any relation between excel spreadsheet and Access Application or you by mistake posted a excel thread in Access forum. Because you want to set properties of excel spreadsheet. But in title you mention that “Access Automation to format Excel Spreadsheet ” because you only want to set spreadsheet properties that is not related with access in any manner. 

    The other thing I want to confirm with you that what do you mean by saying that you are not able to use macro. Is there any problem with macro?

    You want to customize the spreadsheet with VSTO?

    Regards

    Deepak

    Thank you for responding Deepak,

    The Access Forum is the correct Forum.

    I am coding in Access to send a Query out to Excel. This will be for the end user to perform additional duties not related to the database however the data can only come from the database.

    Rather than have the end user spend time reformatting the page layout and column/row sizes and since they are blocked from having Excel Macros, there is a need to set the formatting described above.

    I created a Macro on my PC to affect the formatting directly in Excel and posted the code to give a view of what needs to be achieved. I suppose first I'll have to construct code to create the spreadsheet then make it viewable. Next, open the recordset and set the format. Or if there is a way to use a template that could be stored on the users PC that might work better. Do you have any thoughts in this regard?

    Thank you!!!


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Wednesday, February 24, 2016 3:05 PM
  • Hi, KCDW

    Yes there is a way to use excel template. You want to set your worksheet like above mentioned macro code. So you can run this code so that you get your worksheet formatted. After that save this worksheet as a template. There are 2 options are there. “Excel Template” and  “Excel macro enabled template” as your end user don’t have the rights to use macro. So you can select “Excel Template”. So no need to set excel properties every time. You can distribute this template to end users.

    Regards

    Deepak

    Friday, February 26, 2016 8:43 AM
    Moderator
  • Hi, KCDW

    Yes there is a way to use excel template...You can distribute this template to end users.

    Regards

    Deepak

    Thank you for your response Deepak.

    How does the end user, use this?


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Friday, February 26, 2016 5:01 PM
  • Hi Chris. Pardon me for jumping in but how exactly are you sending the query out to Excel?
    Friday, February 26, 2016 5:16 PM
  • Welcome,

    the DB guy,

    Well I've been working with,

    DoCmd.OutputTo acOutputQuery, "RDFieldTest", acFormatXLS, "", True

    and playing with the Export to Spreadsheet but not having much luck with that.

    Thank you!!!


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Friday, February 26, 2016 7:17 PM
  • Welcome,

    the DB guy,

    Well I've been working with,

    DoCmd.OutputTo acOutputQuery, "RDFieldTest", acFormatXLS, "", True

    and playing with the Export to Spreadsheet but not having much luck with that.

    Thank you!!!


    Hi Chris. Thanks for the clarification. To format the resulting spreadsheet, you'll need to use "automation." For instance, something like:

    Dim objXL As Object
    Dim xlWB As Object
    Dim xlWS As Object
    
    Set objXL = CreateObject("Excel.Application")
    Set xlWB = objXL.Workbooks.Open("c:\folder\file.xlsx")
    Set xlWS = xlWB.Sheets("Sheet1")
    
    With xlWS
        .Range("a1").Font.Size = 24
    End With
    
    xlWB.Save
    
    Set xlWS = Nothing
    Set xlWB = Nothing
    Set objXL = Nothing
    
    
    Also, take a look at this article. Hope that helps...

    Friday, February 26, 2016 7:42 PM
  • Thanks again the DB Guy.

    I can then copy and paste the above formats and paste them after the

    With xlWS
       
    .Range("a1").Font.Size = 24

    Like

    With xlWS
       
    .Range("a1").Font.Size = 24
           .LeftHeader = ""
           
    .CenterHeader = ""
           
    .RightHeader = ""
           
    .LeftFooter = ""
           
    .CenterFooter = ""
           
    .RightFooter = ""
           
    .LeftMargin = Application.InchesToPoints(0.45)
           
    .RightMargin = Application.InchesToPoints(0.45)
           
    .TopMargin = Application.InchesToPoints(0.5)
           
    .BottomMargin = Application.InchesToPoints(0.5)
           
    .HeaderMargin = Application.InchesToPoints(0.3)
           
    .FooterMargin = Application.InchesToPoints(0.3)
           
    .PrintHeadings = False .PrintGridlines = False
           
    .PrintComments = xlPrintNoComments
           
    .PrintQuality = 600
           
    .CenterHorizontally = False
           
    .CenterVertically = False
           
    .Orientation = xlLandscape
           
    .Draft = False
           
    .PaperSize = xlPaper11x17
           
    .FirstPageNumber = xlAutomatic
           
    .Order = xlDownThenOver
           
    .BlackAndWhite = False
           
    .Zoom = 100
           
    .PrintErrors = xlPrintErrorsDisplayed
           
    .OddAndEvenPagesHeaderFooter = False
           
    .DifferentFirstPageHeaderFooter = False
           
    .ScaleWithDocHeaderFooter = True
           
    .AlignMarginsHeaderFooter = True
           
    .EvenPage.LeftHeader.Text = ""
           
    .EvenPage.CenterHeader.Text = ""
           
    .EvenPage.RightHeader.Text = ""
           
    .EvenPage.LeftFooter.Text = ""
           
    .EvenPage.CenterFooter.Text = ""
           
    .EvenPage.RightFooter.Text = ""
           
    .FirstPage.LeftHeader.Text = ""
           
    .FirstPage.CenterHeader.Text = ""
           
    .FirstPage.RightHeader.Text = ""
           
    .FirstPage.LeftFooter.Text = ""
           
    .FirstPage.CenterFooter.Text = ""
           
    .FirstPage.RightFooter.Text = ""
       
    End With
        Application
    .PrintCommunication = True
        Columns
    ("A:A").Select
        Selection
    .ColumnWidth = 4.29
        Columns
    ("B:B").Select
        Selection
    .ColumnWidth = 18.43
        Columns
    ("C:C").Select
        Selection
    .ColumnWidth = 37.57
        Columns
    ("D:G").Select
        Selection
    .ColumnWidth = 10.71
        Columns
    ("H:H").Select
        Selection
    .ColumnWidth = 24.86
        Columns
    ("I:I").Select
        Selection
    .ColumnWidth = 19
        Columns
    ("J:K").Select
        Selection
    .ColumnWidth = 10.71
        Columns
    ("L:N").Select
        Selection
    .ColumnWidth = 8.86
        Selection
    .NumberFormat = "$#,##0.00"
        Selection
    .NumberFormat = "$#,##0.00"


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Friday, February 26, 2016 8:06 PM
  • Hi Chris. Probably not a complete copy and paste, but you'll have to try and test to catch any issues. Good luck!
    Friday, February 26, 2016 8:51 PM
  • Thanks Edward, however this is not the full solution and the credit for the partial answer is due to the DB Guy. However I don't have a full solution yet and still cannot mark this thread closed.

    Sincerely,

    Chris.


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012


    • Edited by KCDW Monday, March 7, 2016 10:30 PM
    Monday, March 7, 2016 10:29 PM