Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Exporting or Printing an Unbound listbox (2003)

    I have been looking all over for any answer how to expor or print the data containted in an unbound listbox. I have a search form with different combo boxes which thru vba code display their results in the listbox. I would like to insert a button where I can print or export the data that currently display in the listbox; no matter the SQL statements that is ran from the different combo boxes.

    I tried to capture the data of the listbox by using strData = lstDisplay.rowsource Then tried to use printout but it doesnt print the data. or tried to export to excel and the same results...Any help is more than welcome.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Exporting or Printing an Unbound listbox (2003)

    Is the row source of the list box an SQL string? You use Automation to start Excel, open a recordset based on the SQL string and use the CopyFromRecordset method to copy its records into a sheet.
    Search for CopyFromRecordset in this forum or in the Excel forum for examples. The Excel VBA help also has a good example.

  3. #3
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Exporting or Printing an Unbound listbox (2003)

    This is what i got so far, check the code below. The code is exporting the data from the listbox rowsource everything time the sql string changes which is great. I am receiving a prompt command everytime if i want to save changes to the file "Datareport.xls" due to "Set xlWbk = xlApp.Workbooks.Open("C:ExcelDataReport.xls")" How can I just have a save prompt without using the location of "xlWbk" Which at the time I am using it to simulate the save as prompt when i press yes to be able to save the data with a new name and location. Any advice.

    Private Sub cmdExport_Click()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim xlApp As Object
    Dim xlWbk As Object
    Dim xlSht As Object

    Set db = CurrentDb
    Set rs = db.OpenRecordset(Me![lstDisplay].RowSource, dbOpenDynaset)
    Set xlApp = CreateObject("Excel.Application")
    Set xlWbk = xlApp.Workbooks.Open("C:ExcelDataReport.xls")
    Set xlSht = xlWbk.Worksheets(1)


    For iCols = 0 To rs.Fields.Count - 1
    xlSht.Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
    Next

    xlSht.Range("A2").CopyFromRecordset rs


    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Set xlWbk = Nothing
    Set xlSht = Nothing

    xlApp.Quit
    Set xlApp = Nothing

    End Sub

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Exporting or Printing an Unbound listbox (2003)

    You could use

    xlApp.Dialogs(5).Show

    5 is the value of the Excel constant xlDialogSaveAs.

  5. #5
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Exporting or Printing an Unbound listbox (2003)

    Is there a way to open excel or create an excel doc without using

    Set xlWbk = xlApp.Workbooks.Open("C:ExcelDataReport.xls")

    and just go straight to xlApp.Dialogs(5).Show with the previous code posted

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Exporting or Printing an Unbound listbox (2003)

    You can create a blank new workbook using

    Set xlkWbk.Workbooks.Add

    You must either create a new workbook or open an existing one, for CreateObject("Excel.Application") starts an invisible instance of Excel without any open workbook (unlike starting Excel interactively).

  7. #7
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Exporting or Printing an Unbound listbox (2003)

    Thanks again Hans. Just posting the revised version. it works great.

    Private Sub cmdExport_Click()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim xlApp As Object
    Dim xlWbk As Object
    Dim xlSht As Object


    If Me.lstDisplay.RowSource = Empty Then
    MsgBox "Please display the required information"

    Else

    Set db = CurrentDb
    Set rs = db.OpenRecordset(Me![lstDisplay].RowSource, dbOpenDynaset)
    Set xlApp = CreateObject("Excel.Application")
    Set xlWbk = xlApp.Workbooks.Add
    Set xlSht = xlWbk.Worksheets(1)

    For iCols = 0 To rs.Fields.Count - 1
    xlSht.Cells(1, iCols + 1).Value = rs.Fields(iCols).NAME
    Next
    xlSht.Range(xlSht.Cells(1, 1), xlSht.Cells(1, rs.Fields.Count)).Font.Bold = True
    xlSht.Range("A2").CopyFromRecordset rs
    xlApp.Dialogs(5).Show

    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Set xlWbk = Nothing
    Set xlSht = Nothing

    xlApp.Quit
    Set xlApp = Nothing

    End If

    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •