export fields to excel from access

claven123

Board Regular
Joined
Sep 2, 2010
Messages
63
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, "")





Code:
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
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,465
Office Version
365
Platform
Windows
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

Board Regular
Joined
Sep 2, 2010
Messages
63
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,465
Office Version
365
Platform
Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,099,696
Messages
5,470,213
Members
406,685
Latest member
711excelhelp

This Week's Hot Topics

Top