Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2008
    Posts
    9

    Unanswered: Formatting Excel cells from within Access VB module

    I am generating an excel sheet using the data from Access and an Access VB module. All the data is going to the correct cells and looks fine with exception of some formatting. I can get bold and font size to work, the text centering refuses to work no matter what I have tried...


    Code:
    	Dim xlApplication As Object 'This is our spreadsheet object
    
    Sub XLS_Export()
    	.
    	.
    	Set xlApplication = CreateObject("Excel.Application")
    	xlApplication.Visible = False
    	Set xlWorkbook = xlApplication.Workbooks.Add
    	.
    	.
    	.
    'These lines of code do work with no errors!
    
    	xlApplication.Worksheets(1).Cells.Select
    	xlApplication.Worksheets(1).Cells.EntireColumn.AutoFit  'Resize the spreadsheet
    
    	xlApplication.Worksheets(1).Rows(1).Font.Bold = True
    	xlApplication.Worksheets(1).Cells(1, 1).Font.Size = 14
    
    'These lines of code do not work, Run-time error '1004'
    'Unable to set the HorizontalAlignment property of the Range class.
    
    	xlApplication.Worksheets(1).Cells(1, 1).HorizontalAlignment = xlCenter
    	xlApplication.Worksheets(1).Cells(1, 1).VerticalAlignment = xlCenter
    
    
    'This does not work either (same run-time error) with exception of .Font.Bold
    
    	xlApplication.Worksheets(1).Rows(1).Select
    	With xlApplication.Selection
    		.HorizontalAlignment = xlCenter
    		.VerticalAlignment = xlCenter
    		.Font.Bold = True
    	End With
    	.
    	.
    	.
    End Sub
    I'm using Access 2003 w/ Visual Basic 6.3 and Excel 2003 SP3. Not sure what I'm doing wrong here while trying to center the text in the cells...

    Thank-you, Bill

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Excel Help is a bit confusing on this score. It sounds like you can only set the VerticalAlignment and HorizontalAlignment on a range of cells, as long as the range contains more than one cell. Try setting the property on an entire row or column, as a test, or at least several cells, and see if that works.

    Sam

  3. #3
    Join Date
    Jul 2008
    Posts
    9
    Glad to see that the site is back up now. I did some more hunting around the web and found a few more folks having the same problem. One solution was to set the .HorizontalAlignment and .VerticalAlignment to a numeric value instead of the normal constant name like "xlCenter". Since I wanted to center the text, I used the numeric value of -4108 and it works. Below are the formatting items that I could get to work and the ones that didn't work for me.


    Code:
        Dim MyExcel As Object 'This is the excel object
        Dim MyBook As Object
        Dim MySheet As Object
    
    Sub Acad_Cons1()
        .
        .
        .
        Set MyExcel = CreateObject("Excel.Application")
        MyExcel.Visible = False
        Set MyBook = MyExcel.Workbooks.Add
        Set MySheet = MyBook.Worksheets.Add
        MySheet.Move After:=MyBook.Sheets(MyBook.Sheets.Count)
        .
        .
        .
    
        With MySheet.Rows("4:4")
            '.HorizontalAlignment = xlHAlignCenter          'Run-Time Error 1004
            '.Cells.HorizontalAlignment = xlHAlignCenter    'Run-Time Error 1004
            '.HorizontalAlignment = xlCenter                'Run-Time Error 1004
            .HorizontalAlignment = -4108                    'Works fine and centers cell contents correctly
            '.HorizontalAlignment = Constants.xlCenter      'Error, Data member not found
            '.Cells.HorizontalAlignment = xlCenter          'Run-Time Error 1004
            '.VerticalAlignment = xlCenter                  'Run-time Error 1004
            .VerticalAlignment = -4108                      'Works fine and centers cell contents correctly
            .WrapText = True                                'Works Fine
            .Font.Bold = True                               'Works Fine
            .Font.Name = "Arial"                            'Works Fine
            .Font.Size = 10                                 'Works Fine
            .Cells.RowHeight = 13                           'Works Fine
            '.Borders(xlEdgeBottom).LineStyle = xlContinuous    'Run-Time Error
            '.Borders(xlEdgeBottom).Weight = xlThick            'Run-time Error
           
        End With
        .
        .
        .
    
    End Sub
    Not the cleanest way of getting it to work, but it does work... Incidently, the formatting works just fine when the VB code is written inside an Excel macro. But when run from within Access, I had to work around the formatting issues...

    Bill

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Thanks for posting the solution

    If you had Option Explicit set in the module, I wonder if you would be forced to abandon Excel constants such as xlCenter?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    This response is not another solution to the problem, but rather a neat way of working. One of my clients had an application which interfaced a lot with Excel and the programmer had set up a global module setting just about every Excel property known to its appropriate numerical equivalent. This meant that he could use functions of Excel origin in his Access applications and vice versa.

  6. #6
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    This response is not another solution to the problem, but rather a neat way of working. One of my clients had an application which interfaced a lot with Excel and the programmer had set up a global module setting just about every Excel property known to its appropriate numerical equivalent. This meant that he could use functions of Excel origin in his Access applications and vice versa.

  7. #7
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Daft question, but have you set up a reference to Excel in the VBA project?

  8. #8
    Join Date
    Jul 2008
    Posts
    9
    It might not be a daft question... The only object references to excel are listed in the code above... Do you see a missing reference that should be there? If you have one in mind, I would be willing to try it!

    Bill

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    looks like a reasonably classic late-bind to excel.
    no reference required AND my preferred route since it bypasses excel-version-panic BUT you need to declare your constants (xlHAlignCenter etc) locally in A-VBA since these values will not be known to A because of the late-bind.

    izy
    currently using SS 2008R2

  10. #10
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Quote Originally Posted by Bill322
    Do you see a missing reference that should be there? If you have one in mind, I would be willing to try it!
    Bill
    In the VBA code editor, go to Tools --> References. If it's not selected, scroll down the list until you find Microsoft Excel x.y Object Library (mine's 9.0, for example), and select it. This will give Access access to the Excel constants and methods.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •