I searched all over and did not find the right answer so I played around until I had found it myself and now I’ll share it with you.
I had picked up a VBA coding project from a contractor who was leaving. The application takes a bunch of Excel data in various complex formats and creates a comma separated value file (CSV) as output that are used for import into a custom object in Salesforce.
The application takes a bunch of Excel data in various complex formats and creates a comma separated value file (CSV) as output that are used for import into a custom object in Salesforce.
One of the issues the customer was reporting was that they had an expectation that currency value in exported CSV files should retain the 2 digits after the decimal point.
The application was sending the output result in a CSV file but the currency value were only retaining one digit after the decimal if the last decimal was a zero.
A value of 91.20 is coming out as 91.2, missing the 2nd digit.
This happens consistently while using Excel with CSV files but not with XLS. It drops the zero every time.
How to Save currency values in a CSV File using Microsoft Excel
Save your CSV file in MS Excel format with a .xlsx or .xls extension.
Update the “Format Cells” to display the column as Currency. I set the option not to display a $ for currency and 2 digits after the decimal point.
Save this file with a .CSV extension. Your currency values with the 2 digits after the decimal point are preserved during this conversion.
That is it!
To validate it, open the CSV file with a text editor like Notepad or Notepad++ and you will see the number format with 2 digits after the decimal was retained.
Watch out! If you open the CSV file with MS Excel and save it again as CSV, Excel will wipe out your currency formatting.
What are Arrow Functions in JavaScript / ECMAScript 6?
Arrow functions are a simplified short hand method for creating anonymous functions.
Older ES5 JavaScript example:
function setup() {
createCanvas(600, 400);
background(0);
let button = createButton('press')
button.mousePressed(changeBackground);
function changeBackground() {
background(random(255));
}
}
New ES6 JavaScript Example
function setup() {
createCanvas(600, 400);
background(0);
let button = createButton('press')
button.mousePressed(() => background(255)));
}
Anonymous Function written as Arrow Function:
() => background(255))
Just a very short blog jot for today as I’m busy working on a Salesforce.com data analysis tool / system.
Since this is a very low budget project, I’m hand coding everything using Notepad++ and VBScript to create a dynamic web application in old school Acitve Server Pages (ASP) that can consume the analytic data that my system stored on a MS Access database.
Again, low budget. Me and my wits pounding out code.
I ran in to an issue where the web page is calling a JavaScript that passes two variables to an Ajax call to load a Chart.js bar chart in a popup window.
It was working great but the page that had the onlick event to load the popup chart was reloading as well when clicked instead of just loading the popup window with the chart.
What’s the fix?
Add “return false;” at the like in the example below and the page containing the onclick link should stop reloading when clicked.
Old Code
<a href="#" OnClick="getChart('John Smith', '2017-01');">John Smith</a>
New Code
<a href="" OnClick="getChart('John Smith', '2017-01');return false;">John Smith</a>
I always give credit to the people who helped me find the answer. I had found the answer in the stackoverflow page below.
I had really been wanting to find a couple of hours to do a crash course on python and finally found an opportunity to do it last night.
Having been a programmer for a while, I’m finding many of the Python videos quite boring as they are for absolute beginners which has you plodding through the basics of programming which makes learning a new programming language painfully slow.
Luckily, I had found this great Python primer video by Derek Banas on YouTube.
I followed along and completed all the code in the video and had working examples of most of the important code snippets for much of how stuff gets done in Python. Success was not without some pain, see problems and resolutions below.
Video: Learn Python in One Video
I will save you some pain by telling you there is a very frustrating code issue at the end of this long video.
See code fix below.
When you are in the Dog class referencing the Animal super class from which it inherits the name, weight, height and sound, properties or values you have to use the setter and getter class methods instead of the object “self” reference like we could in the Animal object. Once you change that, the code works.
For the Dog class, the toString() method only worked for me the example below:
(this works)
def toString(self):
return "{} is {} cm tall and {} kilograms, says {} and owner is {}".format(
self.get_name(),
self.get_height(),
self.get_weight(),
self.get_sound(),
self.__owner)
...instead of:
(this doesn't)
def toString(self):
return "{} is {} cm tall and {} kilograms, says {} and owner is {}".format(
self.__name,
self.__height,
self.__weight,
self.__sound,
self.__owner)
As a bonus I would add watching this video to help solidify the idea of polymorphism if you’re having difficulty getting a grasp of it.
I’m taking a class on how to build Arduino devices.
This morning’s Arduino lesson used a temperature sensor to read the temperature and output it to the Arduino serial monitor.
Here is my Arduino code which is a combo of code from the class plus my attempt to integrate an alarm to it which I got from a combination of YouTube and Arduino online resources.
The code is my own creation based on stuff I found.
It is free for you to use copy improve and share. Enjoy!
Arduino Based Temperature Monitor Sample Code
int tempPin = A1; //Sets Analog Pin for Temp Sensor
int buzzer = 12; //Set alarm pin for peiezio sensor
int alertTemp = 78;
void setup() {
// put your setup code here, to run once:
Serial.begin(9600); //Starts serial monitor output
pinMode(buzzer,OUTPUT); //initialize the buzzer pin as an output
}
void loop() {
// put your main code here, to run repeatedly:
int sensorVal = analogRead(tempPin); //Reads the temperature sensor
float voltage = sensorVal * 5; //Turns sensrVal to Voltage
voltage /= 1024; //Divides voltage by 1024 and sets voltage to that result
float tempC = (voltage - .5) * 100; //Determines temp inCelsius from Voltage
float tempF = (tempC * 9 / 5) + 32; //Determines temp in Fahrenheit from Celsius
/***************** Begin Loop for alarm if temp is above ***********************/
unsigned char i; //define a variable
if(tempF > alertTemp) {
Serial.println(tempF); //Prints value of tempF variable anbd end line
Serial.print("Celsius Temp: "); //Prints Title
Serial.print(tempC);
Serial.print(" Fahrenheit Temp: "); //Prints Title
delay(1000); //delay for 1000 milliseconds
//output an frequency
for(i=0;i<80;i++) {
digitalWrite(buzzer,HIGH);
delay(1);//wait for 1ms
digitalWrite(buzzer,LOW);
delay(1);//wait for 1ms;
}
//output another frequency
for(i=0;i<100;i++){
digitalWrite(buzzer,HIGH);
delay(2);//wait for 2ms
digitalWrite(buzzer,LOW);
delay(2);//wait for 2ms;
}
}
else {
digitalWrite(buzzer,LOW);
}
/***************** End Loop for alarm if temp is above ***********************/
Serial.println(tempF); //Prints value of tempF variable and end line
Serial.print("Celsius Temp: "); //Prints Title
Serial.print(tempC);
Serial.print(" Fahrenheit Temp: "); //Prints Title
delay(1000); //delay for 1000 milliseconds
}
SEO: Google Web Master Report Showing Multiple 302 Redirects Instead of 404 Page Not Found Error with IIS URL Rewrite and Web.Config File Fix
While reviewing my Google Web Master Reports, I came across an odd error.
When a 404, Page Not Found, error should be showing for a page that has since been deleted, multiple 302s are generated and Google errors out with something like Err too many redirects.
The root cause was a reconfiguration in Microsoft IIS.
I have a custom 404 error page configuration that references the execution of a customer 404 page for a better customer experience.
Below is what the configuration looks like in IIS. It is set to execute a custom 404.asp page instead of the standard 404.htm page included with IIS.
Let’s zoom out and see that there is another error setting.
If we click to edit the “Error” feature setting, we will see another we are editing the custom 404 setting.
This is the correct setting that solved the issue! Scroll down to see the wrong configuration.
This is the wrong or bad or incorrect configuration that caused me some grief.
Excel VBA code example: How to delete all worksheets except for specific ones ones you want to retain.
This came from real world need today so I pounded this code out.
I have a list of worksheets that I want to keep.
I run a process that adds analyzed data from another file to a new “temp” worksheet (tab), and additional worksheets per vendor from parsing out the imported data in the temp worksheet.
I can end up with 10 new tabs of analyzed data including the temp sheet.
The code below deletes all worksheets not on a “keep sheet list” . Basically, resetting the spreadsheet back to the state it was in before I ran macro to import and process that data that added all the new sheets.
Excel VBA Example: ResetWorkbook Function
'Reset Workbook
'Author Rick Cable
'Date: 11/28/2018
'Title: ResetWorkbook
'Purpose: Deletes any workbooks not on the keep sheet list,
' resetting it to previous state if you've added them
' from some other process and want to undo
'Version 1.0
Public Sub ResetWorkbook()
Dim sht As Worksheet
Dim arrKeepSheetList As Variant
Dim strKeepSheetList As Variant
Dim isOnList As Boolean
Application.DisplayAlerts = False
isOnList = False
strKeepSheetList = "Instructions,Template,Sample CSV File,Data Elements,Config"
arrKeepSheetList = Split(strKeepSheetList, ",")
'Loop through each worksheet in ActiveWorkbook
For Each sht In ActiveWorkbook.Worksheets
isOnList = False
'Look thru list of sheets to keep
'if sheet is not on list, delete it
For Each Item In arrKeepSheetList
If sht.Name = Item Then
isOnList = True
'If isOnList Then
' Exit For
'End If
End If
Next Item
If isOnList Then
'MsgBox (sht.Name & " is on the list")
End If
If isOnList = False Then
'MsgBox ("I would be deleting " & sht.Name & " right now!")
Worksheets(sht.Name).Delete
End If
Next sht
Application.DisplayAlerts = True
End Sub
It is Saturday and I’m about to go out and mow my lawn but I had an Excel VBA problem I wanted to get past first.
Not sure this is the best solution, but it is the one I came up with so I’ll share it with you now before I go mow.
Problem: I have a worksheet that is an event log for other subroutines. When created, the worksheet had a header row also created with data in Row 1 across columns A thru G.
When “WriteToEventLog” function call is firing off, we’re checking for next empty row in column A and cell “A1” already has header data written to it.
The method below works when cell “A2” is the starting cell, but fails when cell “A1” is used as the starting Range.
This code fails in an infinite loop and Out of Memory Error when starting from cell “A1” and ends up selecting all the cells memory can hold from column A.
Here is my Saturday morning, pre mow, attempt to solve this issue with a custom Excel VBA function that correctly finds the next empty cell when row 1 contains the header row.
Please email me if you read this and it has helped you in any way.
1st Pass Code:
Sub AddItemToEndOfList()
Worksheets("Sheet1").Activate
'We assume that row 1 is already filled out from first process
'Check to see if cell A2 is empty, if so then enter the next value in A2.
'if not then go to next empty cell and enter the value.
Range("A2").Activate
If IsEmpty(Range("A2").Value) Then
Range("A2").Activate
ActiveCell.Value = "http://wwww.FinditClassifeds.com"
Else
Range("A1").Activate
ActiveCell.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = "http://wwww.FinditClassifeds.com"
End If
End Sub
Sometimes you want to learn something new but you don’t know where to start.
I’ve been working in the Microsoft world & IIS stack so long, I forget how many other web stacks are out there and how many have past us by. OMG!
I wanted to challenge myself to learn how to setup a Cloud hosted, Linux based, web server to host a website.
Where do I start? Well, I just started looking stuff up and decided to blog my learning process along the way.
I hope this helps somebody else with the same desire to learn this topic as quickly and painlessly as possible.
Here we go….
First off, I was not sure what programming language I wanted to use.
This project will use a Linode cloud hosted server and Ubuntu as our operating system.
I started off thinking about a web server using a LAMP stack. I thought it would be OK to consider other options too.
I hate to assume people know tech jargon so I will stop and explain things as best I can along the way. My intent is to inform, not to annoy.
That being said, what is a stack?
A stack is just a term used to describe a collection of all the moving parts required to have a modern functional website. Includes server operating system, web server software, a database and a framework/language.
When we hear the term, “Full Stack Developer”, someone is talking about a developer with experience in all of the aspects of the stack. For example,a LAMP stack is Linux OS + Apache Web Server + MySQL Database + PHP language/framework.
Other server options are replacing Apache with NginX (Pronounced engine x) or building our own using the GO programming language (GoLang).
I started off with Apache then went to NginX.
I got NginX to work as a server so I’m leaving that information in this article at the bottom.
The rest of this article is focused on trying to get a very basic Linux Ubuntu + GOLang stack up and running. No database.
If you and I can get a server up and running, I’m going to celebrate then come back later and add the database.
Let’s do this!
What you need before you start: If you’re using Windows to connect to your Linux server, use Putty to connect via SSH. Details are in the video. Get Putty here.
SSH is a client/server program that enables secure connection to the SSH server on a remote machine. The SSH command is used for remote execution of login, file transfer between machines and executing other remote commands. Step 1. Go to https://www.Linode.com and create an account. You will need a credit card. A web server can be run on Linode for about .50 a day so learning can be inexpensive. You can delete the server anytime and just rebuild it in about 5 minutes.
Once you have your account, watch and follow along with this video.
Video: Setting Up Your Linode Ubuntu Server
2. Once, You’ve installed your server. You should take some time to secure it. Watch this video and follow along.
Installing GO and Use It to Setup a Super Simple Web Server
I tried several times and failed to get Apache running quickly so I moved on to NginX and GOLang since this was supposed to be easy. Use the two videos above to walk you thru building your server operating system and securing it. Then go to https://www.linode.com/docs/development/go/install-go-on-ubuntu/ to get the instruction on how to install GO on Ubuntu. Then watch and follow along with this video to see how to write your own web server and web application using GO. It has all the moving parts built in, you won’t believe how easy it is. Give it a try!
It may be working when you click. Here is a simple page up and running.
Video: Simple Web App – Go Lang Practical Programming Tutorial p.5
Lessons Learned
This is where I’ll stop and relate some Microsoft stack items to what we just did.
When we build sites on the Microsoft stack, we already have a server, Internet Information Services (IIS), it runs on Windows as a background service. If we’re having issues or make an update to the web.config file, we restart the IIS service.
Once you get your simple server working, you will realize it is only running when you run it at the command line. If you exit, your server stops.
We can cheat a bit by using the Linux command nohup which is short for “No Hangups”.
We don’t run nohup by itself, it is a supplemental command that tells Linux not to stop the main command, even if the user logs out.
We can fool our friends in to thinking we’ve built our server by launching our GO app using nohup but this is not the right way to do this. It needs to be running as a background service (daemon).
You will want to learn how to make your GO server run in the background all the time and restart automatically if the server restarts.
I was stuck on this part for a day or so but thank goodness I know the founder of chapbook.com, who is using GOLang so I hit them up and was pointed to the link below.
This link is a good example of how to setup a background service called a daemon in Linux terms.
We’ll do the example on the site above as an exercise to get some daemon code working before we move on to the next task of making our own server code run as a daemon.
It took me a while to get it working but I did. Took longer than I expected as I’m not used to doing everything remotely via SSH and with the nano text editor. Feels like bootcamp.
Here is a screenshot of my successful attempt.
Installing NginX Web Server on Ubuntu
This is an older part of blog where I was going to go the NginX route and had a simple working server up. I’ll come back to update this later.
I started trying to do this using a LAMP stack but had too many problems getting the Apache2 web server to work and was getting tired and frustrated.
After staying up until 0230 failing to get the Apache2 server installed on Ubuntu, exhaustion was setting in and I was feeling the need a confidence boost so I started my Linode server over from scratch and tried this video on getting NginX web server installed and had success. Use this video first, it was much easier.
Video: Setting up a Linode Server and Hosting a Website using NginX Web Server
I did not design the code and the author did not provide their name in it so I could thank them or give them credit. I’ve tested this and it works great!
If you are not in to coding then scroll down to the very bottom of this post where I’ve embedded a video (not mine) of how to export Outlook calendars to Excel using the built in export feature in Outlook. The only issue I have with it is that is does not calculate the duration of the appointments and why I used the script below. Good luck.
Sample Output:
I’ve made a couple of small modifications to the original script: 1. Changed the destination drive from d:\ to c:\ 2. Changed the output date format from dd/mm/yyyy to mm/dd/yyyy
How to make this work?
First, you will need to be on a PC running Windows XP or Windows 7.
Copy the code below and paste it in to any text editor like notepad and save it with a file name like OutlookCalendarExport.vbs.
Now you should be able to double click the file you just saved to run this script. You should get message boxes asking for the start and end dates then it will tell you that it is going off to do its work and will display a message letting you know when it is done. It will save the file on the root of you c: drive.
VBScript: Export Outlook Calendar to Excel
on error resume next
DefaultDrive = "c:\"
set objFS=Wscript.CreateObject("Scripting.FileSystemObject")
If Not objFS.DriveExists(DefaultDrive) Then
msgbox "Your home drive is not mapped. (" & DefaultDrive & ")" & vbcrlf & "Please resolve this before continuing.",48,"Drive Mapping Error"
wscript.quit
end if
starttime = now
' ==== Define Variables
Dim theApp, theNameSpace, theMailItem, calendar, apointmentItems, appointment
' ==== Create a Shell and FileSystem Object
Set WshShell = WScript.CreateObject( "WScript.Shell" )
Set fso = CreateObject("Scripting.FileSystemObject")
' ==== Create Excel Spreadsheet
Set app = CreateObject("Excel.Application")
Set wb = app.Workbooks.Add
app.Visible = False
app.AlertBeforeOverwriting = True
wb.Activate
Row = 1
Column = 1
Set ws = wb.Worksheets(1)
ws.Cells(1,1).Value = "Subject"
ws.Columns(1).ColumnWidth = 50
ws.Cells(1,2).Value = "Date"
ws.Columns(2).ColumnWidth = 9
ws.Cells(1,3).Value = "Start Time"
ws.Columns(3).ColumnWidth = 11
ws.Cells(1,4).Value = "End Time"
ws.Columns(4).ColumnWidth = 11
ws.Cells(1,5).Value = "Duration"
ws.Columns(5).ColumnWidth = 8
ws.Cells(1,6).Value = "Category"
ws.Columns(6).ColumnWidth = 25
Row = Row + 1
OutputFileName = DefaultDrive & "OutlookExport.xls"
' ==== Connect to Outlook
set theApp = CreateObject("Outlook.Application")
set theNameSpace = theApp.GetNameSpace("MAPI")
set calendar = theNameSpace.GetDefaultFolder(9)
set appointmentItems = calendar.Items
' ==== Work out example dates
' ==== Selects Monday to Friday of Previous week as defaults
LastWeek = Weekday(Now)
Select Case LastWeek
Case "1"
Lastweek=Dateadd("D",-6,Date)
Case "2"
Lastweek=Dateadd("D",-7,Date)
Case "3"
Lastweek=Dateadd("D",-8,Date)
Case "4"
Lastweek=Dateadd("D",-9,Date)
Case "5"
Lastweek=Dateadd("D",-10,Date)
Case "6"
Lastweek=Dateadd("D",-11,Date)
Case "7"
Lastweek=Dateadd("D",-12,Date)
End Select
StartofRange = inputbox("Start of Range","Outlook Exporter",LastWeek)
StartofRange=cdate(StartofRange)
EndofRange = inputbox("End of Range","Outlook Exporter",(dateadd("D",+6,LastWeek)))
EndofRange=cdate(EndofRange)
DatetoQuery=StartofRange
If (StartofRange = "") OR (EndofRange = "") Then
wscript.quit
End If
'Popup
Set Shell = wScript.CreateObject("wScript.Shell")
Msg = "Export Started. You will be notified when it is ready."
Timeout = 5
temp = Shell.Popup(Msg, Timeout, "Outlook Exporter")
'if temp = -1 then
' wScript.Quit
'End if
' ==== Create Array to hold valid date range
dim datearray()
numberofdays=DateDiff("D", StartofRange, (dateadd("D",1,EndofRange)))
If Not numberofdays = 0 Then
redim datearray(numberofdays)
Else
redim datearray(1)
End if
' ==== populate the array with the valid dates for the query
datearray(0)=StartofRange
for x=1 to (ubound(datearray)-1)
datearray(x)=dateadd("D",1,(datearray(x-1)))
next
' ==== Find appointments that occur between the StartofRange and EndofRange dates
set appointment = appointmentItems.GetFirst
While TypeName(appointment) <> "Nothing"
if TypeName(appointment) = "AppointmentItem" then
For x = 0 to ubound(datearray)
' ==== Check if appointment is recurring
If appointment.RecurrenceState = 0 then
' =================================================================================
' ====================== Non Recurring appointment ================================
' =================================================================================
If DateValue(appointment.Start) = datearray(x) then
if (appointment.BusyStatus = 2) AND (appointment.sensitivity = 0) Then
Beginning=TimeValue(appointment.start)
Ending=TimeValue(appointment.end)
Duration=DateDiff("N",Beginning,Ending)
if appointment.AllDayEvent then
Beginning="8:30:00"
Ending="5:00:00"
Duration="480"
Else If Duration = "24.00" Then
Duration="480"
End If
End If
ws.Cells(Row,Column).Value = appointment.subject
Column = Column + 1
ws.Cells(Row,Column).Value = DatePart("M",appointment.start) & "/" & DatePart("D",appointment.start) & "/" & DatePart("YYYY",appointment.start)
Column = Column + 1
ws.Cells(Row,Column).Value = Beginning
Column = Column + 1
ws.Cells(Row,Column).Value = Ending
Column = Column + 1
ws.Cells(Row,Column).Value = FixDate(abs(Duration))
Column = Column + 1
ws.Cells(Row,Column).Value = appointment.categories
Row = Row + 1
Column = 1
End If
End If
Else
' =================================================================================
' ====================== Recurring appointment -- get occurrence for date =========
' =================================================================================
Set recurrencePattern = appointment.GetRecurrencePattern
Set recurringApptItem = recurrencePattern.GetOccurrence(datearray(x) & " " & TimeValue(appointment.Start))
errorCode = err.Number
err.Clear
If errorCode = 0 then
set appointment = recurringApptItem
if (appointment.BusyStatus = 2) AND (appointment.sensitivity = 0) Then
Beginning=TimeValue(appointment.start)
Ending=TimeValue(appointment.end)
Duration=DateDiff("N",Beginning,Ending)
if appointment.AllDayEvent then
Beginning="8:30:00"
Ending="5:00:00"
Duration="480"
Else If Duration = "24.00" Then
Duration="480"
End If
End If
ThisOne = """" & appointment.subject & """" & "," & DatePart("M",appointment.start) & "/" & DatePart("D",appointment.start) & "/" & DatePart("YYYY",appointment.start) & "," & Beginning & "," & Ending & "," & Duration & "," & """" & appointment.categories & """" & vbCRLF
If Not ThisOne = LastOne Then
ws.Cells(Row,Column).Value = appointment.subject
Column = Column + 1
ws.Cells(Row,Column).Value = DatePart("M",appointment.start) & "/" & DatePart("D",appointment.start) & "/" & DatePart("YYYY",appointment.start)
Column = Column + 1
ws.Cells(Row,Column).Value = Beginning
Column = Column + 1
ws.Cells(Row,Column).Value = Ending
Column = Column + 1
ws.Cells(Row,Column).Value = FixDate(abs(Duration))
Column = Column + 1
ws.Cells(Row,Column).Value = appointment.categories
Row = Row + 1
Column = 1
LastOne = ThisOne
End If
End If ' status
End If ' error code 0
End If ' appointment.RecurrenceState = 0
Next ' in date array
End If ' TypeName(appointment) = "AppointmentItem"
set appointment = appointmentItems.GetNext
Wend
Beginning = ""
Ending = ""
Duration = ""
' =======================================================================================================
' ============================= Edited Recurring Appointments ===========================================
' =======================================================================================================
Set appointmentItems = calendar.Items
Set appointment = appointmentItems.GetFirst
While TypeName(appointment) <> "Nothing"
If TypeName(appointment) = "AppointmentItem" then
Set RecurrencePattern = appointment.GetRecurrencePattern
Counter = RecurrencePattern.Exceptions.Count
For y = 1 to (Counter)
Set Exception = RecurrencePattern.Exceptions.Item(y)
If (appointment.BusyStatus = 2) AND (appointment.sensitivity = 0) Then
For x = 0 to ubound(Datearray)
If Formatdatetime(DatePart("M",Exception.AppointmentItem.start) & "/" & DatePart("D",Exception.AppointmentItem.start) & "/" & DatePart("YYYY",Exception.AppointmentItem.start)) = formatdatetime(Datearray(x)) Then
Beginning=TimeValue(Exception.AppointmentItem.start)
Ending=TimeValue(Exception.AppointmentItem.end)
Duration=DateDiff("N",Beginning,Ending)
If NOT Beginning = "" Then
ws.Cells(Row,Column).Value = Exception.AppointmentItem.Subject
Column = Column + 1
ws.Cells(Row,Column).Value = DatePart("M",Exception.AppointmentItem.Start) & "/" & DatePart("D",Exception.AppointmentItem.Start) & "/" & DatePart("YYYY",Exception.AppointmentItem.Start)
Column = Column + 1
If Beginning = Ending Then
Beginning="8:30:00 AM"
Ending="5:00:00 PM"
Duration="480"
End If
ws.Cells(Row,Column).Value = Beginning
Column = Column + 1
ws.Cells(Row,Column).Value = Ending
Column = Column + 1
If (Duration = "24.00") OR (Duration = "0") Then
Duration="480"
End If
ws.Cells(Row,Column).Value = FixDate(abs(Duration))
Column = Column + 1
ws.Cells(Row,Column).Value = Exception.AppointmentItem.categories
Row = Row + 1
Column = 1
Beginning = ""
Ending = ""
Duration = ""
End If
End If ' date within range
Next ' ubound(Datearray)
End If ' appointment status
Next 'y - counter
set appointment = appointmentItems.GetNext
End If
wend
' =======================================================================================================
' =======================================================================================================
' =======================================================================================================
Function FixDate(Duration)
Hours = fix(duration / 60)
If (duration - (Hours*60)) < 10 Then
Minutes = "0" & (duration - (Hours*60))
Else
Minutes = (duration - (Hours*60))
End If
If (Hours > 0) AND (Hours < 10) Then
T = "0" & Hours & ":" & Minutes
Else If (Hours > 9 ) Then
T = Hours & ":" & Minutes
Else
T = "00:" & Minutes
End if
End If
FixDate = T
End Function
' =============================================================
' ============ Sort Excel Data and save file ==================
' =============================================================
Const xlAscending = 1
Const xlDescending = 2
Const xlGuess = 0
Const xlTopToBottom = 1
app.Selection.Sort app.Worksheets(1).Range("F2"), _
xlAscending, _
app.Worksheets(1).Range("B2"), _
, _
xlAscending, _
, _
, _
xlGuess, _
1, _
False, _
xlTopToBottom
app.DisplayAlerts = False
err.clear
ws.SaveAs OutputFileName
If err.number = 1004 Then
msgbox err.description & vbcrlf & vbcrlf & "Outlook Exporter cannot continue."
err.clear
wscript.quit
End If
wscript.sleep 2000
' ==== Tidy Up
set appointmentItems = nothing
set calendar = Nothing
theNameSpace.Logoff
set theNameSpace = Nothing
set theApp = Nothing
set fso = Nothing
Set ws = Nothing
wb.close
Set wb = Nothing
' ==== Exit Excel - important if visible = false
app.quit
set app = Nothing
endtime = now
TimeToComplete = formatdatetime(endtime - starttime)
' ==== Inform that the report is finished
Retval = msgbox("Report duration was " & TimeToComplete & vbcrlf & vbcrlf & "View results now?",vbyesno + vbinformation,"Outlook Exporter")
If Retval = vbYes then
wsHshell.Run (OutputFileName)
End If
Set WshShell = Nothing
' ===========================================================================================================
' ================================================== Notes ==================================================
' ===========================================================================================================
' Lists appointments from Outlook Calander
' Includes only those appointments in user specified date range
' Includes regular , recurring and edit-recurring appointments
' Only lists appointments where Busy and not Private
' If alldayevent then duration listed as 8 hours, start 8.30, stop 5pm
' If 24 hour event the duration listed as 8 hours, start 8.30, stop 5pm
' Converts duration to HH:MM format
' Sorts by Date, then start time
' Saves as H:\OutlokExport.xls
