export fields to excel from access

[claven123]

I'm looking to export demographic info to excel from access. This is what I have so far, it just outputs the fullname in A8 and list them all.
I'd like to pull in the cell phone, address, email etc horizontally from A8 to say A9, A10 etc... then the loop will take over for the next record on B8 the next name and then B9 etc...

The way I have it set up now, it pulls the fullname in the loop, I can't seem to get it to pull other data. I've added it to the SQL statement, but I get the error in the loop, if I put it on the next line. The field I'm trying to add is tblMembers.CellPhone

.Range("B" & i).Value = Nz(rsTEST_ONE!tblMembers.CellPhone, "")

Dim xlApp As Excel.Application
Dim xlWkb As Excel.Workbook
Dim xlWks As Excel.Worksheet
Dim i As Integer
Dim SQLTEST_ONE As String
Dim rsTEST_ONE As DAO.Recordset
SQLTEST_ONE = "SELECT TblMembers.LastName, TblMembers.FirstName, TblMembers.Status, [FirstName] & "" "" & [LastName] AS FullName " & _
    "FROM TblMembers " & _
    "WHERE (((TblMembers.Status) = 'Active')) " & _
    "ORDER BY TblMembers.LastName, TblMembers.FirstName;"
Set rsTEST_ONE = CurrentDb.OpenRecordset(SQLTEST_ONE, dbOpenSnapshot)
Set xlApp = New Excel.Application
Set xlWkb = xlApp.Workbooks.Open(CurrentProject.Path & "\Master\TESTINGONE.xlsx")
Set xlWks = xlWkb.Sheets("Oct")
xlApp.Visible = True
i = 8
With xlWks
    Do While Not rsTEST_ONE.EOF
        .Range("A" & i).Value = Nz(rsTEST_ONE!FullName, "")
        i = i + 1
    rsTEST_ONE.MoveNext
Loop
End With

D

 

[claven123]

Anyone have any insight into this issue?

Thanks

 

[Joe4]

Personally, I find trying to control Excel from Access (or Access from Excel) quite clunky and cumbersome.
I would take a different approach to the problem. I would export the data from Access, and write an Excel macro to format the data in the manner you want.
I then just call/run the Excel macro from Access VBA code (as opposed to tried to code it all in Access VBA).

 

[claven123]

This is VBA from Access, which exports to excel.

I'm stuck on the export the data from Access (in the specific way I need it), it works with one field of one record. I need many fields of a record in the same row, then loop and do it again for all the records in the data set.


Thanks,
D

 

[Joe4]

This is VBA from Access, which exports to excel.

I know. I am saying that I would approach it a different way.
Export whatever data you need from Access to Excel using one of the built-in options (i.e. the ones you find in the Macros, which can easily be converted to VBA code).
It doesn't really matter how exactly it is exported, as long as it exports all the needed data, because you can create an Excel Macro to do whatever adjustments you need.

I'm looking to export demographic info to excel from access. This is what I have so far, it just outputs the fullname in A8 and list them all.
I'd like to pull in the cell phone, address, email etc horizontally from A8 to say A9, A10 etc... then the loop will take over for the next record on B8 the next name and then B9 etc...

Based on the above statement, and your VBA code, it just looks like you are exporting a Query. So you should be able to create that exact same query in an Access Query (doesn't need to be VBA), and just export that Query.
Why are you complicating it?