CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Page 1 of 3 123 LastLast
Results 1 to 15 of 34
  1. #1
    Join Date
    Feb 2006
    Posts
    153

    Object Variable or with block variable not set

    Hi all,

    I got a very weird scenario here, i am using VBA in my MS access forms to do automation to export a table to exceln run a excel macro from MS access. however, the 1st time i open the db n run this form, it works. The 2nd time, it fails, then i need to go to VB editor n acknowledge the error shown on the thread title, after which, the code will run smoothy infinitly... i cannot tell where is my error.. Can anyone pt out my mistake?

    Code:
    Private Sub Export_Button_Click() 'export
    
    On Error GoTo Err_Export_Button_Click
        Dim objExcel As Excel.Application
        Dim objBook As Excel.Workbook
        Dim partName As String
        Dim langName As String
        Dim tempPath As String
        Dim strLen As String
        Dim userInput As String
        Dim userPath As String
        Dim blankPic As String
        Dim codeLength As String
        Dim tempStr As String
        Dim tempLength As Integer 'counts length of code
        Dim xLoop, yLoop, codeCounter, i As Integer
    
        
         
        
        codeCounter = 1 'counts the total no of codeword for a particular language set
        tempLength = 0
        
        
        langName = ""
           
                  
        If IsNull(Me![ListInput]) Or (Me![ListInput]) = "" Then 'checks if user has selected any language
            MsgBox "Please select one of the available languages.", vbExclamation
            Exit Sub
        End If
        
        
        If IsNull(Me![tbFile]) Or (Me![tbFile]) = "" Then   'checks for empty export path
            MsgBox "Please enter the destination where the output file will be saved!", vbExclamation, "Empty Path"
            Me![tbFile].SetFocus
            Exit Sub
        End If
        
        
       Select Case ListInput  'assign the filename based on the listbox input
        
       Case "Malay"
       langName = "Malay"
       
       Case "Chinese Simplified"
       langName = "CH Simplified"
       
     
       
       End Select
    
       If Right(tbFile, 3) <> "xls" Then
            MsgBox "File to be exported has to be of .xls extension.", vbExclamation
            Exit Sub
        End If
       
       
      ' On Error GoTo Err_Export_Button_Click
       DoCmd.OutputTo acOutputQuery, langName & " Q", acFormatXLS, tbFile, False
      
        MsgBox langName & " successfully exported to " & tbFile & ". Conversion of codes will now begin in the background. Depending on the file size, this process may take a few minutes.", vbInformation, "Exporting of File Completed"
       ' DoCmd.Hourglass (True)
        
        
            Set objBook = Nothing
            Set objExcel = Nothing
         
        
        Set objExcel = CreateObject("Excel.Application")
        Set objBook = objExcel.Workbooks.Open(tbFile)
      
       
        objExcel.Cells.Select
        objExcel.Selection.RowHeight = 21.01
        objExcel.Selection.ColumnWidth = 4.57
        objExcel.Columns("B:B").EntireColumn.AutoFit
        
        
        strLen = Len(objExcel.Cells(2, 3).Value)
        tempStr = Left(objExcel.Cells(2, 3).Value, strLen - 6)
        
        
        xLoop = 1  'start counting the headings
        yLoop = 3
        
        While objExcel.Cells(xLoop, yLoop).Value <> ""
            tempLength = tempLength + 1
            yLoop = yLoop + 1
        Wend
        
        
        blankPic = tempStr & "00.JPG"
        
        codeLength = tempLength
        
        xLoop = 2 'start from Cell(2,3)
        yLoop = 3
        
        For xLoop = 2 To 344
            For yLoop = 3 To codeLength + 2
                If objExcel.Cells(xLoop, yLoop).Value <> blankPic Then
                objExcel.Cells(xLoop, yLoop).Select
           'changed
                objBook.ActiveSheet.Pictures.Insert(ActiveCell.FormulaR1C1). _
                Select
              
                End If
            
            Next
            If objExcel.Cells(xLoop + 1, 1).Value = "" Then
                GoTo Proceed
            End If
        Next
        
    Proceed:
            objExcel.Range("C2:U343").Select
            objExcel.Range("U2").Activate
            objExcel.ActiveWindow.SmallScroll Down:=-15
            objExcel.Application.CutCopyMode = False
            objExcel.Selection.ClearContents
        
         
            For yLoop = 3 To codeLength + 2 'create row headings for 1st row
                objExcel.Cells(1, yLoop).Value = codeCounter
                codeCounter = codeCounter + 1
                
                objExcel.Cells(1, yLoop).Select
                 With objExcel.Selection
                .HorizontalAlignment = xlGeneral
                .VerticalAlignment = xlCenter
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .ShrinkToFit = False
                .MergeCells = False
            End With
            objExcel.Selection.Font.Bold = True
            With Selection.Font
                .Name = "MS Sans Serif"
                .Size = 12
                .Strikethrough = False
                .Superscript = False
                .Subscript = False
                .OutlineFont = False
                .Shadow = False
                .Underline = xlUnderlineStyleNone
                .ColorIndex = xlAutomatic
            End With
            With objExcel.Selection
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .ShrinkToFit = False
                .MergeCells = False
            End With
            
            Next
            
            objExcel.Range("A1:B1").Select 'create the heading for 1st row
            With objExcel.Selection
                .HorizontalAlignment = xlGeneral
                .VerticalAlignment = xlCenter
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .ShrinkToFit = False
                .MergeCells = False
            End With
            objExcel.Selection.Font.Bold = True
            With Selection.Font
                .Name = "MS Sans Serif"
                .Size = 16
                .Strikethrough = False
                .Superscript = False
                .Subscript = False
                .OutlineFont = False
                .Shadow = False
                .Underline = xlUnderlineStyleNone
                .ColorIndex = xlAutomatic
            End With
            With objExcel.Selection
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .ShrinkToFit = False
                .MergeCells = False
            End With
            objExcel.ActiveWorkbook.Save 'saves the file after conversion
          
            
                 objExcel.Workbooks.Close
          
                 Set objBook = Nothing
                 objExcel.Quit
                 Set objExcel = Nothing
            
          '  DoCmd.Hourglass (False)
            
            MsgBox langName & " has been successfully converted to codes in " & tbFile, vbInformation, "Conversion Successfully"
            
    Exit_Export_Button_Click:
            Exit Sub
        
    Err_Export_Button_Click:
            objExcel.Workbooks.Close
            Set objBook = Nothing
            objExcel.Quit
            Set objExcel = Nothing
            'DoCmd.Hourglass (False)
            MsgBox "Error - " & Err.Description, vbExclamation
            Resume Exit_Export_Button_Click
        
    End Sub
    i been trying to figure this out since yest, can anyone pls guide me along? thks

    FT
    Last edited by fulltime; April 8th, 2006 at 10:00 PM.

  2. #2
    Join Date
    Sep 2001
    Location
    Québec, Canada
    Posts
    1,923

    Re: Object Variable or with block variable not set

    Which line give the error? This error happens when you try to use an object that was not initialised, for example, if you would try to use objExcel without using the line Set objExcel = CreateObject("Excel.Application").

    JeffB
    CodeGuru VB FAQ Visual Basic Frequently Asked Questions
    VB Code color Tool to color your VB code on CodeGuru
    Before you post Importants informations to know before posting

  3. #3
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: Object Variable or with block variable not set

    You dim as an Excel App, then set it to nothing, and then set it to an Excel App.

    I'd dim as Objects instead, then you can set them to nothing each time.

    Otherwise, create them when the app opens, and close them when it's finished and unloading.
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  4. #4
    Join Date
    Feb 2006
    Posts
    153

    Re: Object Variable or with block variable not set

    Quote Originally Posted by JeffB
    Which line give the error? This error happens when you try to use an object that was not initialised, for example, if you would try to use objExcel without using the line Set objExcel = CreateObject("Excel.Application").

    JeffB

    hi jeff,

    sorry, i forgot to indicate which line induced the error... I have bolded it in the codes above..

    this is the line..
    objBook.ActiveSheet.Pictures.Insert(ActiveCell.FormulaR1C1). _
    Select


    i did initialised the object as an workbook , so i was wonmdering why the error will occur onli on the 2nd time of executiomn, thereafter, no nore errors will occur?

    thks

  5. #5
    Join Date
    Feb 2006
    Posts
    153

    Re: Object Variable or with block variable not set

    Quote Originally Posted by dglienna
    You dim as an Excel App, then set it to nothing, and then set it to an Excel App.

    I'd dim as Objects instead, then you can set them to nothing each time.

    Otherwise, create them when the app opens, and close them when it's finished and unloading.
    hi dglienna,

    care to elaborate on this statement, Otherwise, create them when the app opens, and close them when it's finished and unloading.??

    how do i open the application? n close?

    thks

  6. #6
    Join Date
    Feb 2006
    Posts
    153

    Re: Object Variable or with block variable not set

    Quote Originally Posted by dglienna
    You dim as an Excel App, then set it to nothing, and then set it to an Excel App.

    I'd dim as Objects instead, then you can set them to nothing each time.

    Otherwise, create them when the app opens, and close them when it's finished and unloading.
    i tried w ur suggestions,

    Dim objExcel As Object
    Dim objBook As Object

    without making changes to the rest of the codes, but it still gives me the same error..

  7. #7
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: Object Variable or with block variable not set

    Use DIM in a module, and make tham PUBLIC. Sorry if I forgot to include that.
    Just DIM them once, open the connection once, and close it once. You can open and close WorkVBooks in between.

    In Module1.bas
    Code:
    Public objExcel As Object
    Public objBook As Object
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  8. #8
    Join Date
    Feb 2006
    Posts
    153

    Re: Object Variable or with block variable not set

    Quote Originally Posted by dglienna
    Use DIM in a module, and make tham PUBLIC. Sorry if I forgot to include that.
    Just DIM them once, open the connection once, and close it once. You can open and close WorkVBooks in between.

    In Module1.bas
    Code:
    Public objExcel As Object
    Public objBook As Object

    So u mean i have a seperate module, just to DIM these 2 variables?
    just add the 2 lines of codes u shown above in a new module?


    Thks, i will try it out and let u know if it works..

  9. #9
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: Object Variable or with block variable not set

    I was working on a mailmerge from within VB. I found that I had the same problem if we ran the same report a second time. It was suggested that I open the connection once (to MsWord) and keep it opened thruout the program.
    Not only did that resolve my error, but it kept multiple processes of MsWord from opening. Not to mention, but Word wasn't running when my app shut down.
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  10. #10
    Join Date
    Feb 2006
    Posts
    153

    Re: Object Variable or with block variable not set

    Quote Originally Posted by dglienna
    I was working on a mailmerge from within VB. I found that I had the same problem if we ran the same report a second time. It was suggested that I open the connection once (to MsWord) and keep it opened thruout the program.
    Not only did that resolve my error, but it kept multiple processes of MsWord from opening. Not to mention, but Word wasn't running when my app shut down.
    oh, so i open MS Excel once, n i onli close it when i wan to exit my access database? is tat wat u mean?

  11. #11
    Join Date
    Feb 2006
    Posts
    153

    Re: Object Variable or with block variable not set

    Quote Originally Posted by dglienna
    I was working on a mailmerge from within VB. I found that I had the same problem if we ran the same report a second time. It was suggested that I open the connection once (to MsWord) and keep it opened thruout the program.
    Not only did that resolve my error, but it kept multiple processes of MsWord from opening. Not to mention, but Word wasn't running when my app shut down.
    hi,

    sorry if i am requesting for too much, but i tried wat u mentioned, and it still wun work the 2nd time.. isit possible for u to put wat u mean down in codes?? cos i am still relatively new to VB, so i dun realli understand wat u mean by the term connection.. thks

    FT

  12. #12
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: Object Variable or with block variable not set

    Only open Excel once, and Access once. Use one object for each.

    Just don't close them, and re-open tham. You can check for errors the first time, and if they open, than you can keep using them with different objects, which you can close, destroy, and re-use.
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  13. #13
    Join Date
    Feb 2006
    Posts
    153

    Re: Object Variable or with block variable not set

    Quote Originally Posted by dglienna
    Only open Excel once, and Access once. Use one object for each.

    Just don't close them, and re-open tham. You can check for errors the first time, and if they open, than you can keep using them with different objects, which you can close, destroy, and re-use.
    but i am using ACCESS to call the spreadsheet in EXCEL...

    Dim objExcel As Object
    Dim objBook As Object

    Set objExcel = CreateObject("Excel.Application")
    Set objBook = objExcel.Workbooks.Open(tbFile)

    [b] macro codes here[b]


    objExcel.Workbooks.Close
    Set objBook = Nothing
    objExcel.Quit <---------- u mean dun do this?
    Set objExcel = Nothing <---- how abt this?

  14. #14
    Join Date
    Feb 2006
    Posts
    153

    Re: Object Variable or with block variable not set

    hi, now my program can run smoothly without any errors, but there seems to be someting wrong with my objBook variable, as its supposed to insert a picture based on the forrmula in the cell, but it always inserts the same pic even though the formula in the cell varies, i suspect there is something wrong with the way i initialise or destroy the objBook object? can u advise me?

    objBook.ActiveSheet.Pictures.Insert(ActiveCell.FormulaR1C1). _
    Select

  15. #15
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: Object Variable or with block variable not set

    I think you can close and destroy the objBook to solve that problem. Just leave the Excel object open.

    Don't use the QUIT to exit Excel, until the program ends, so you don't open multiple instances of it.
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

Page 1 of 3 123 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured