asked on

Run-time error if I output to EXCEL more than once

I have a VB program that starts an EXCEL session, adds data to “Sheet1”, then subtotals column “I”. The first time I run it, it runs fine. The second time I run it, it starts an EXCEL session, adds data to “Sheet1” in the 2nd session, and I get this error when it gets to the line of code that does the subtotals:

Run-time error 1004
Method ‘Range’ of object ‘_Global’ failed.

What do I need to do to get this to run multilpe times? (Following is the code I am running.)

Set objExcel = New Excel.Application
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add
Set objworksheet = objWorkbook.Worksheets(1)
‘ Column headings
With objworksheet
.Cells(1, 1) = "Rec Date"
.Cells(1, 2) = "Loc"
.Cells(1, 3) = "Pay Type"
.Cells(1, 4) = "Payment No"
.Cells(1, 5) = "Received From"
.Cells(1, 6) = "Program/Sub Program"
.Cells(1, 7) = "Sort Code"
.Cells(1, 8) = "Rec No"
.Cells(1, 9) = "Amount Distributed"
.Cells(1, 10) = "Account"
.Cells(1, 11) = "Treas Rec No"
End With

With rsHSReceipting
.MoveFirst
introw = 2
Do While Not .EOF
objworksheet.Cells(introw, 1) = .Fields("RecDate")
objworksheet.Cells(introw, 2) = .Fields("RecLocation")
objworksheet.Cells(introw, 3) = .Fields("TypePayment")
objworksheet.Cells(introw, 4) = .Fields("PayNo")
objworksheet.Cells(introw, 5) = .Fields("RecFrom")
objworksheet.Cells(introw, 6) = .Fields("ProgramCd")
objworksheet.Cells(introw, 7) = .Fields("SortCd")
objworksheet.Cells(introw, 8) = .Fields("RRecNo")
varAmount = .Fields("AmtDisburse")
objworksheet.Cells(introw, 9) = varAmount
introw = introw + 1
.MoveNext
Loop

‘ THIS IS THE LINE THAT I GET THE ERROR ON
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(9), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End With

rsHSReceipting.Close
frmSplash.Visible = True
Unload Me
Set objworksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing

rkot2000

try to use :

objExcel.Selection

rkot2000

plus you maybe need to activate your new objworksheet
like
objworksheet.Activate

plus, sometimes you need to use range("A1:B12").select
to select data

jhartski

ASKER

Where exactly does that code go? I've tried it in a couple of places, and I still get the error.

Mike McCracken

listening

n_narayanan

Hi

change this from
Set objworksheet = objWorkbook.Worksheets(1)
to
Set objworksheet = objWorkbook.Worksheets("sheet1")

Now it will work.

Note: If you have any specific name in your XLS file Sheet1 then give that.

Narayanan.

n_narayanan

The method objworkbook.Worksheets(1) will work for the first time. Since the reference is not closed util end of your program you are getting this error.

If you hardcode your sheet name there, then this problem will be solved.

Cheers

Narayanan

jhartski

ASKER

n narayanan

I did as you said, but I still get the error message:
Run-time error 1004
Method ‘Range’ of object ‘_Global’ failed.

Any other suggestions?

ASKER CERTIFIED SOLUTION

n_narayanan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

n_narayanan

Also Check your last lines

rsHSReceipting.Close
frmSplash.Visible = True
Unload Me
Set objworksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing

move the unload me to the last line like below.

rsHSReceipting.Close
frmSplash.Visible = True
Set objworksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing
unload me

jhartski

ASKER

By using the code from the article, I was able to get it to work.

Moving the "unload me" command didn't help.

rkot2000

p.s

I had the same solution :
>>>
try to use :

objExcel.Selection

Mike McCracken

Good information.

mlmcc