Error #91 Object Variable or Block Variable not set

[pd2004]

Hello,

I am using VBA from MS Access to create an instance of Excel and of Word, and then to copy and paste from Excel into Word. When I execute, the first time it runs properly, the second time I get this error message:

Run Time Error #91
Object Variable or With Block Variable not Set.

here is the code where it hangs:

appExcel.ActiveWorkbook.Worksheets.Add
appExcel.ActiveSheet.Name = "Ticket_format1"
appExcel.Worksheets("Receipt_Ticket").Activate
appExcel.Range("a1").Select
rowcnt_excel_1 = appExcel.ActiveCell.CurrentRegion.Rows.Count - 1
colcnt_excel_1 = appExcel.ActiveCell.CurrentRegion.Columns.Count - 1


'trouble area below
'name321 = appExcel.ActiveSheet.Name

appExcel.CutCopyMode = False

appExcel.Workbooks(bookname_excel_1).Sheets("Receipt_Ticket").Range(ActiveCell.Address, ActiveCell.Offset(rowcnt_excel_1, colcnt_excel_1).Address).Select




here is the line that it hangs up on:

'appExcel.ActiveSheet.Range(ActiveCell.Address, ActiveCell.Offset(rowcnt_excel_1, colcnt_excel_1).Address).Select


appExcel.Selection.Copy


I have looked at all of the resources, and I don't understand what is going on. I have made sure that there are no other instances of excel or word open (just manually, not programatically). Any help is greatly appreciated!!

Thank you to all. This forum has been very helpful to me in the past when I hit a wall. Thank you!!!!

PS... I would like to understand why it runs once, but will not run again without error.

Pat

 

[PHV]

Replace this:
appExcel.Workbooks(bookname_excel_1).Sheets("Receipt_Ticket").Range(ActiveCell.Address, ActiveCell.Offset(rowcnt_excel_1, colcnt_excel_1).Address).Select
with this:
With appExcel.Workbooks(bookname_excel_1).Sheets("Receipt_Ticket")
.Range(.ActiveCell.Address, .ActiveCell.Offset(rowcnt_excel_1, colcnt_excel_1).Address).Select
End With

And this:
appExcel.ActiveSheet.Range(ActiveCell.Address, ActiveCell.Offset(rowcnt_excel_1, colcnt_excel_1).Address).Select
with this:
With appExcel.ActiveSheet
.Range(.ActiveCell.Address, .ActiveCell.Offset(rowcnt_excel_1, colcnt_excel_1).Address).Select
End With

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

hi,

Avoid ActiveWHATEVER. Use Explicit references.

    With appExcel
        .ActiveWorkbook.Worksheets.Add
        .ActiveSheet.Name = "Ticket_format1"
        With .Worksheets("Receipt_Ticket")
            
            rowcnt_excel_1 = .Range("a1").CurrentRegion.Rows.Count - 1
            colcnt_excel_1 = .Range("a1").CurrentRegion.Columns.Count - 1
        
        
            appExcel.CutCopyMode = False
        
            appExcel.Workbooks(bookname_excel_1).Sheets("Receipt_Ticket").Range(.Range("a1"), .Range("a1").Offset(rowcnt_excel_1, colcnt_excel_1)).Copy

'don't WANT the address property here
            .Range(.Range("a1"), .Range("a1").Offset(rowcnt_excel_1, colcnt_excel_1)).Copy
        End With
    End With

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[pd2004]

Hello and thank you for all of your help, everyone! Please see below:

Explicit References: I always want to use explicit references, but in this case, I am looping through columns on the spreadsheet, so the cell changes each iteration. I know there are ways to handle, but this seemed the most reasonable under the circumstances. Let me know if you think it is ever OK to handle this way. thank you.

On the following code:
Replace:
'appExcel.Workbooks(bookname_excel_1).Sheets("Receipt_Ticket").Range(ActiveCell.Address, ActiveCell.Offset(rowcnt_excel_1, colcnt_excel_1).Address).Select
'With this:
With appExcel.Workbooks(bookname_excel_1).Sheets("Receipt_Ticket")
.Range(.ActiveCell.Address, .ActiveCell.Offset(rowcnt_excel_1, colcnt_excel_1).Address).Select
End With

I get the following error message:

"runtime error #438, object doesn't support this property or method.


I will be working through the examples provided and others that may come over the weekend, but please let me know if you see an error in my ways that you can point out.

Thank you,

Pat

 

[SkipVought]

is this workbook

appExcel.Workbooks(bookname_excel_1)

different than the workbook that you originally opened?

Also you don't show where you open everything up, CLOSE and KILL the applications and CLEAN UP the objects.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PHV]

Sorry:
.Range(.ActiveCell, .ActiveCell.Offset(rowcnt_excel_1, colcnt_excel_1)).Select

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[pd2004]

Could you tell me the proper way I should be killing the instances of the excel and word apps? I think that is where part of my problem lies.

Thank you,

Pat