asked on

Export a listbox containing field captions to excel

Hello, I have a database where there is a listbox (lstFields) whose record source is qryAll. I've attached the code for building this listbox and also the database to this question, but basically the list box is using captions rather than field names. I have a VBA script that exports the selections to word (it used to when the field names were present but now that I've changed the selections to captions I can't seem to get it to work) and I would like to allow users to do the same for an excel spreadsheet.
Could anyone show me an example of how to get the selected fields w/ caption property shown to export?

Thank you,
Bevo S.

Option Compare Database

Private Sub Command0_Click()
BuildValueList ("qryAll")
End Sub

Public Function BuildValueList(TableName As String)
On Error GoTo myerror
Dim FinalString As String
Dim db As Database
Dim rs As Recordset
Dim myfield As Field
Set db = CurrentDb
Set rs = db.OpenRecordset("Select * from " & TableName & " where 1 = 2;", dbOpenDynaset, dbSeeChanges)

For Each myfield In rs.Fields
    FinalString = FinalString & Nz(myfield.Properties("Caption"), "no caption") & ";"
Next myfield

Me.lstFields.RowSource = FinalString

Exit Function

myerror:
If Err.Number = 3270 Then 'no existing caption
    FinalString = FinalString & myfield.Name & ";" & "no caption" & ";"
    Resume Next
End If

End Function

Open in new window

Rey Obrero (Capricorn1)

try this revision

Public Function BuildValueList(TableName As String)
On Error GoTo myerror
Dim FinalString As String
Dim db As Database
Dim rs As Recordset
Dim myfield As Field
Set db = CurrentDb
Set rs = db.OpenRecordset("Select * from " & TableName & " where 1 = 2;", dbOpenDynaset, dbSeeChanges)

For Each myfield In rs.Fields
    FinalString = FinalString & myfield.Name & ";" & Nz(myfield.Properties("Caption"), "no caption") & ";"
Next myfield

Me.lstFields.RowSource = FinalString
Me.lstFields.ColumnCount = 2

Exit Function

myerror:
If Err.Number = 3270 Then 'no existing caption
    FinalString = FinalString & myfield.Name & ";" & "no caption" & ";"
    Resume Next
End If

End Function

Open in new window

that will produce a list with two columns as the rowsource of the listbox
first column is the Field Name
2nd column is the caption

Bevos

ASKER

Hi Capricorn, I tried this, but the display of the listbox doesn't change. Is that intended (the field name is masked by the caption name) or is something not working as it should?

Thanks, Bevo

Rey Obrero (Capricorn1)

see this revised db

click on the buildList button
EE-AbsRev.mdb

Bevos

ASKER

Sorry to constantly turn to you for help capricorn, but I'm not having much luck with the coding. I am trying to use a pretty simple command but am getting errror 3011 'The microsoft office access database engine could not find the object 'currentDB.Openrecordset(strSQL)'. Make sure the object exists and that you spell its name and path name correctly. I added the following code:

Private Sub ExcelExport_Click()
Dim fieldlist As String, fldArr, j As Integer, fldCapList As String, CapArr
Dim nc As Long, rs As DAO.Recordset, strSql As String
Dim nr As Long

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "CurrentDb.OpenRecordset(strSql)", "d:\testing.xls", True
End Sub

And the error occurs on the DoCmd line.

Thanks for all the help thus far,
Bevo
EE-AbsRev.mdb

Rey Obrero (Capricorn1)

you can not use "CurrentDb.OpenRecordset(strSql)" in the transferspreadsheet command.
you either use a name of table or saved query.

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bevos

ASKER

This workes better than I could have hoped! Thanks so much Capricorn1!