• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1873
  • Last Modified:

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

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

0
patricio26
Asked:
patricio26
1 Solution
 
obrienslalomCommented:
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.
0
 
jmoss111Commented:
Ensure that there is not already an occurrence of Excel running; check processes...
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You're only setting ExcelSheet IF mastercount = 0 ... so it's entirely possible that your code is running correctly. You can add a check in your code to see if ExcelSheet has been built:

If Not ExcelSheet Is Nothing then
  With ExcelSheet
    blah blah
  End With
Else
  Msgbox "ExcelSheet Not built correctly"
End If
0
 
patricio26Author 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!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now