Saving some results in a text file or perhaps access database

Question

TheDocterd 7 Junior Poster

13 Years Ago

Hi There

I just wanna find out how I can save some data to a text file or access database (doesn't matter - the one that's the quickest).

What my form does is it has a list box with ID's in it. I can select them. Then I have a button that calculates what ID's are 2 days behind the current date (those who are checked). When calculated it shows a messagebox with those who are indeed behind and then open outlook to send an email.

All I want to do now is take the ID's that are behind, the days behind and also the current date to be saved in the way mentioned above.

Can somebody help me?

Here is my code:

Dim xList As String

Private Sub ShowMail()

On Error Resume Next
Dim intX As Integer

With List1

.ListIndex = 0

For intX = 0 To .ListCount - 1
    If .Selected(intX) Then
        personel.ListIndex = .ListIndex
        xList = xList & personel.Text & ";"
    End If
    .ListIndex = .ListIndex + 1
Next intX
End With

End Sub



Private Sub Form_Load()
 
 folder = Dir("Z:\ACS\Admin\ServU\JobBook\LogBook\*.txt") 'variable folder conatins names of files in folder c:\LogBook\ with extension *.txt
    While Len(folder) <> 0 'loop while length of folder is 0 which means no file found...
        List1.AddItem Left(folder, InStr(folder, ".txt") - 1) 'add file to list, but trim .txt extension..
        folder = Dir() 'read again
    Wend
 
End Sub



Private Sub Picture2_Click()
 
Dim IDs As String
 
For i = 0 To List1.ListCount - 1 'do loop within all od list1 indexes
    If List1.Selected(i) Then 'If checked index is selected then ...
        filename = "Z:\ACS\Admin\ServU\JobBook\LogBook\" + List1.List(i) + ".txt" 'Construct filename, add folder and extension
        Open filename For Input As #1 'Open file for reading
        While Not EOF(1)
            Line Input #1, LineA 'Since all dates are same i will read only 1st row
        Wend
        Close #1 'Close file
            If DateDiff("d", CDate(Left(LineA, 10)), Now()) > 2 Then 'If difference between now and readed date is more than 2 days ("d")
                IDs = IDs & "ID " & List1.List(i) & " Days behind = " & DateDiff("d", CDate(Left(LineA, 10)), Now()) & Chr(13)
            End If
    End If
Next

MsgBox IDs 'Display ID's

Call ShowMail
 
Dim objOL As Outlook.Application
Dim msg As Outlook.MailItem
 
Set objOL = New Outlook.Application
Set msg = objOL.CreateItem(olMailItem)

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 ' Get a free file number
nFileNum = FreeFile
' Open a text file for input. inputbox returns the path to read the file
Open "C:\Template.txt" For Input As nFileNum
lLineCount = 1
' Read the contents of the file
Do While Not EOF(nFileNum)
   Line Input #nFileNum, sNextLine
   'do something with it
   'add line numbers to it, in this case!
   sNextLine = sNextLine & vbCrLf
   sText = sText & sNextLine
Loop
' Close the file
Close nFileNum
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 
With msg
.To = xList
.Subject = sText
.Body = sText
.Display
End With
 
Exit_Email:
Set objOL = Nothing
Exit Sub

End Sub

visual-basic

3 Contributors
16 Replies
190 Views
6 Days Discussion Span
Latest Post 13 Years Ago Latest Post by AndreRet

WaltP 2,905 Posting Sage w/ dash of thyme

13 Years Ago

OPEN a file
WRITE or PRINT the data
CLOSE the file.

AndreRet 526 Senior Poster

13 Years Ago

Dewald, I'm shooting home now, I have been really tied up for the past week or so. I'll help tomoorow morning, you're grafting as well?:)

AndreRet 526 Senior Poster

13 Years Ago

a Quick answer to the last 2 lines -

Write #iFileNo, Now 'will add the date
        Write #iFileNo, Thedatebehind 'Use the variable you got earlier on to check if a date is behind, and by how much...

For data, which is much easier, I'll post something soon. Sorry bud, I'm really stuck at work...:)

AndreRet 526 Senior Poster

13 Years Ago

No problem.:)

Dimension a variable at the TOP of your code form, before any subs -

Dim xDate As Date
'When getting the DateDiff value, save it immediately to xDate...
xDate = DateDiff

'THEN call it to save it in the text file.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

TheDocterd 7 Junior Poster · Answer 1 · 2011-03-25T15:13:08+00:00

Okay this is the code I've coded, nut how can I write the last two lines so that it writes the current date and days behind:

