Results 1 to 10 of 10
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Export Listbox Contents as Text

    Hi!

    I have a list box on my spreadsheet, is there any way to export it's contents to a text "log" file?

    The listbox is updated with a file name each time a pre-defined process is complete.. Any ideas? [img]/forums/images/smilies/smile.gif[/img])

    Thanks!

    Drk.
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export Listbox Contents as Text

    The following code assumes that the listbox is linked to cell A1 on the active sheet and that the string from the list box can be gotten from there.

    <pre>Dim strLBVal As String
    Dim iFile As Integer
    strLBVal = Range("A1").Value
    iFile = FreeFile()
    Open "C:MYDirMYLog.LOG" For Append As #iFile
    Print #iFile, strLBVal
    Close #iFile
    </pre>

    Legare Coleman

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export Listbox Contents as Text

    What if the values are being stored in the listbox only while the sheet is opened?

    Thanks!

    Drk.
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export Listbox Contents as Text

    I afraid I don't understand your question. What do you mean by values are only stored while the sheet is open?
    Legare Coleman

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export Listbox Contents as Text

    I've got a loop on the main sheet, which executes a series of commands for various worksheets which meet specific criteria. As each set of commands executes, the name of that worksheet is added as a value in the listbox on the main sheet. Once the workbook is closed, those values are lost, because the listbox isn't linked to anything.

    Ideally, when the workbook is closed, the 'cache' or listbox would be cleared out, and moved to 'log file'. Would it be possible to do this without linking the listbox?

    Thanks again!

    Drk.
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export Listbox Contents as Text

    Your termanology is still confusing me. What do you mean that you have a loop on the main sheet? Sheets don't have code to loop, unless you are talking about an Excel 4 macro sheet. Code goes in modules, which can be user modules, or event code modules attached to worksheets or workbooks. I am also not sure where this list box is located. Is it on a worksheet, or is it in a user form? You say it is on a sheet, but some of what you are saying sounds like it is on a user form.

    How are you putting things into the listbox? I'm not an expert on controls on worksheets, and I don't know how you can put anything into one or get something out if it is not linked to cells on a worksheet. If the listbox is on a userform, you should be able to use the .List(index) property to get the items in the list out and write them to your log file. This could be done in the workbook close event routine.
    Legare Coleman

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export Listbox Contents as Text

    The listbox is on the worksheet. I say that the code is in the main sheet because when I double-click on the main sheet in VBA, there it is. Here's my 'Get Data' Code for the listbox.

    ***CODE START***
    ...
    Loop Until sFolder <> ""
    ReDim FileArray(0)
    FindFiles sFolder, FileArray(), "*" & StrInitials & "*.xls"
    For i = 1 To UBound(FileArray)
    ListBox.AddItem Now() & ". . . . . ." & FileArray(i)
    Workbooks.Open FileName:=FileArray(i)
    WbName = ActiveWorkbook.Name
    n = n + 1
    Application.DisplayAlerts = False
    Workbooks(WbName).Worksheets("Statistical Data").Range("A2:N2").Copy
    ...
    ****CODE END****

    The listbox contians the date, and the path/filename of the file being opened. I am using this routine to extract data from multiple spreadsheets, and the listbox is not linked to any 'physical' location within the database. Essentially, it holds the values while the database is opened. When it's closed, those values are lost, because it's not linked.

    Hope this clarifies a bit..

    Drk.
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export Listbox Contents as Text

    This is getting into an area that I don't know much about. However, if you can use the AddItem method to put things in the list box, you should be able to use the List property to get them out. something like:

    ListBox.List(I)

    Where I is the index number of the item you want.

    Was this listbox created from the Controls Tool Box or from the Forms Tool Box?
    Legare Coleman

  9. #9
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export Listbox Contents as Text

    Controls, I beleive...

    Drk.
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export Listbox Contents as Text

    The following works for me:

    <pre>Dim strLBVal As String
    Dim iCnt As Integer, I As Integer, iFile As Integer
    iCnt = Sheet1.ListBox1.ListCount
    iFile = FreeFile()
    Open "C:MYDirMYLog.LOG" For Append As #iFile
    For I = 0 To iCnt
    strLBVal = Sheet1.ListBox1.List(I)
    Print #iFile, strLBVal
    Next I
    Close #iFile
    </pre>

    Legare Coleman

Posting Permissions

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