Object variable or with set variable not set. (resolved)

Using codes from marco (excel) i have use codes like in vb like :

worksheet.Range("A5:P9").Select
Selection.Copy

..

but i got the above error msg at the selection.copy ..and worst of all.sometimes it works sometimes it does not..( ie.hit the error)..what happen?

Regard
Goh

I have this example of creating a text file of an Excel sheet.

VB Code:

Option Explicit
' Set a reference to M$ Excel xx.0 Object Library
 
Private Sub Form_Load()
Dim objExcel As Object
Dim objWorkbook As Object
Dim objSheet As Excel.Worksheet
Dim ff As Integer, r As Integer, c As Integer
Dim txt As String
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open(App.Path & "\book1.xls")
Set objSheet = objExcel.Worksheets.Item(1)
 
ff = FreeFile
Open App.Path & "\Grid.txt" For Output As #ff
 
 
For r = 1 To objSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
  txt = ""
  For c = 1 To objSheet.Cells.SpecialCells(xlCellTypeLastCell).Column
    If objSheet.Cells(r, c).Value <> "" Then
      txt = txt & objSheet.Cells(r, c).Value & ", "
    End If
  Next c
  If Len(txt) > 0 Then txt = Left(txt, Len(txt) - 2)  ' remove last ", "
  Print #ff, txt
Next r
Close #ff
Set objSheet = Nothing
objWorkbook.Close
Set objWorkbook = Nothing
objExcel.Quit
Set objExcel = Nothing
Unload Me
End Sub

You need to post more of your code so we can see what is really going on. You state your using vb, but is it vb6 or vb.net, etc.?


@dglienna, whay are you using Object as a variable type when you have set a reference to excel? Your early binding AND late binding in the same project. No need, just do one or the other. ;)


Moved from Classic VB forum :)

I am using this version of coding:
..
..

Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Add
Set xlsheet = xlApp.ActiveSheet
..

is there a difference between the one you have given and this? I mean which is a better one? Whats the difference?Sorry to ask this as what i am doing now is amending codes that have been written in the past. Thus careful consideration has to be done if there is a need to use the 'object'..thanks

Regards
Alfred

Can you post the code where you posted

???

worksheet.Range("A5:P9").Select
Selection.Copy

???

Hmmm. How would you write it? I pulled that from somewhere, and modified it slightly.

@Dave:

Just like post #4.

VB Code:

Dim objExcel As Exce.Application
Dim objWorkbook As Excel.Workbook
Dim objSheet As Excel.Worksheet

VB Code:

Function LoadListInExcel() As Boolean
LoadListInExcel= True
Set rstLoadRecord = New ADODB.Recordset
Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Add
Set xlsheet = xlApp.ActiveSheet
.
.
.
Call setBorder(xlsheet)
Call setColTitleWidth(xlsheet)
End function
 
Private Sub setBorder(worksheet As Excel.worksheet)
With worksheet
.cell...
.range...
 
end with
End Function
 
 
Private Sub setColTitleWidth(worksheet As Excel.worksheet)
 
If lstr <> Trim$(rstLoadRecord![Ready]) Then
                    
 
                   'cut and paste heading
                   .Range("A5:P9").Select
                    [COLOR=Sienna]Selection.Copy[/COLOR]                
                    NextRows = CStr(NextRows + 5)
                   lstrCellCopy = "A" & NextRows
                
                   .Range(lstrCellCopy).Select
                   ActiveSheet.Paste
                   NextRows = CStr(NextRows + 5)
                
End if 
 
 
 
End Function

...sorry that i miss out those DIM ..;)..Does it got smthing to do with instance..?

I see. Thanks.

No prob. Anytime Dave :)


Goh, you have a block of code here that is not correct.

VB Code:

If lstr <> Trim$(rstLoadRecord![Ready]) Then
                    
 
                   'cut and paste heading
                   .Range("A5:P9").Select '<---WHERE IS THE WITH BLOCK?
                    Selection.Copy                
                    NextRows = CStr(NextRows + 5)
                   lstrCellCopy = "A" & NextRows
                
                   .Range(lstrCellCopy).Select '<---WHERE IS THE WITH BLOCK?
                   ActiveSheet.Paste
                   NextRows = CStr(NextRows + 5)
                
End if

And this other procedure you have multiple dots???

VB Code:

Private Sub setBorder(worksheet As Excel.worksheet)
With worksheet
.cell...  '<-- WHATS UP WITH THE "..." ?
.range...
 
end with
End Function

Hi thanks for the reply..so that i have given incomplete codes...but some how it seems there is no problem now..thank you to all :) will reflect if any more problems.

Regards
Alfred

hi guys i still got errors at times :( thanks for any suggestion.

Function LoadListInExcel() As Boolean
LoadListInExcel= True
Set rstLoadRecord = New ADODB.Recordset
Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Add
Set xlsheet = xlApp.ActiveSheet
'==================
' codes
'==================
Call setBorder(xlsheet)
Call setColTitleWidth(xlsheet)
End function

Private Sub setBorder(worksheet As Excel.worksheet)
With worksheet
' .cell ' sorry i did not complete this part of the codes as it works fine here.
' .range ' sorry i did not complete this part of the codes as it works fine here.
'==================
' codes
'==================
End with
End Function


Private Sub setColTitleWidth(worksheet As Excel.worksheet)
With worksheet
If lstr <> Trim$(rstLoadRecord![Ready]) Then


'cut and paste heading
.Range("A5:P9").Select
Selection.Copy ' this is the part the error ocurs.
NextRows = CStr(NextRows + 5)
lstrCellCopy = "A" & NextRows

.Range(lstrCellCopy).Select
ActiveSheet.Paste
NextRows = CStr(NextRows + 5)

End if

End With

End Function

Hi everyone I just found out that the error occurs only when the codes rerun the second time ie consecutively. the first time is ok. Only the second time the error hits. Is there something i need to close or set something as new ? thanks

mm..for the "selection.copy" how do i amend to avoid the error ?