Write #iFileNo, " text "
        Write #iFileNo, " some more text! "

All code:

Dim xList As String

Private Sub ShowMail()

On Error Resume Next
Dim intX As Integer

With List1

.ListIndex = 0

For intX = 0 To .ListCount - 1
    If .Selected(intX) Then
        personel.ListIndex = .ListIndex
        xList = xList & personel.Text & ";"
    End If
    .ListIndex = .ListIndex + 1
Next intX
End With

End Sub

Private Sub Form_Load()
 
 folder = Dir("Z:\ACS\Admin\ServU\JobBook\LogBook\*.txt") 'variable folder conatins names of files in folder c:\LogBook\ with extension *.txt
    While Len(folder) <> 0 'loop while length of folder is 0 which means no file found...
        List1.AddItem Left(folder, InStr(folder, ".txt") - 1) 'add file to list, but trim .txt extension..
        folder = Dir() 'read again
    Wend
 
End Sub


Private Sub Picture2_Click()
 
Dim IDs As String
 
For i = 0 To List1.ListCount - 1 'do loop within all od list1 indexes
    If List1.Selected(i) Then 'If checked index is selected then ...
        filename = "Z:\ACS\Admin\ServU\JobBook\LogBook\" + List1.List(i) + ".txt" 'Construct filename, add folder and extension
        Open filename For Input As #1 'Open file for reading
        While Not EOF(1)
            Line Input #1, LineA 'Since all dates are same i will read only 1st row
        Wend
        Close #1 'Close file
            If DateDiff("d", CDate(Left(LineA, 10)), Now()) > 2 Then 'If difference between now and readed date is more than 2 days ("d")
                IDs = IDs & "ID " & List1.List(i) & " Days behind = " & DateDiff("d", CDate(Left(LineA, 10)), Now()) & Chr(13)
            End If
    End If
Next

MsgBox IDs 'Display ID's

Call createFile

Call ShowMail

Dim objOL As Outlook.Application
Dim msg As Outlook.MailItem

Set objOL = New Outlook.Application
Set msg = objOL.CreateItem(olMailItem)

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 ' Get a free file number
nFileNum = FreeFile
' Open a text file for input. inputbox returns the path to read the file
Open "C:\Template.txt" For Input As nFileNum
lLineCount = 1
' Read the contents of the file
Do While Not EOF(nFileNum)
   Line Input #nFileNum, sNextLine
   'do something with it
   'add line numbers to it, in this case!
   sNextLine = sNextLine & vbCrLf
   sText = sText & sNextLine
Loop
' Close the file
Close nFileNum
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

With msg
.To = xList
.Subject = sText
.Body = sText
.Display
End With

Exit_Email:
Set objOL = Nothing
Exit Sub

End Sub

Private Sub createFile()

Dim iFileNo As Integer
iFileNo = FreeFile

'open the file for writing
Open "C:\LogFile.txt" For Append As #iFileNo

For i = 0 To List1.ListCount - 1
    If List1.Selected(i) Then
        'write text to the file
        Write #iFileNo, List1.List(i)
        Write #iFileNo, " text "
        Write #iFileNo, " some more text! "
    End If
Next

'close the file (if you dont do this, you wont be able to open it again!)
Close #iFileNo

End Sub

TheDocterd 7 Junior Poster · Answer 2 · 2011-03-28T12:59:14+00:00

Hehe yes..It's a almost like a nonstop working situasion here. So many things to do, then one little problem holding me up with some.

But thanks! :) waiting for your reply.

TheDocterd 7 Junior Poster · Answer 3 · 2011-03-29T13:23:46+00:00

TheDocterd 7 Junior Poster

13 Years Ago

Did you manage to get a time to look at it?

TheDocterd 7 Junior Poster · Answer 4 · 2011-03-29T14:09:33+00:00

Thanks, My variable is called DateDiff, but when I call it in that line I get an error Argument not optional.

Perhaps I'm missing something simple.

Ag there's no problem man, I'm also in quite a hot spot here with all the work. :) but thanks for making some time

TheDocterd 7 Junior Poster · Answer 5 · 2011-03-29T19:31:12+00:00

thanks!! will test it tonight and modify where needed..

Have a nice day further :)

AndreRet 526 Senior Poster · Answer 6 · 2011-03-29T22:16:47+00:00

AndreRet 526 Senior Poster

13 Years Ago

Cool, no problemo's.

TheDocterd 7 Junior Poster · Answer 7 · 2011-03-30T12:18:03+00:00

I seem to be getting an error at the line xDate = DateDiff

Don't if I'm just being stupid or something.. Line 57

