We help IT Professionals succeed at work.

'Object variable or With block variable not set' - VBA error

2,114 Views
Last Modified: 2012-05-06
Hi guys,

  For the following VBA code, what I'm trying to do is open an excel sheet that was declared at the beginning.  Can anyone let me know why I'm getting the error 'Object variable or With block variable not set at: Set tempWorkbook = ExcelSheet.Workbooks.Open(pattern)   right inside my With  ExcelSheet block?  This has worked in other projects of mine, so I don't get it.
Private Sub QueryButton_Click()
  Dim fileName As String
        Dim endFileName As String
        Dim properFileName As String
        Dim masterCount As Integer
        Dim cumulativeCount As Integer
        Dim oExcel As Object
        Dim oBook As Excel.Workbook
        Dim myPattern As String
        Dim Path As String
        Dim pattern As String
        Dim copyArray(1000, 6) As String
        Dim oSheet As Excel.Worksheet
        Dim i As Integer
        Dim j As Integer
        Dim reportColumn As Integer
        Dim pageEndBlankLineCount As Integer
        Dim xlApp As Object
        Dim name As String
        Dim tempWorkbook As Excel.Workbook
        Dim tempWorksheet As Excel.Worksheet
        Dim EntireName As String
        Dim invalidDateRange As Boolean
        Dim ExcelSheet As Object
        isValidDateRange = True
        cumulativeCount = 0
        masterCount = 0
        If DateDiff("d", StartDate, EndDate) > 7 Then
            MsgBox ("You have chosen an interval that spans more than seven days.  " & vbCrLf + "This is not recommended, as it can cause too many alarm records " _
            & vbCrLf & "to be queried.  Please narrow your search range and try again.")
            isValidDateRange = False
        End If
        If isValidDateRange = True Then
        endFileName = Format(EndDate.Value, "yyyymmdd")
        fileName = Format(StartDate.Value, "yyyymmdd")
        myPattern = fileName
        While (CInt(myPattern <= endFileName))
            Path = "S:\PBrooks\Programs\jamescreek\ALMLOG\"      'Change this to the parent directory where the .dbf files are being dumped
            pattern = Path + myPattern + "AL.dbf"
            EntireName = myPattern + "AL.dbf"
           If Not FExists(pattern) Then
             MsgBox ("The date: " & Mid$(myPattern, 5, 2) & "/" & Mid$(myPattern, 7, 2) _
                     & "/" & Left$(myPattern, 4) & " does not have a report associated with it.")
             Else
             If masterCount = 0 Then
                  Set ExcelSheet = CreateObject("Excel.Application")
             End If
             
             With ExcelSheet
                Set tempWorkbook = ExcelSheet.Workbooks.Open(pattern) 'ERROR!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
                Set tempSheet = tempWorkbook.Worksheets(1)
                tempSheet.Activate
                For i = 1 To cumulativeCount + tempSheet.UsedRange.Rows.count
                    reportColumn = 0
                    If masterCount <> 0 And i = 1 Then
                    pageEndBlankLineCount = pageEndBlankLineCount + 1
                    End If
                    For j = 1 To tempSheet.UsedRange.Columns.count
                    If j = 1 Or j = 2 Or j = 4 Or j = 5 Or j = 6 Or j = 15 Then                     'Change these if you want different columns from the .dbf files (a = 1, b = 2, etc.)
                        copyArray(i + cumulativeCount - 1 - pageEndBlankLineCount, reportColumn) = tempWorkbook.Worksheets(1).Cells(i + 1, j)
                        reportColumn = reportColumn + 1
                    End If
                    Next
                Next
                isActive = False
                cumulativeCount = cumulativeCount + tempSheet.UsedRange.Rows.count - 1
                 tempWorkbook.Close (False)
                 
             End With
          
        End If
        myPattern = CLng(myPattern)
        If (Mid$(myPattern, 5, 2) = "02" And Mid$(myPattern, 7, 2) = "28") Then
            If ((Mid$(myPattern, 1, 2)) Mod 4) <> 0 Then
                myPattern = myPattern + 100
                myPattern = myPattern - 27
            Else
                myPattern = myPattern + 1
            End If
            ElseIf (Mid$(myPattern, 5, 2) = "02" And Mid$(myPattern, 7, 2) = "29") Then
                myPattern = myPattern + 100
                myPattern = myPattern - 28
            ElseIf ((Mid$(myPattern, 5, 2) = "01" Or Mid$(myPattern, 5, 2) = "03" _
                  Or Mid$(myPattern, 5, 2) = "05" Or Mid$(myPattern, 5, 2) = "07" _
                  Or Mid$(myPattern, 5, 2) = "08" Or Mid$(myPattern, 5, 2) = "10") _
                  And Mid$(myPattern, 7, 2) = "31") Then
                  myPattern = myPattern + 100
                  myPattern = myPattern - 30
            ElseIf (Mid$(myPattern, 5, 2) = "12" And Mid$(myPattern, 7, 2) = "31") Then
                myPattern = myPattern + 10000
                myPattern = myPattern - 1130
            ElseIf ((Mid$(myPattern, 5, 2) = "04" Or Mid$(myPattern, 5, 2) = "06" _
                 Or Mid$(myPattern, 5, 2) = "09" Or Mid$(myPattern, 5, 2) = "11") _
                 And Mid$(myPattern, 7, 2) = "30") Then
                myPattern = myPattern + 100
                myPattern = myPattern - 29
            Else
                myPattern = myPattern + 1
                
       End If
          
       masterCount = masterCount + 1
   Wend
   If cumulativeCount <> 0 Then
            With ExcelSheet
                       .Visible = True
                       .WindowState = -4137
                       Dim oXL As Object, oAddin As Object
                       Set cumWorkBook = ExcelSheet.Workbooks.Add
                       Dim sheet As Excel.Worksheet
                       Set oSheet = cumWorkBook.Worksheets(1)
                       oSheet.Range("A1", "A1").Value = "Date"
                       oSheet.Range("B1", "B1").Value = "Time"
                       oSheet.Range("C1", "C1").Value = "Transition Type"
                       oSheet.Range("D1", "D1").Value = "Tagname"
                       oSheet.Range("E1", "E1").Value = "Tag Value"
                       oSheet.Range("F1", "F1").Value = "Alarm Description"
                       oSheet.Range("A2").Resize(cumulativeCount, 6).Value = copyArray
                       oSheet.Rows(1).Font.Bold = True
                       For nColumn = 1 To 6
                            .Columns(nColumn).HorizontalAlignment = -4108
                            .Columns(nColumn).AutoFit
                       Next nColumn
            End With
    End If
        'cumulativeCount = cumulativeCount - 1
        MsgBox (masterCount & " day(s) were queried, " & masterCount & " record(s) were found, and " & _
                       cumulativeCount & " alarms were found.")
    
        Me.Enabled = False
        Hide
        Unload Me
    End If
End Sub

Open in new window

Comment
Watch Question

My suggestion is that you set a breakpoint at that line and add ExcelSheet to your watch list.  You should be able to verify that the ExcelSheet & Workbooks object receive values.  If they do not, you can also try setting a breakpoint where ExcelSheet gets set (at the CreateObject invocation). It may just be program flow, but sometimes there will be a problem with CreateObject which will leave the object null (although I think it actually will throw an exception).
As a side note, that surrounding with statement isn't really being utilized, so you can remove it if you think it will help you isolate the problem.

Commented:
Ensure that there is not already an occurrence of Excel running; check processes...
Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Indeed, Excelsheet was set to nothing.   It's been several months since I last worked on this, so I think this was an unfinished iteration that I had just cut and pasted from another project.  Thanks!
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.