Hi everyone. This is my first post on here, so before I begin, hello, and thanks in advance!
I am struggling with some VBA, which is sooooo nearly working, and I'd be really grateful of some help.
I have a form, which displays a single customers information; Name, Address, Post Code, order history etc etc. I'm trying to run some VBA that will pull the name and address info, and copy it into a xls document. The code I am using is shown below.
Code:
Private Sub invoice_Click()
Dim lngColumn As Long
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim blnEXCEL As Boolean, blnHeaderRow As Boolean
blnEXCEL = False
blnHeaderRow = False
On Error Resume Next
Set xlx = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set xlx = CreateObject("Excel.Application")
blnEXCEL = True
End If
Err.Clear
On Error GoTo 0
xlx.Visible = True
Set xlw = xlx.Workbooks.Open("C:\users\admin\desktop\invoice.xls")
Set xls = xlw.Worksheets("Invoice")
Set xlc = xls.Range("E2")
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("Customers", dbOpenDynaset, dbReadOnly)
If rst.EOF = False And rst.BOF = False Then
rst.MoveFirst
If blnHeaderRow = True Then
For lngColumn = 0 To rst.Fields.Count - 1
xlc.Offset(0, lngColumn).Value = rst.Fields(lngColumn).Name
Next lngColumn
Set xlc = xlc.Offset(1, 0)
End If
Do While rst.EOF = False
For lngColumn = 0 To rst.Fields.Count - 1
xlc.Offset(0, lngColumn).Value = rst.Fields(lngColumn).Value
Next lngColumn
rst.MoveNext
Set xlc = xlc.Offset(1, 0)
Loop
End If
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
End Sub
Now, this works in as much as it opens the XLS, copies and pastes the info. BUT it copies ALL my customer records. How could I re-gig it so that It pulls the data from the loaded form, and not the table?
I have tried this
Code:
Private Sub Command11_Click()
Dim DataObj As New MSForms.DataObject
Dim S As String
S = Me.Customer.Name
DataObj.SetText S
DataObj.PutInClipboard
End Sub
which will get loaded form data, but I cant seem to combine the two.
Any help on the above would be really gratefully received.
Many thanks in advance, Andy