copy and paste current record only to excel (1 Viewer)

[sambucaman]

Hi guys

Its been a few years since I used Access, and despite searches I cant find what I'm looking for. I have a VERY simple form, with only one record on it - "address". I have some print buttons which will print that address to different size labels, and some navigation items, but only one actual record - which is "address"
(in the table, this address is a MEMO)

I would like to have a command button that when clicked copies the current record, and opens an existing Excel Spreadsheet and pastes that address into (for example) cell C8

My questions are

Can it be done easily?
Can someone help?

Is there a DoCmd.TransferSpreadsheet kind of secnario that works with current record only?

Many thanks in advace - if you guys are stilla s helpful as you were several years ago I know I'll be ok!

 

[pbaldy]

You can use automation:

http://support.microsoft.com/kb/210288

 

[vbaInet]

And there are some more examples here:
http://www.btabdevelopment.com/ts/exportformrecordset

 

[sambucaman]

Thank you both so much!
The first example shown works fine aprat from the fact Excel doesnt show (as explained in KB it only shows when Excel is open prior to the command being run. There is no way to see if things have worked apart from opening the xls to check - the whole point was this would open the xls, paste the address and let me do some more work on the xls.
My code is now

Private Sub Command37_Click()

Dim mysheet As Object, myfield As Variant, xlApp As Object

Set xlApp = CreateObject("Excel.Application")
Set mysheet = xlApp.workbooks.Open("c:\users\andy\desktop\test.xls").Sheets(1)

myfield = Me!Address
mysheet.cells(8, 1).Value = myfield

End Sub

Is there anything I (you!) can add to get excel to open and stay open while I work on it?

Many thanks again

Andy

 

[vbaInet]

Make the xlApp object Visible and set the UserControl to True.

 

[sambucaman]

Was about to reply that I used

xlApp.Visible = True

in the command;

That seems to have done the trick - thanks so much

Regards

Andy

 

[vbaInet]

I did also say you should also set the UserControl to True.

 

[sambucaman]

I did also say you should also set the UserControl to True.

Yes, I know thank you. I had already fixed it before I saw your reply, but thanks

 

[vbaInet]

No problem!