We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Export a listbox containing field captions to excel

Bevos
Bevos asked
on
Medium Priority
505 Views
Last Modified: 2012-06-27
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

Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016

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



Author

Commented:
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
CERTIFIED EXPERT
Top Expert 2016

Commented:
see this revised db

click on the buildList button
EE-AbsRev.mdb

Author

Commented:
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
CERTIFIED EXPERT
Top Expert 2016

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

CERTIFIED EXPERT
Top Expert 2016
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
This workes better than I could have hoped! Thanks so much Capricorn1!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.