Excel Automation Confusion

[beckyr]

Hi i have two modules; both contain the code below to insert entries into an excel spreadsheet; this code works for both modules separately. However when i run a sub that runs both modules one after another, the 1st modules entries are put on the spreadsheet but the second modules entries are not. Can anyone tell me why this is please?

Set rs3 = db.OpenRecordset("Select [tblWorking].STU_ID, [tblWorking].STU_FORENAME, [tblWorking].STU_SURNAME, [tblWorking].STU_TU_CODE From [tblWorking] Where tblWorking.STU_TU_CODE is not null ")

Dim objXL As Excel.Application 'Modify spreadsheet that informs SIS of allocation
Dim objWkb As Object
On Error Resume Next
Set objXL = GetObject(, "Excel.Application")
Set objWkb = objXL.Application.ActiveWorkbook
Const xlDown = -4121
With objXL.ActiveSheet.Range("a1").End(xlDown).Offset(1, 0)
.CopyFromRecordset rs3
End With
If objXL Is Nothing Then
Set objXL = CreateObject("Excel.application")
Dim objws As Excel.Worksheet
Set objWkb = objXL.Workbooks.add
Set objws = objWkb.Worksheets.add
With objws
objws.Range("A1").CopyFromRecordset rs3
End With
End If

objXL.Visible = True
objXL.WindowState = xlMinimized
Set objXL = Nothing

 

[KeithG]

In the second part you are creating a new instance of excel and a new workbook.

 

[beckyr]

I thought im only creating a new instance of excel if there's not one open already-no?

 

[DJkarl]

The first thing I would do is take out the On Error Resume Next line from both modules then step through your code and see what's happening, if you can't figure out what's going wrong from that post back what error number you're getting and where.

 

[KeithG]

Im sorry your write. Is this two seperate functions? If so can you seperate them?

 

[beckyr]

Its not two separate functions but its contained in two separate functions that run one after the other. I took out the On Error Resume Next line and it then highlights
With objXL.ActiveSheet.Range("a1").End(xlDown).Offset(1, 0)
And says Application Defined or Object Defined Error; this happens in the first function

 

[DJkarl]

Its not two separate functions but its contained in two separate functions that run one after the other. I took out the On Error Resume Next line and it then highlights
With objXL.ActiveSheet.Range("a1").End(xlDown).Offset(1, 0)
And says Application Defined or Object Defined Error; this happens in the first function

Ok, I don't see that line of code in your original post...which leads me to believe this comes somewhere after your posted code. If that is the case then the problem is the line

Set objXL = Nothing

If you are trying to use the Excel object after setting it equal to nothing then that would generate the error you're describing.

 

[beckyr]

Ive changed things around a bit. Now in the first function im creating hte excel sheet and copying from my recordset into it and in the second function i am trying to get that spreadsheet and add to it.

My code looks like the following. Contained in the first function:

Set rs3 = db.OpenRecordset("Select [tblWorking].STU_ID, [tblWorking].STU_FORENAME, [tblWorking].STU_SURNAME, [tblWorking].STU_TU_CODE From [tblWorking] Where tblWorking.STU_TU_CODE is not null ")
                
    Dim exApp As Excel.Application
    Dim exBook As Excel.Workbook
    Dim exSheet As Excel.Worksheet
    Set exApp = New Excel.Application

    'Open the workbook
    Set exBook = exApp.Workbooks.add
    
    exApp.Visible = True
    
    exApp.Interactive = False

    'Instantiate the exSheet object to the specific work sheet you want to play with
    Set exSheet = exBook.Worksheets("Sheet1")
    
    exSheet.Activate
    
    If Not rs3.EOF Then
        exSheet.Range("A1").CopyFromRecordset rs3
    End If
    
    rs3.Close
    Set rs3 = Nothing
    
    Set exSheet = Nothing
    Set exBook = Nothing
    Set exApp = Nothing

And then in the second function is where i get my error.

Set rs3 = db.OpenRecordset("Select [tblWorking].STU_ID, [tblWorking].STU_FORENAME, [tblWorking].STU_SURNAME, [tblWorking].STU_TU_CODE From [tblWorking] Where tblWorking.STU_TU_CODE is not null ")
                
    Dim exApp As Excel.Application
    Dim exBook As Excel.Workbook
    Dim exSheet As Excel.Worksheet
    Const exdown = -4121
    
    Set exApp = GetObject(, "Excel.Application")

    'Open the workbook
    Set exBook = exApp.ActiveWorkbook
    
    exApp.Visible = True
    
    exApp.Interactive = False

    'Instantiate the exSheet object to the specific work sheet you want to play with
    Set exSheet = exBook.Worksheets("Sheet1")
    
    exSheet.Activate
    
    If Not rs3.EOF Then
        [COLOR="Red"][B]With exSheet.Range("A1").End(xlDown).Offset(1, 0)[/B][/COLOR]        .CopyFromRecordset rs3
        End With
    End If
    
    
    
    rs3.Close
    Set rs3 = Nothing
    
    Set exSheet = Nothing
    Set exBook = Nothing
    Set exApp = Nothing

 

[DJkarl]

Try this line instead

exSheet.Range("A1").Offset(1, 0).End(xlDown)

 

[beckyr]

I tried that. It inserted it for the first function and then an egg timer appears on the excel sheet so that eventually i have to end the process

 

[DCrake]

Simple Software Solutions

Becky

When using the CopyFromRecordset method you do not need to specify the range within the worksheet.

All you need is:-

oSheet.Range("A1") .CopyFromRecordset r3

CodeMaster:

 

[beckyr]

Im still having trouble with this; has anyone any other suggestion please, its driving me mad!

I want to insert the new data from the second recordset into the next empty cell, thats why im trying to use the end(xlDown) and offset

 

[DJkarl]

how about

dim xr as Excel.Range

exSheet.Range("A1").End(xlDown).Select

Set xr = exApp.ActiveCell.Offset(1, 0)

With xr
.CopyFromRecordset rs3
End With

 

[beckyr]

Its now highlighting

Set xr = exApp.ActiveCell.Offset(1, 0)

And saying application defined or object defined error?!?

 

[chergh]

try something like:

set xr = exapp.range("A" & exapp.range("A1").end(xldown).row + 1)

 

[DJkarl]

Its now highlighting

Set xr = exApp.ActiveCell.Offset(1, 0)

And saying application defined or object defined error?!?

How many records are you trying to insert, and how many records were inserted on your first recordset copy? If the activecell = 65536 and you try to offset by 1 you will get the error you describe.

 

[beckyr]

At the moment the first recordset only inserts 1 record and so does the second and there will never be more than two thousand altogether. Is it because
exSheet.Range("A1").End(xldown).Select
selects the last cell in the sheet? Because at the moment i can see it inserting the first recordset, then the cell 65536 is highlighted and an egg timer on the excel sheet appears and i get that error message

 

[chergh]

Yeah that would be it, instead of using

range("A1").end(xldown)

use

range("A65536").end(xlup)

 

[beckyr]

It works

Thanks both of you for all your help!