how to generate appointment times with 5 minute interval

[crazyfisher]

Dear all, I want to write a macro to inform the patients about a x-ray scan, and I wish to write a macro that can automatically generate time(at a 5 minute interval, from 9 am to 4:55 pm, excluding 12 am-1pm)....I know how to get the name and address from a file, but I am not sure how to make the time setting thing working, could anyone help ???thanks a lot !!

[OTWarrior]

I am not sure I completely follow what you are after, but an array (or 2?)in a loop would possibly be a good start. What code do you have so far?

What exactly do you mean by "automatically generate time in a 5 minute interval"?

Do you mean when you book the appointment it is the last time + 5 minutes? or something else?

[crazyfisher]

yes, you are right.
the first patient's appointment is 9am and the second is 9:05 am ...etc.

I think about inputing from a file with the time,,but there should be a way to automate the time sequence...
thanks!

[OTWarrior]

I would imagine a global value hidden in the document (either in code or somewhere on the document itself) with the last value held in itself could be a way to do it.

for example

public nValue as integer

public sub AddTime()
Dim Data As Variant
Data = ("9:00", "9:05", "9:10")

nValue = nValue + 1

activedocument.bookmarks("txtBox").text = Data(nValue )
end sub



This is untested, but should point you in the right direction

[TonyJollans]

Something like this?

Dim Appt As Date
For Appt = #9:00:00 AM# To #11:55:00 AM# Step #12:05:00 AM#
Debug.Print "Appointment: " & Appt
Next

[Bob Phillips]

Extending to the original question



Dim Appt As Date
For Appt = #9:00:00 AM# To #4:55:00 PM# Step TimeSerial(0, 5, 0)
If Hour(Appt) <> 12 Then
Debug.Print "Appointment: " & Appt
End If
Next

[TonyJollans]

Thanks, Bob. I thought of two consecutive loops but that condition is neater.

[Bob Phillips]

Thanks, Bob. I thought of two consecutive loops but that condition is neater.

That was my first thought too.

[crazyfisher]

thank you for all your help!

all the codes sound new to me, I gotta have a go first .
just another problem, if the patient name and address information is in the excel file, how should I input them via macro to the word?

Option Explicit
Dim D As Word.Document
Dim R As Word.Range
Dim x, inpTitle, inpFirstName, inpClient, inpSurName, inpAddress, inpSuburb, R1
Sub printletter()
Selection.HomeKey unit:=wdStory
'pay attention to the format and space out the commands
'vbcrlf--visual basic carriage return line feed
Open "E:\patient.xls" For Input As #1
Do
' show the sequence of all types of data
Input #1, inpTitle, inpFirstName, inpSurName, inpAddress, inpSuburb, inpTime

Set D = Documents.Add("E:\patient.dot")
Set R = D.Bookmarks("address").Range

R.InsertAfter inpTitle & " " & inpFirstName & " " & inpSurName & vbCrLf
R.InsertAfter inpAddress & vbCrLf
R.InsertAfter inpSuburb

ActiveDocument.Bookmarks("date").Select
Selection.TypeText Format(Date, "dd/mm/yy")

'ActiveDocument.Bookmarks("name").Select
'Selection.TypeText inpTitle & " " & inputSurName

Set R1 = D.Bookmarks("name").Range
R1.InsertAfter inpTitle & " " & inpSurName


'create more docs with the data.
x = x + 1
D.SaveAs ("E:\letter" & x & ".doc")
D.Close savechanges:=wdDoNotSaveChanges
Loop Until EOF(1)
Close #1
End
End Sub

[crazyfisher]

just another question about the code by xld, it turns out it fills a table with all these appointment time...
I want to make it generate an appointment time for each client in their letter, so when I run the macro, separate letters will be generated with their different times...
is it possible ?
Thanks !

[TonyJollans]

Seems like you should be doing a mailmerge. What are you actually doing?

[crazyfisher]

Dear all, I am not using mailmerge and sorry for causing all the confusion,


I figure it out with a programming friend about this...but later I find there is still a bug,,, I solve the 5 minute interval problem ,but when it reaches the end of a day(4:55PM) , how could I make the date autometically change to the next day ?
here is my code: the patient's name and address are stored in a text file
and i am trying to generate letters to each of them with their name,address and appointment time.


