Creating a file - 500pts

Hey All,
I want to create a file based on information held in a table of my database.  The file itself is an ICS file (calendar file), however this is not important.  The data held in the file is plain text, and follows the structure below:

BEGIN:VCALENDAR
VERSION
 :2.0
PRODID
 :-//Mozilla.org/NONSGML Mozilla Calendar V1.0//EN
METHOD
 :PUBLISH
BEGIN:VEVENT
SUMMARY
 :The Title of the Event
DESCRIPTION
 :Description of the Event
CLASS
 :PUBLIC
X-MOZILLA-ALARM-DEFAULT-LENGTH
 :30
X-MOZILLA-RECUR-DEFAULT-INTERVAL
 :0
DTSTART
 ;VALUE=DATE
 :yyyymmdd
DTEND
 ;VALUE=DATE
 :yyyymmdd
END:VEVENT
END:VCALENDAR

For Each Event
BEGIN:VEVENT
SUMMARY
 :The Title of the Event (Name of customer)
DESCRIPTION
 :Description of the Event (Extra details from the customer database, previous tunings and address)
CLASS
 :PUBLIC
X-MOZILLA-ALARM-DEFAULT-LENGTH
 :Standard numeric time – fixed for all events ( :30)
X-MOZILLA-RECUR-DEFAULT-INTERVAL
 :Standard numeric time – always 0 ( :0)
DTSTART
 ;VALUE=DATE
 :yyyymmddThhmmss
DTEND
 ;VALUE=DATE
 :yyyymmddThhmmss
END:VEVENT

The data I would like to retrieve from my database would come from a query holding the fields Summary, Description, Date Start and Date End.  For each record on the query I would like a new event created in the file.  After running for each record of the query I would like the file to be saved as Calendar.ics.  Every time the script is run I would like it to recreate the entire file and overwrite the previous existing one.

Please could you provide me with a script which will do this.

Liam
LVL 1
Liam2k3Asked:
Who is Participating?
 
rockiroadsCommented:
put together equals something like this

    Dim iHandle As Integer
    Dim rs As DAO.Recordset
   
    iHandle = FreeFile
   
    Open "c:\calendars\Calendar.ics" For Output As #iHandle
    Set rs = CurrentDb.OpenRecordset("SELECT Summary, Description, DateStart, DateEnd FROM MyTable")
    Do While rs.EOF = False
       
        Print #iHandle, "BEGIN: VCALENDAR"
        Print #iHandle, "Version"
        Print #iHandle, ":2.0"
        Print #iHandle, "PRODID"
        Print #iHandle, ":-//Mozilla.org/NONSGML Mozilla Calendar V1.0//EN"
        Print #iHandle, "METHOD"
        Print #iHandle, ": PUBLISH"
        Print #iHandle, "BEGIN: VEVENT"
        Print #iHandle, "SUMMARY"
        Print #iHandle, ":The Title of the Event (" & rs!Summary & ")"
        Print #iHandle, "Description"
        Print #iHandle, ":" & rs!Description
        Print #iHandle, "Class"
        Print #iHandle, ":PUBLIC"
        Print #iHandle, "X -MOZILLA - ALARM - Default - LENGTH"
        Print #iHandle, ":30"
        Print #iHandle, "X -MOZILLA - RECUR - Default - INTERVAL"
        Print #iHandle, ":0"
        Print #iHandle, "DTSTART"
        Print #iHandle, ";VALUE=DATE"
        Print #iHandle, ": " & Format(rs!StartDate, "YYYYMMDDHHMMSS")
        Print #iHandle, "DTEND"
        Print #iHandle, ";VALUE=DATE"
        Print #iHandle, ": " & Format(rs!StartDate, "YYYYMMDDHHMMSS")
        Print #iHandle, "End: VEVENT"
        Print #iHandle, "End: VCALENDAR"

        rs.MoveNext
    Loop
   
    rs.Close
    Set rs = Nothing
    Close #iHandle
0
 
rockiroadsCommented:
This sample code shows you how to write to a file


    Dim iHandle As Integer
   
    iHandle = FreeFile
    Open "c:\test.txt" For Output As #iHandle
    Print #iHandle, "hello"
    Close #iHandle


this code opens the DB and reads records

    Dim rs As DAO.Recordset

sSql = "SELECT * FROM myTable"
set rs = CurrentDB.OpenRecordSet(sSql)
do while rs.EOF = FALSE
    'access fields
    debug.print rs!field1
    'next
    rs.movenext
loop
rs.close
set rs =nothing


Now merge the two,

    Dim iHandle As Integer
    Dim rs As DAO.Recordset
   
    iHandle = FreeFile
    Open "c:\dipak\xxx_ttt.txt" For Output As #iHandle
   
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM Table1")
    Do While rs.EOF = False
        Print #iHandle, "hello"
        rs.MoveNext
    Loop
    rs.close
    set rs = nothing
    close #iHandle

But what you do is write the lines you want and the fields you want within the loop
if you want individual files, then open and close the file within the loop

does this make sense?
0
 
rockiroadsCommented:
spot the mistakes!!!

I put StartDate where there should be End Date

You also need to correct the fields from the table,

Hopefully u understand what Ive done so you can correct the code where necessary
0
 
Liam2k3Author Commented:
Thanks for the help, the code worked a treat, heres what I ended up with:

    Dim iHandle As Integer
    Dim rs As DAO.Recordset
   
    iHandle = FreeFile
   
    Open "c:\Calendar.ics" For Output As #iHandle
    Set rs = CurrentDb.OpenRecordset("SELECT Summary, Description, StartTime, EndTime FROM Calendar")
   
        Print #iHandle, "BEGIN:VCALENDAR"
        Print #iHandle, "Version:2.0"
        Print #iHandle, "X-WR-CALNAME:Work"
        Print #iHandle, "PRODID:-//Mozilla.org/NONSGML Mozilla Calendar V1.0//EN"
        Print #iHandle, "X-WR-TIMEZONE:Europe/London"
        Print #iHandle, "CALSCALE:GREGORIAN"
        Print #iHandle, "METHOD:PUBLISH"
       
    Do While rs.EOF = False
        Print #iHandle, "BEGIN:VEVENT"
        Print #iHandle, "SUMMARY:" & rs!Summary
        Print #iHandle, "Description:" & rs!Description
        Print #iHandle, "Class:PUBLIC"
        Print #iHandle, "X-MOZILLA-ALARM-Default-LENGTH:30"
        Print #iHandle, "X-MOZILLA-RECUR-Default-INTERVAL:0"
        Print #iHandle, "DTSTART:" & Format(rs!StartTime, "YYYYMMDDHHMMSS")
        Print #iHandle, "DTEND:" & Format(rs!EndTime, "YYYYMMDDHHMMSS")
        Print #iHandle, "End:VEVENT"
       
        rs.MoveNext
    Loop
        Print #iHandle, "End:VCALENDAR"
    rs.Close
    Set rs = Nothing
    Close #iHandle

Out of interest, could you possibly explain to me why we set iHandle as an Integer yet we add to it like a string?

Liam
0
 
rockiroadsCommented:
thats how VB works

FreeFile is used to obtain the next file handle. File handles in VB are integers
lots of people just used a number like 1, 2, but I prefer to make use of that

Everytime u use the file handle, u need to specify in whatever call u do

Print is one command to write to a file, but first u must specify what file u want to write to

It is possible to have multiple files open

Therefore we use Print <file handle>, <text to write>


does that explain what you meant by why we set iHandle as an Integer yet we add to it like a string?

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.