asked on

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

rockiroads

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?

ASKER CERTIFIED SOLUTION

rockiroads

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rockiroads

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

Liam2k3

ASKER

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

rockiroads

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?