Can only open once

[spectrum]

Can someone tell me why I can only fill an Excel sheet once. If I run the code again it just opens a blank xls document. I have to close Access to make it work again. Thanks

Dim oXLApp As Excel.Application
Dim oXLBook As Excel.Workbook
Dim oXLSheet As Excel.Worksheet

Set oXLApp = New Excel.Application
Set oXLBook = oXLApp.Workbooks.Add
Set oXLSheet = oXLBook.Worksheets(1)
oXLApp.Visible = True
oXLSheet.Range("B2").CopyFromRecordset LP.Recordset

Set oXLBook = Nothing
Set oXLApp = Nothing
Set oXLSheet = Nothing

 

[spectrum]

Thanks Richard. The code shown is on a command button, so I would have thought it would have gone through the same process again? But it just makes an Excel Application open showing nothing being entered in the page?

So I need to know why it's not reusable code

 

[vbaInet]

Return control to the user:

    Dim oXLApp As Excel.Application
    Dim oXLBook As Excel.Workbook
    Dim oXLSheet As Excel.Worksheet
    
    Set oXLApp = New Excel.Application
    Set oXLBook = oXLApp.Workbooks.Add
    Set oXLSheet = oXLBook.Worksheets(1)

    oXLSheet.Range("B2").CopyFromRecordset LP.Recordset
    oXLApp.Visible = True
[COLOR=Blue]    oXLApp.UserControl = True[/COLOR]
    
    Set oXLBook = Nothing
    Set oXLApp = Nothing
    Set oXLSheet = Nothing

Also noticed I populated the spreadsheet before making it visible. It's more logical that way.

 

[spectrum]

Thanks for your help, however still does not work. If I fill a listbox on a form (LP) with selected records and click the command button with the code above, the Excel application opens up and is filled. If I then manually close Excel nd repeat the operation by refilling listbox LP and clicking the command button, Excell opens up but is blank. Thanks

 

[vbaInet]

Can you upload a sample database we can use to reproduce your problem?

 

[spectrum]

Thanks again. It would be a problem to chop out the relevant part of the database, it also may be a problem in my Excell2010?? . I will have to look around for other examples of how to send a recordset dynamically to Excel, allowing me to open Excel, print it, close it and then send another recordset to Excel which would work the second and future times.

For the future I need to find how/where to upload things to the forum.

Regards

 

[vbaInet]

Alright, I see what you're up against.

Try this:

    Dim rs As DAO.Recordset
    Dim oXLApp As Excel.Application
    Dim oXLBook As Excel.Workbook
    Dim oXLSheet As Excel.Worksheet
    
    Set oXLApp = New Excel.Application
    Set oXLBook = oXLApp.Workbooks.Add
    Set oXLSheet = oXLBook.ActiveSheet

    Set rs = LP.RecordsetClone
    rs.MoveFirst

    oXLSheet.Range("B2").CopyFromRecordset rs
    oXLApp.Visible = True
    oXLApp.UserControl = True
    
    Set oXLBook = Nothing
    Set oXLApp = Nothing
    Set oXLSheet = Nothing

 

[spectrum]

Thanks, but still not working. It did not like RecordsetClone, Method or Datamember not found.

I appreciate your time, and working in the blind. I think I must try and see what happens when the database is closed and reopens, something remaining open. Just hope it's not one more of many bugs I have found with Access 2010.

Sorry it's long, and a bit primitive in parts, but maybe I will bore you with the code that fills the listbox LP in case you can see what gets left open.

Public Sub BuildList()
Dim MySql As String, i As Integer, strIN As String
On Error GoTo BuildList_Error

