Operating Excel using Access VBA

I have a procedure that creates a report in Excel.  The procedure below, which merges cells, fails every second time I create the Excel file (Succeed-Fail-Succeed-Fail..).  When it fails, the value of Selection is Nothing; when it succeeds, the value is the value in the cell.  The error message is:

Run-time error '91':
Object variable or With block variable not set

The rows and columns in the range are the same when it fails as when it succeeds.



Sub cellVerticalMerge(inRow1 As Long, inColumn1 As Long, inRow2 As Long, inColumn2 As Long)

'    Application.ScreenUpdating = False
'    Application.DisplayAlerts = False
    DoCmd.SetWarnings False
    xl.Range(xl.Cells(inRow1, inColumn1), xl.Cells(inRow2, inColumn2)).Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    DoCmd.SetWarnings True
'    Application.ScreenUpdating = True
'    Application.DisplayAlerts = True
   
End Sub

Here's the main procedure:

Sub makeBrief()

    Dim rec As Recordset
    Dim filePath As String
    Dim sql As String
    Dim previousDate As Date
    Dim thisRow As Long
    Dim thisColumn As Long
    Dim Facility As Long
    Dim facilityLast As Long
    Dim facilityRowStart As Long
    Dim oldUpdates As String
    Dim newUpdates As String

    DoCmd.SetWarnings False
    DoCmd.SetWarnings True
    thisRow = 4
    thisColumn = 1
    facilityRowStart = 4
    Facility = 0
    facilityLast = 0
    'filePath = "\\xlgc07015f-fs02\30LCG_ALL\MAFIA Working Group\Timeline.xlsx"
    filePath = "C:\Users\1505990036E\Desktop\Dev\FIM\1 ASTS Weekly Ops Status Facilities.xlsx"
    Set xl = New Excel.Application
    xl.ScreenUpdating = False
    xl.DisplayAlerts = False
    'Set xlBook = xl.Workbooks.Add
    Set xlBook = xl.Workbooks.Open(filePath)
    Set xlSheet = xlBook.Worksheets(1)
    xl.Visible = True
    With xlSheet
        .Cells(2, 2) = "Total Open W/Os: " & getCountOfWOs(GetPreviousDate)
        Set rec = CurrentDb.OpenRecordset(getsqlWeeklyOps(GetPreviousDate))
        Do While Not rec.EOF
            Facility = rec!Facility
            If facilityLast <> 0 And facilityLast <> Facility Then
                cellVerticalMerge facilityRowStart, 1, thisRow - 1, 1
                cellVerticalMerge facilityRowStart, 2, thisRow - 1, 2
                facilityRowStart = thisRow
            End If
            If IsNull(rec!oldUpdates) Then
                oldUpdates = ""
            Else
                oldUpdates = rec!oldUpdates
            End If
            If IsNull(rec!newUpdates) Then
                newUpdates = ""
            Else
                newUpdates = rec!newUpdates
            End If
            cellFacility thisRow, 1, rec!Facility, rec!OpenWorkOrders
            cellStatus thisRow, 2, rec!RiskStatus
            If IsNull(rec!DateOpened) Then
                cellNSTR (thisRow)
            Else
                Cells(thisRow, 3) = rec!Title
                Cells(thisRow, 4) = rec!DateOpened
                cellUpdate thisRow, 5, oldUpdates, newUpdates
            End If
            thisRow = thisRow + 1
            facilityLast = Facility
            rec.MoveNext
        Loop
    End With
    xl.ScreenUpdating = True
    xl.DisplayAlerts = True
    Set xl = Nothing
   
End Sub
Barry SweezeySoftware EngineerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
Barry

You shouldn't use Select/Selection when automating Excel from Access.

That might be why you are having problems.

Can't have a proper look at the code right now but I'll post back later.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
hi Barry,

as Norie suggested, it is best to avoid Select/Selection when automating Excel.  Sometimes a selection must be used (like for freezing panes, and changing zoom) -- but most often, not.

for instance, this:
    xl.Range(xl.Cells(inRow1, inColumn1), xl.Cells(inRow2, inColumn2)).Select
    With Selection
could be changed to

    With xl.Range( xl.Cells(inRow1, inColumn1), xl.Cells(inRow2, inColumn2) )

... however, I do not see where "xl" is set ... hence probable reason for "Object variable or With block variable not set"

add an error handler to the code to see where the error is happening. Here is a short video on error handling. There are 2 other videos in this series if you wish to know more.

https://www.experts-exchange.com/videos/1478/Basic-Error-Handling-code-for-VBA-and-Microsoft-Office.html

~~~
when code is posted, everything it calls must be posted too, such as getsqlWeeklyOps ... however I suspect this inital problem is that xl is not defined. If it is, the procedure where that happens was not included. What is is the code that is calling cellVerticalMerge?

~~
what is this being done?
"DoCmd.SetWarnings False"
often DoCmd can be avoided by using alternate syntax --I don't see anything that needs warnings to be turned off

have an awesome day,
crystal

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Barry SweezeySoftware EngineerAuthor Commented:
I made the change you suggested and it ran correctly ten times, so the Select/Selection seems to have been the problem. Thanks for your help!
Fabrice LambertConsultingCommented:
Additional notes:

When playing with application environment (SetWarnings, .ScreenUpdating, Application.DisplayAlerts) you should write an error Handler to restore the values in case of troubles.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.