Question advice needed for copy and paste (1 Viewer)

[sambucaman]

Hi everyone, hope someone may be able to help with some advice.

Im trying to find out the best way to update a xls document from Access2003, via a click button.

Basically, I need to copy a customers address, and then paste it into a xls. I dont need the xls to save, just to open and "receive" the address.

I have a "Customers form" with Name, Address1, Address2, Town, and Post Code.

Which would be the best / cleanest / easiest way to get the data from these fields to an excel, and to get them to paste into the correct cells (ie E6, E7, E8, E9, E10)

I have the following code, which copeis the text

Private Sub invoice_Click()
Dim DataObj As New MSForms.DataObject
    Dim S As String
    S = Form![Name] & " " & Form![Address1] & " " & Form![Address2] & " " & Form![Town]
    DataObj.SetText S
    DataObj.PutInClipboard
End Sub

and from here I can manually open the xls paste into it, and then separate the paste into individual cells.

BUT....there must be a cleaner, and easier way of doing this. I have never worked with linked tables or mergeing, or macro's etc. I really am a bit of a novice with Access, and VBA, and although I'm leaning all the time I'm totally stumped on this. There seems to be a plethora of information out there that I cant really work out!

Thanks in advance

 

[Beetle]

See this page by (former) forum moderator Bob Larson.