Sometimes Run time error 1004 - only sometimes ???

[keldsor]

I use this code in Access to draw a timeline and freeze panes in Excel - so the commands are for the Excel enviroment - that's why I'm asking here.

Sometimes it runs into error 1004 in the marked line:

Private Sub bygTidslinien(st As Integer, d As Boolean)
    Dim xlApp As Object
    Dim wb As Excel.Workbook
    Dim ws As Excel.Worksheet
    Set xlApp = GetObject(, "Excel.Application")
    With xlApp
        .Run "DeleteAllShapes"
        .Run "timeLine", txtFra, txtTil, st, d
        .ActiveSheet.Cells(5, 5).Select
        .ActiveWindow.FreezePanes = True        <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< error 1004 ????
    End With
    AppActivate xlApp.Caption
    Set xlApp = Nothing
End Sub

Why ?

and can it be done WITHOUT using ".Select" ?

 

[rlv01]

My own habit is to be more deliberate about making sure that FreezePanes is set to false before calling FreezePanes = True

Private Sub bygTidslinien(st As Integer, d As Boolean)
    Dim xlApp As Object
    Dim wb As Excel.Workbook
    Dim ws As Excel.Worksheet
    Set xlApp = GetObject(, "Excel.Application")
    With xlApp
        .Run "DeleteAllShapes"
        .Run "timeLine", txtFra, txtTil, st, d

        If .ActiveWindow.FreezePanes Then  '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
            .ActiveWindow.FreezePanes = False
        End If

        .ActiveSheet.Cells(5, 5).Select
        .ActiveWindow.FreezePanes = True
    End With
    AppActivate xlApp.Caption
    Set xlApp = Nothing
End Sub

As far as not using select, I've seen it done like this:

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">With ActiveWindow
     If.FreezePanes Then
       .FreezePanes =False
     end if
    .SplitColumn =0
    .SplitRow =1
    .FreezePanes =True
End With
</code>

 

[keldsor]

>>> rlv01

Thx, so fare it works nicely

 

[keldsor]

No it's not stable !

I have this code:

Private Sub bygTidslinien(st As Integer, d As Boolean)
    Dim xlApp As Object
    Dim wb As Excel.Workbook
    Dim ws As Excel.Worksheet
    Set xlApp = GetObject(, "Excel.Application")
    With xlApp
        .Run "DeleteAllShapes"
        .Run "timeLine", txtFra, txtTil, st, d
        With .ActiveWindow
            If .FreezePanes Then
                .FreezePanes = False         '<<<<<<<<<<<<<<<<  sometimes it fails here !
            End If
            .SplitColumn = 4
            .SplitRow = 4
            .FreezePanes = True
        End With
    End With
    AppActivate xlApp.Caption
    Set xlApp = Nothing
End Sub

and it still "SOMETIMES" fail (Run time error 1004) in the marked line !

No problem doing the IF-testing the state of freezing panes !!!!!

It's kind of weird, I think !