Run time error '13': Type Mismatch

[Cark]

I have a bit of VBA that I am trying to make my export from Access carry out at the export stage. I am wanting columns A, B, C, D, E and H to be formatted with horizontal alignment = center and vertical alignment = center.

My code is giving me the error at:

        .Columns("A:A,B:B,C:C,D:D,E:E,H:H").HorizontalAlignment = xlCenter

The full code is:

    Set objApp = CreateObject("Excel.Application")
    objApp.Visible = True
    Set wb = objApp.workbooks.Open(FileName, True, False)
    'select all worksheets & cells In turn
    For Each WS In wb.worksheets
    With WS
            .Cells.Font.Name = "Arial"
            lastrow = .Range("A1").currentregion.Rows.Count
            lastCol = .Range("A1").currentregion.Columns.Count
        .Columns("D").Font.Bold = True
        .Columns("D").Font.Italic = True
        .Range("H:H").NumberFormat = "[hh]:mm"
        .Columns("H:H").Replace ":", ":"
        .Columns("A:A").NumberFormat = "d-mmm-yy"
        .Columns("A:A,B:B,C:C,D:D,E:E,H:H").HorizontalAlignment = xlCenter
        .Columns("A:A,B:B,C:C,D:D,E:E,H:H").VerticalAlignment = "xlCenter"
        .Columns("A:A,B:B,C:C,D:D,E:E,H:H").WrapText = False
        .Columns("A:A,B:B,C:C,D:D,E:E,H:H").Orientation = 0
        .Columns("A:A,B:B,C:C,D:D,E:E,H:H").AddIndent = False
        .Columns("A:A,B:B,C:C,D:D,E:E,H:H").IndentLevel = 0
        .Columns("A:A,B:B,C:C,D:D,E:E,H:H").ShrinkToFit = False
        .Columns("A:A,B:B,C:C,D:D,E:E,H:H").ReadingOrder = "xlContext"
        .Columns("A:A,B:B,C:C,D:D,E:E,H:H").MergeCells = False
        .Rows(1).HorizontalAlignment = xlCenter
        .Rows(1).VerticalAlignment = xlCenter
        .Rows(1).Font.Bold = True
        .Rows(1).Font.Italic = False
        .Rows(1).Interior.Color = RGB(200, 200, 200)
        .Rows(1).Font.Color = RGB(0, 0, 0)
        .Cells.EntireColumn.AutoFit
    End With
    
    Next 'next worksheet
 objApp.sheets(1).Activate
    Set objApp = Nothing

I also have another line for the vertical alignment which I am guessing will need to follow the same solution method as with the horizontal.

 

[Ranman256]

xlCenter is not a value in Access. Its a constant only in Excel.
you set some to :"xlCenter"
and some to xlCenter
its not a string value, its numeric.
but in excel xlCenter = -4108

so replace the word with: -4108
.Columns("A:A,B:B,C:C,D,E:E,H:H").HorizontalAlignment =-4108

 

[CJ_London]

don't think columns will accept more than one argument, try

.Columns("A:E").HorizontalAlignment = xlCenter
.Columns("H").HorizontalAlignment = xlCenter

 

[Cark]

Thanks. Changing it to -4108 solved the issue.

Keeping the code like this still worked fine.

        .Range("A:A,B:B,C:C,D:D,E:E,H:H").HorizontalAlignment = -4108
        .Range("A:A,B:B,C:C,D:D,E:E,H:H").VerticalAlignment = -4108

Is there a list of Excel to Access conversions for stuff like this? I did a lot of googling around HorizontalAlignment and errors etc and didn't come across anything close to -4108. Even after finding that -4108 works, I googled -4108 and HorizontalAlignment error and couldn't find anything.

I use Excel's macro recorder feature a lot to get the bulk of my VBA Access export formatting code etc as it allows a newbie like me to quickly piece together code. A list of conversions would be really useful to me.

 

[JANR]

Is there a list of Excel to Access conversions for stuff like this?

list over Excel constants: https://msdn.microsoft.com/en-us/library/aa221100(office.11).aspx

JanR

 

[The_Doc_Man]

Or you could have added Excel to your references to get all of the Excel constants defined for you and then stayed with the symbolic name? I checked. For the version of Access I am using, it is simply the Microsoft Excel 14.0 Object Library (in the references list).

If you don't know how to add references, do this

Open the database in a way that allows you to design things. (I.e. you have to be able to see the navigation pane and database window and ribbon.)

Select tab Database Tools. Click Visual Basic. Click Tools. Click References (from the drop-down). Now use the scroll bars to find the name listed above. Check the box. Using Apply when available, Close your way back out to the code window. I suggest that you compile your code at this point. Then see if that fixed everything.