Can only open once

[GrandadsReturn]

Yes - I saw that - the difference is that you can use the MoveFirst method directly on the ListBox recordset. As the list box (called "LP") is on a form with the command button, I used

Me.LP.Recordset.MoveFirst

which fixed Spectrum's issue that I had replicated exactly.

Have you tried it?

 

[spectrum]

I did a basic database, and the Excel Application opened up every time. I then spent time taking everything off the tab control page and built a completely seperate form.

It failed. So I removed everything from the form until it was very basic, but still not working. I have attached it and hope it fails at your end. It's basically a single list with dummy companies in. Selecting one goes into the lower list. Pressing the send to Excel should open Excel with the selected company name in. Appreciate your time. Thanks again

 

[vbaInet]

Yes - I saw that - the difference is that you can use the MoveFirst method directly on the ListBox recordset. As the list box (called "LP") is on a form with the command button, I used

Me.LP.Recordset.MoveFirst

which fixed Spectrum's issue that I had replicated exactly.

Have you tried it?

Yes we're aware that you can use the MoveFirst method on the listbox object itself, but applying this MoveFirst method on LP or on rs makes no difference. They both point to the same memory location so whatever action you take on rs is directly affecting the listbox's recordset. We do it this way so that we can use Intellisense.

@spectrum: Find attached a zipped doc containing two dbs - your original db and a new db. Each dbs contain four buttons to export to Excel, try each one of them. I suspect that your db might be corrupt which was why I created a new one.

Also, you do realise that if your code halts or errors halfway through your entire function, the Excel instance will linger?

 

[spectrum]

Many thanks, and yes your database works perfectly?

I tried the following.

1) Created a new blank database, imported my suspect table/form. Did not work, halted on the line rs.movefirst (Runtime error 3420 Object invalid or no longer set) on the second attempt to fill Excel.

2) Created a new blank database, imported all the objects from your working database, again had runtime error 3420 on the second attempt to fill Excel.

My last check will be to see what happens doing the same operation on my laptop. Thanks again for all your time/effort

 

[spectrum]

Just tried my laptop. Downloaded fresh copy of your database, and on second attempt to fill Excell I got the runtime error 3420 Object invalid or no longer set, halted on line rs.movefirst.

If I take out this line, then second opening of Excel is blank.

It would have been good to have found a clear reason for the problem, ie corrupted db, corrupted Office installation, but it's up in the air as to why this is happening, checked all references etc. Think I will be a taxi driver soon

 

[GrandadsReturn]

Yes we're aware that you can use the MoveFirst method on the listbox object itself, but applying this MoveFirst method on LP or on rs makes no difference. They both point to the same memory location so whatever action you take on rs is directly affecting the listbox's recordset. We do it this way so that we can use Intellisense.

Ok - thanks - lesson for me - thought there maybe a difference between the two constructs. Why didn't I ever do a computer science course?

 

[vbaInet]

You're getting that error because the LP listbox is not populated. Also, check your references.

 

[vbaInet]

Ok - thanks - lesson for me - thought there maybe a difference between the two constructs. Why didn't I ever do a computer science course?

No problemo! You will learn a lot on this forum if you hang around.

 

[spectrum]

Many thanks for all te help. I had checked all my references, and LP listbox always appears to be filled with something. Good news is someone replicated my problems so I feel happy my db is not corrupted. They got me sorted out adding :

Set rs = CurrentDb.OpenRecordset(Me.LP.RowSource)

and putting in the line after the Dim statements:

If Me.LP.RowSource = "" Then Exit Sub

It works every time. Thanks for all your help, I hope passing this back might help someone else in same circumstances.

 

[vbaInet]

That's pretty much equivalent (within the context of your problem) to the MoveFirst command which was proposed a few posts back. There must be something wrong somewhere. It's worth finding the root cause in the end or rebuilding the form from scratch, de-compiling and compiling.

Happy developing!