If you can take a look at my code..

Dim xList As String

Dim xDate As Date



Private Sub ShowMail()

On Error Resume Next
Dim intX As Integer

With List1

.ListIndex = 0

For intX = 0 To .ListCount - 1
    If .Selected(intX) Then
        personel.ListIndex = .ListIndex
        xList = xList & personel.Text & ";"
    End If
    .ListIndex = .ListIndex + 1
Next intX
End With

End Sub



Private Sub Form_Load()
 
 folder = Dir("Z:\ACS\Admin\ServU\JobBook\LogBook\*.txt") 'variable folder conatins names of files in folder c:\LogBook\ with extension *.txt
    While Len(folder) <> 0 'loop while length of folder is 0 which means no file found...
        List1.AddItem Left(folder, InStr(folder, ".txt") - 1) 'add file to list, but trim .txt extension..
        folder = Dir() 'read again
    Wend
 
End Sub



Private Sub Picture2_Click()
 
Dim IDs As String
 
For i = 0 To List1.ListCount - 1 'do loop within all od list1 indexes
    If List1.Selected(i) Then 'If checked index is selected then ...
        filename = "Z:\ACS\Admin\ServU\JobBook\LogBook\" + List1.List(i) + ".txt" 'Construct filename, add folder and extension
        Open filename For Input As #1 'Open file for reading
        While Not EOF(1)
            Line Input #1, LineA 'Since all dates are same i will read only 1st row
        Wend
        Close #1 'Close file
            If DateDiff("d", CDate(Left(LineA, 10)), Now()) > 2 Then 'If difference between now and readed date is more than 2 days ("d")
                IDs = IDs & "ID " & List1.List(i) & " Days behind = " & DateDiff("d", CDate(Left(LineA, 10)), Now()) & Chr(13)
                
                'When getting the DateDiff value, save it immediately to xDate...
                xDate = DateDiff
                
            End If
    End If
Next

MsgBox IDs 'Display ID's

Call createFile

Call ShowMail

Dim objOL As Outlook.Application
Dim msg As Outlook.MailItem

Set objOL = New Outlook.Application
Set msg = objOL.CreateItem(olMailItem)

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 ' Get a free file number
nFileNum = FreeFile
' Open a text file for input. inputbox returns the path to read the file
Open "C:\Template.txt" For Input As nFileNum
lLineCount = 1
' Read the contents of the file
Do While Not EOF(nFileNum)
   Line Input #nFileNum, sNextLine
   'do something with it
   'add line numbers to it, in this case!
   sNextLine = sNextLine & vbCrLf
   sText = sText & sNextLine
Loop
' Close the file
Close nFileNum
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

With msg
.To = xList
.Subject = sText
.Body = sText
.Display
End With

Exit_Email:
Set objOL = Nothing
Exit Sub

End Sub



Private Sub createFile()

Dim iFileNo As Integer
iFileNo = FreeFile

'open the file for writing
Open "C:\LogFile.txt" For Append As #iFileNo

For i = 0 To List1.ListCount - 1
    If List1.Selected(i) Then
        'write text to the file
        Write #iFileNo, personel.List(i)
        Write #iFileNo, Now
        Write #iFileNo, xDate
    End If
Next

'close the file (if you dont do this, you wont be able to open it again!)
Close #iFileNo

End Sub

AndreRet 526 Senior Poster · Answer 8 · 2011-03-30T12:36:42+00:00

Change it to -

xDate = DateDiff("d", CDate(Left(LineA, 10)), Now()) & Chr(13)

TheDocterd 7 Junior Poster · Answer 9 · 2011-03-30T13:14:37+00:00

Haha it works but it prints some weird in the text file..

This is my output in txt file: check the last line

"abc@someemail.com"
#2011-03-30 09:12:42#
#1899-12-30#

AndreRet 526 Senior Poster · Answer 10 · 2011-03-30T14:13:41+00:00

try the following. I have add another "d" for dd -

xDate = DateDiff("dd", CDate(Left(LineA, 10)), Now()) & Chr(13)

TheDocterd 7 Junior Poster · Answer 11 · 2011-03-30T15:04:30+00:00

Arrgghh... Error: invalid procedurecall or argument. The 'dd' is date format or what?

AndreRet 526 Senior Poster · Answer 12 · 2011-03-30T15:45:25+00:00

Seems that I am also loosing track here, sorry.

I'm running it as it comes. The first part was correct, just remove the Chr13 part, it returns an invalid date, hence the 1899 part, I think?

xDate = DateDiff("d", CDate(Left(LineA, 10)), Now())