Solved

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

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

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

863 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now