Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 456
  • Last Modified:

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

0
Bevos
Asked:
Bevos
  • 4
  • 3
1 Solution
 
Rey Obrero (Capricorn1)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



0
 
BevosAuthor 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
0
 
Rey Obrero (Capricorn1)Commented:
see this revised db

click on the buildList button
EE-AbsRev.mdb
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
BevosAuthor 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
0
 
Rey Obrero (Capricorn1)Commented:
you can not use "CurrentDb.OpenRecordset(strSql)" in the transferspreadsheet command.
you either use a name of table or saved query.

0
 
Rey Obrero (Capricorn1)Commented:
see the codes behind the excel export button
EE-AbsRev2.mdb
0
 
BevosAuthor Commented:
This workes better than I could have hoped! Thanks so much Capricorn1!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now