Solved

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

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

744 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

16 Experts available now in Live!

Get 1:1 Help Now