'declare variables
Sub printletter()

Dim D As Word.Document
Dim R As Word.Range
Dim app As Date
Dim dat As Date
app = #9:00:00 AM#
dat = #11/15/2007#

Selection.HomeKey unit:=wdStory
'pay attention to the format and space out the commands
'vbcrlf--visual basic carriage return line feed
Open "E:\patients.txt" For Input As #1
Do
' show the sequence of all types of data
Input #1, inpTitle, inpFirstName, inpSurName, inpAddress, inpSuburb

Set D = Documents.Add("E:\patient.dot")
Set R = D.Bookmarks("address").Range
R.InsertAfter inpTitle & " " & inpFirstName & " " & inpSurName & vbCrLf
R.InsertAfter inpAddress & vbCrLf
R.InsertAfter inpSuburb

'ActiveDocument.Bookmarks("name").Select
'Selection.TypeText inpTitle & " " & inputSurName

Set R1 = D.Bookmarks("name").Range
R1.InsertAfter inpTitle & " " & inpSurName
ActiveDocument.Bookmarks("time").Select
Selection.TypeText app & " on " & dat
If TimeValue(app) = #11:55:00 AM# Then
app = #2:00:00 PM#
ElseIf TimeValue(app) = #4:55:00 PM# Then
DateValue(dat) = dat + 1
TimeValue(app) = #9:00:00 AM#
Else
app = app + 5 / 1440
End If


'create more docs with the data.
x = x + 1
D.SaveAs ("E:\patient\letter" & x & ".doc")
D.Close savechanges:=wdDoNotSaveChanges
Loop Until EOF(1)
Close #1
End
End Sub

when I run the code, it is working until it reach 4:55 pm, then the error windows pops up.. how can I fix the date problem?
thanks !!

[TonyJollans]

Try just

dat = dat + 1

[TonyJollans]

And, of course, ...

app = #9:00:00 AM#

[crazyfisher]

thanks !!!
Cool, it is working perfectly now, you are all great help !! thanks again

[lucas]

It would be great if you could post your final code and an example txt file so others could try your code....

[crazyfisher]

'declare variables
Sub printletter()

Dim D As Word.Document
Dim R As Word.Range
Dim app As Date
Dim dat As Date
app = #9:00:00 AM#
dat = #11/15/2007#

Selection.HomeKey unit:=wdStory
'pay attention to the format and space out the commands
'vbcrlf--visual basic carriage return line feed
Open "E:\patients.txt" For Input As #1
Do
' show the sequence of all types of data
Input #1, inpTitle, inpFirstName, inpSurName, inpAddress, inpSuburb

Set D = Documents.Add("E:\patient.dot")
Set R = D.Bookmarks("address").Range
R.InsertAfter inpTitle & " " & inpFirstName & " " & inpSurName & vbCrLf
R.InsertAfter inpAddress & vbCrLf
R.InsertAfter inpSuburb

'ActiveDocument.Bookmarks("name").Select
'Selection.TypeText inpTitle & " " & inputSurName

Set R1 = D.Bookmarks("name").Range
R1.InsertAfter inpTitle & " " & inpSurName
ActiveDocument.Bookmarks("time").Select
Selection.TypeText app & " on " & dat
If TimeValue(app) = #11:55:00 AM# Then
app = #2:00:00 PM#
ElseIf TimeValue(app) = #4:55:00 PM# Then
dat = dat + 1
app = #9:00:00 AM#
Else
app = app + 5 / 1440
End If


'create more docs with the data.
x = x + 1
D.SaveAs ("E:\patient\letter" & x & ".doc")
D.Close savechanges:=wdDoNotSaveChanges
Loop Until EOF(1)
Close #1
End
End Sub

[lucas]

I was tinkering with this and got my .dot file with 3 bookmarks ok but having diffuculty with the layout of the txt file. Is it a comma delimited with these items on each line seperated by comma's?
inpTitle, inpFirstName, inpSurName, inpAddress, inpSuburb
or is it by line?

the comma's seem to work except when the address starts with a number??