Me.LP = Null
If Me.PLIST > "" Or Me.TLIST > "" Or Me.STLIST > "" Or Me.RQLIST > "" Or Me.DTLIST > "" Or Me.DULIST > "" Then
MySql = "SELECT PARTNERS.Partner, ACTIVITIES.Type, ACTIVITIES.Status, ACTIVITIES.RequestedBy, ACTIVITIES.[Date Raised],"
MySql = MySql & " PROGRESS.[Updated On], ACTIVITIES.Identifier1, ACTIVITIES.[Task Comments],"
MySql = MySql & " ACTIVITIES.Resources, PROGRESS.Update, ACTIVITIES.[Task Description], PARTNERS.ID1, ACTIVITIES.ID3 FROM (PARTNERS LEFT JOIN ACTIVITIES ON"
MySql = MySql & " PARTNERS.ID1 = ACTIVITIES.ID1) LEFT JOIN PROGRESS ON ACTIVITIES.ID3 = PROGRESS.ID3"
MySql = MySql & " WHERE 1 = 1 "


If STRLIST1 > "" Then
MySql = MySql & " AND ACTIVITIES.[Date Raised] IN (" & (STRLIST1) & ")"
End If
Else
Me.LP.RowSource = ""

If WHATLIST = 0 Then
Me.DTLIST.RowSource = ""
End If

If WHATLIST2 = 0 Then
Me.DULIST.RowSource = ""
End If

Me.LP.Visible = False
Me.Command267.Enabled = True
Me.Command269.Enabled = True
Me.Label316.Visible = False
Me.NF.Visible = True

' Now need to refill date raised list

If MySql > "" Then
Me.DTLIST.RowSource = MySql
Else
MySql = "SELECT PARTNERS.Partner, ACTIVITIES.Type, ACTIVITIES.Status, ACTIVITIES.RequestedBy, ACTIVITIES.[Date Raised],"
MySql = MySql & " PROGRESS.[Updated On], ACTIVITIES.Identifier1, ACTIVITIES.[Task Comments],"
MySql = MySql & " ACTIVITIES.Resources, PROGRESS.Update, ACTIVITIES.[Task Description], PARTNERS.ID1, ACTIVITIES.ID3 FROM (PARTNERS LEFT JOIN ACTIVITIES ON"
MySql = MySql & " PARTNERS.ID1 = ACTIVITIES.ID1) LEFT JOIN PROGRESS ON ACTIVITIES.ID3 = PROGRESS.ID3"
MySql = MySql & " WHERE ((Not (ACTIVITIES.[Date Raised]) Is Null))"
MySql = MySql & " ORDER BY ACTIVITIES.[Date Raised], PROGRESS.[Updated On];"
Me.DTLIST.RowSource = MySql
End If
Exit Sub
End If

' Selected Partner
If [Forms]![TABBEDFORM]![PLIST] <> "" Then
MySql = MySql & " AND PARTNERS.Partner = '" & [Forms]![TABBEDFORM]![PLIST] & "'"
End If

' Selected Type
If [Forms]![TABBEDFORM]![TLIST] <> "" Then
MySql = MySql & " AND ACTIVITIES.Type = '" & [Forms]![TABBEDFORM]![TLIST] & "'"
End If

' Selected Status
If [Forms]![TABBEDFORM]![STLIST] <> "" Then
MySql = MySql & " AND ACTIVITIES.Status = '" & [Forms]![TABBEDFORM]![STLIST] & "'"
End If

' Selected Requested By
If [Forms]![TABBEDFORM]![RQLIST] <> "" Then
MySql = MySql & " AND ACTIVITIES.RequestedBy = '" & [Forms]![TABBEDFORM]![RQLIST] & "'"
End If

' Selected Date Raised from
If [Forms]![TABBEDFORM]![DTLIST] <> "" Then
MySql = MySql & " AND ACTIVITIES.[Date Raised] >= #" & Format([Forms]![TABBEDFORM]![DTLIST], "yyyy/mm/dd") & "#"
End If

' Selected Date Updated from
If [Forms]![TABBEDFORM]![DULIST] <> "" Then
MySql = MySql & " AND PROGRESS.[Updated On] >= #" & Format([Forms]![TABBEDFORM]![DULIST], "yyyy/mm/dd") & "#"
End If

