?
Solved

Creating a file - 500pts

Posted on 2006-04-03
5
Medium Priority
?
313 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:Liam2k3
  • 4
5 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 16361462
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
 
LVL 65

Accepted Solution

by:
rockiroads earned 2000 total points
ID: 16361533
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 16361704
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
 
LVL 1

Author Comment

by:Liam2k3
ID: 16371757
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 16375518
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

850 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