Solved

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

Posted on 2009-05-05
4
1,847 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

0
Comment
Question by:patricio26
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 6

Expert Comment

by:obrienslalom
ID: 24309153
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
 
LVL 18

Expert Comment

by:jmoss111
ID: 24310071
Ensure that there is not already an occurrence of Excel running; check processes...
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 24313089
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
 

Author Closing Comment

by:patricio26
ID: 31578213
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

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question