MySql = MySql & " ORDER BY ACTIVITIES.[Date Raised], PROGRESS.[Updated On];"

Me.LP.RowSource = MySql

If WHATLIST = 0 Then
Me.DTLIST.RowSource = MySql
End If

If WHATLIST2 = 0 Then
Me.DULIST.RowSource = MySql
End If

If Me.LP.ListCount > 0 Then
Me.LP.Visible = True
Me.Command267.Enabled = False
Me.Command269.Enabled = False
Me.Label316.Visible = True
Else
Me.LP.Visible = False
Me.Command267.Enabled = True
Me.Command269.Enabled = True
Me.Label316.Visible = False
End If

On Error GoTo 0
Exit Sub
BuildList_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure BuildList of VBA Document Form_TabbedForm"
End Sub

 

[vbaInet]

I find it difficult to read code in that format. In the future, when you want to post code enclose it in Code tags so that the indents are preserved. Here's how:

http://www.access-programmers.co.uk/forums/showthread.php?p=1009015#post1009015

I thought that LP was the form itself. If LP is the listbox simply change RecordsetClone to Recordset. Everything else remains the same.

 

[spectrum]

Thanks, sorry I did go in afterwards to edit the code to make it look better. Yes when loaded in the forum page it did look difficult. Thanks for the link I will read it.

I did try the code without the Clone part, but Excel still opens up the second time empty.

 

[vbaInet]

Just create a blank database, create one table, one form + the listbox, amend the SQL string in your code then test it. If it doesn't work, upload that stripped down version on here and I will have a look. I just tested what was suggested in my previous post and it works for me.

Here's how to upload a database onto the form:

http://www.access-programmers.co.uk/forums/showthread.php?t=140587

 

[spectrum]

Thanks for the information. I will try and make something to send.

 

[spectrum]

I just made a form with a single list box, a button to derive SQL to fill the list, and a button to open Excel, and it opens and closes every time!!

So something is staying open in my database. I will have to spend time looking to see why, will take some effort as a mutitab control with muliple forms are on, although when I open the database I am only going to the query by form form with the code as posted. Thanks for all your help, it's down to me to fix. Thanks for your suggestion to try out another way. Best regards

 

[vbaInet]

CopyFromRecordset is an effecient way of doing it. Let me know when you can throw up a test db.

 

[spectrum]

Did you see my last posting? I did a basic DB and your coding worked perfectly, proving there is something staying open in my main database. I don't know what it can be as although there are many forms on a tab control, only 1 form gets focus with what I am trying to make work (query by form)

 

[vbaInet]

Alright, I didn't understand your post to mean that it was working with a single form.

So, I would imagine that you have some other code somewhere that is keeping an instance open. If you're making changes to an existing workbook remember to Close it before setting the object to Nothing.

 

[GrandadsReturn]

Hi - I've just joined so can't post links yet but have a look at support dot microsoft dot com/kb/178510
The way you reference the Range in the Worksheet uses a different syntax to the example in the link and you don't mention an error being raised but - in case it helps...

 

[vbaInet]

Hi - I've just joined so can't post links yet but have a look at support dot microsoft dot com/kb/178510
The way you reference the Range in the Worksheet uses a different syntax to the example in the link and you don't mention an error being raised but - in case it helps...

Welcome to the forum GrandadsReturn!

Thanks for the link. The way the Range is specified in spectrum's code is perfectly valid. It has nothing to do with that.

 

[GrandadsReturn]

Thanks for the welcome

A fix follows:

The recordset is held in memory while the form is open - after first time the "CopyFromRecordset" method is fired, the cursor will be positioned at the end of the recordset. To reposition the cursor, add the following line of code before the "CopyFromRecordset" line:

"LP.Recordset.MoveFirst"

I found this works every time.

 

[vbaInet]

We've already been doing that since post #8.