Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2009
    Posts
    4

    Unanswered: Center in Excel from Access VBA

    I am trying to center some cells in an excel worksheet and turn on track changes on the workbook. This is what I have:

    Code:
            Set appexcel = CreateObject("Excel.Application")
            Set wrkbk = appexcel.Workbooks.Open(wkBookName)
            Set wrksht = wrkbk.Worksheets(1)
    
            With wrksht.Rows("1:1")
                .HorizontalAlignment = xlCenter
            End With
            With wrkbk
                .KeepChangeHistory = True
                .ChangeHistoryDuration = 7
            End With

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Welcome to the forums.

    So what's the problem? What behavior are you getting, and how is it different from what you want?

    There's a post in our Code Bank that does a lot of good Excel exporting and formatting, I'd suggest having a look there to see if it's any help to you.

    Cheers!
    Me.Geek = True

  3. #3
    Join Date
    Aug 2009
    Posts
    4
    It is not doing anything. I want it to center the headings on the sheet but nothing is happening. I will take a look at the link.

    Another question. I have this query. This may be a dumb way to do it but it works.

    Code:
    SELECT DISTINCT "BD" AS CC, 
    (SELECT COUNT(*) FROM table WHERE CC = 1 AND TYPE = 3 AND LOC = 1 GROUP BY CC) AS veh1,
    (SELECT COUNT(*) FROM table WHERE CC = 1 AND TYPE = 2 AND LOC = 1 GROUP BY CC) AS veh2,
    (SELECT COUNT(*) FROM table WHERE CC = 1 AND TYPE = 1 AND LOC = 1 GROUP BY CC) AS veh3,
    (SELECT COUNT(*) FROM table WHERE CC = 1 AND LOC = 1 GROUP BY CC) AS TOTAL
    FROM
    table
    UNION
    SELECT DISTINCT "BL" AS CC, 
    (SELECT COUNT(*) FROM table WHERE CC = 2 AND TYPE = 3 AND LOC = 1 GROUP BY CC) AS veh1,
    (SELECT COUNT(*) FROM table WHERE CC = 2 AND TYPE = 2 AND LOC = 1 GROUP BY CC) AS veh2,
    (SELECT COUNT(*) FROM table WHERE CC = 2 AND TYPE = 1 AND LOC = 1 GROUP BY CC) AS veh3,
    (SELECT COUNT(*) FROM table WHERE CC = 2 AND LOC = 1 GROUP BY CC) AS TOTAL
    FROM
    table
    UNION
    SELECT DISTINCT "MR" AS CC, 
    (SELECT COUNT(*) FROM table WHERE CC = 4 AND TYPE = 3 AND LOC = 1 GROUP BY CC) AS veh1,
    (SELECT COUNT(*) FROM table WHERE CC = 4 AND TYPE = 2 AND LOC = 1 GROUP BY CC) AS veh2,
    (SELECT COUNT(*) FROM table WHERE CC = 4 AND TYPE = 1 AND LOC = 1 GROUP BY CC) AS veh3,
    (SELECT COUNT(*) FROM table WHERE CC = 4 AND LOC = 1 GROUP BY CC) AS TOTAL
    FROM
    table
    Now I know I could do a group by, but it was easier for me to do it this way so I can just union on as many rows as I want. Ok, enough of explaining this is my real question. Often times it returns nothing for certain vehicles. Instead of it showing null I want it to show a 0. Any advice?
    Last edited by resullivan; 08-25-09 at 00:34.

  4. #4
    Join Date
    Aug 2009
    Posts
    4
    I think the problem with centering is the xlCenter. It is saying that it cannot set the property.

  5. #5
    Join Date
    Aug 2009
    Posts
    4
    FYI. I got it to center, but I am still working on turning on tack changes. This is what I did.

    In VBA goto

    Tool>References

    Then check Microsoft Excel *

  6. #6
    Join Date
    May 2005
    Posts
    1,191
    for the null to zero thing, look at the IIF function.
    Me.Geek = True

Posting Permissions

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