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: 702
  • Last Modified:

VBA ListBox Help "Save Contents of Listbox At Document Close"

I'm having problems with this code.  I can't get it to work right.  I'm trying to save the contents of a multicolumn listbox to file so when i open up my word document i'll all still be there. My listbox has three columns.


Option Explicit

Private FileName As String

Private Sub Document_Open()
Dim I As Integer
Dim inputLine As String

FileName = Application.Path & "\Report"
   
If Dir(FileName) <> "" Then
    Debug.Print "Loading ListBox data from file..."
       
Open FileName For Input As #1
    While Not EOF(1)
        Line Input #1, inputLine
        PartsForm.lstParts2.AddItem (inputLine)
    Wend
Close #1
Else
    Debug.Print "Loading initial ListBox data..."
   
    For I = 1 To 10
        PartsForm.lstParts2.AddItem ("Item" & I)
    Next
End If

End Sub

Private Sub Document_Close()

Dim Cancel As Integer
Dim I As Integer

FileName = Application.Path & "\Report.txt"

    Debug.Print "Saving ListBox data to file..."

Open FileName For Output As #1
    For I = 1 To PartsForm.lstParts2.ListCount
        Print #1, PartsForm.lstParts2.List(I - 1)
    Next
Close #1
   
End Sub
0
Jason_Kha
Asked:
Jason_Kha
1 Solution
 
ShauliCommented:
Maybe its because the filename is wrong?

'in Document_Open:
FileName = Application.Path & "\Report"
'                                                       ^^^

'in Document_Close:
FileName = Application.Path & "\Report.txt"
'                                                        ^^^

S
0
 
Jason_KhaAuthor Commented:
Maybe you can help me with how to use FSO (File System Object) to do this!  I did some research on it but I don't understand it.  I'm new at this stuff.
0
 
gecko_au2003Commented:
go to:

http://juicystudio.com/tutorial/vb/files.asp

That is a great site. Also you need a reference to the Microsoft Scripting Runtime. Go to Project --> References to add reference to it :)

Also :

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon98/html/vbconprogramminginfsoobjectmodel.asp

There are different area's within that , which will help you out :)
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!

 
Jason_KhaAuthor Commented:
What I want to do is if possible i want to be able to save the contents of a multicolumn listbox to (I think) through binary file access.  So that when i save my word document the contents of the listbox aren't lost when i want to open my document back up and work on it again.  I read the stuff you suggested but i'm pretty new at this stuff and i don't understand it enough to make it work properly.
0
 
gecko_au2003Commented:
I have never tried saving contents of a multi column listbox myself.  Try and write the contents of the listbox to a text file and see what happens, other then that wait for idle mind :) He is a shear genius when it comes to programming :D

I will research it in a little bit and post back my findings , if any :D
0
 
gecko_au2003Commented:
If it is ok with EE rules,  if you can email me the word document and I will amend and upload to some free webspace :)
0
 
Jason_KhaAuthor Commented:
yeah I can do that!
0
 
Jason_KhaAuthor Commented:
Whats your Email?
0
 
gecko_au2003Commented:
It is on my profile, click on my id and it will take you to my profile :)
0
 
gecko_au2003Commented:
I just wanted to ask, did you want the listbox items saved in the same word document or did you want the listbox items saved in a new seperate word document ? Also If I cant manage that, would a text file be ok ?
0
 
Jason_KhaAuthor Commented:
if possible i would like to have the items saved in the same word document
0
 
PaulHewsCommented:
Option Explicit

Private Sub Document_Close()
   
    Dim I As Integer
    Dim FileName As String
   
    FileName = Application.Path & "\Report.txt"
   
    Debug.Print "Saving ListBox data to file..."
   
    Open FileName For Output As #1
        For I = 1 To  PartsForm.lstParts2.ListCount
            Print #1, PartsForm.lstParts2.List(I - 1) & "|" &  PartsForm.lstParts2.List(I - 1, 1) & "|" &  PartsForm.lstParts2.List(I - 1, 2)
        Next
    Close #1
   

End Sub



Private Sub Document_Open()
    Dim I As Integer
    Dim FileName As String
    Dim LineString As String
    Dim SplitString As Variant
    Dim Count As Integer
   
    FileName = Application.Path & "\Report.txt"
    If Len(Dir(FileName)) = 0 Then Exit Sub
    If FileLen(FileName) = 0 Then Exit Sub
    Debug.Print "Loading ListBox data from file..."
   
    Open FileName For Input As #1

    Do
        Line Input #1, LineString
        SplitString = Split(LineString, "|")
        With  PartsForm.lstParts2
            .AddItem SplitString(0)
            .List(Count, 1) = SplitString(1)
            .List(Count, 2) = SplitString(2)
        End With
        Count = Count + 1
    Loop Until EOF(1)
End Sub
0
 
gecko_au2003Commented:
There you go Jason kha, if you still want me to work on it let me know :) I have your documents but didnt know if you still wanted me to work on it taking into consideration that paul hews made a reasonable suggestion :)
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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