?
Solved

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

Posted on 2005-03-14
13
Medium Priority
?
610 Views
Last Modified: 2011-09-20
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
Comment
Question by:Jason_Kha
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
13 Comments
 
LVL 19

Expert Comment

by:Shauli
ID: 13538750
Maybe its because the filename is wrong?

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

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

S
0
 

Author Comment

by:Jason_Kha
ID: 13549869
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
 
LVL 23

Expert Comment

by:gecko_au2003
ID: 13554945
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
Independent Software Vendors: 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!

 

Author Comment

by:Jason_Kha
ID: 13561395
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
 
LVL 23

Expert Comment

by:gecko_au2003
ID: 13561711
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
 
LVL 23

Expert Comment

by:gecko_au2003
ID: 13561739
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
 

Author Comment

by:Jason_Kha
ID: 13577719
yeah I can do that!
0
 

Author Comment

by:Jason_Kha
ID: 13577727
Whats your Email?
0
 
LVL 23

Expert Comment

by:gecko_au2003
ID: 13577740
It is on my profile, click on my id and it will take you to my profile :)
0
 
LVL 23

Expert Comment

by:gecko_au2003
ID: 13577771
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
 

Author Comment

by:Jason_Kha
ID: 13577950
if possible i would like to have the items saved in the same word document
0
 
LVL 38

Accepted Solution

by:
PaulHews earned 2000 total points
ID: 13579594
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
 
LVL 23

Expert Comment

by:gecko_au2003
ID: 13580490
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month13 days, 10 hours left to enroll

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question