Importing contents of Outlook message to Excel

[Katie6]

Hi there,

I'd be really grateful for any help with sorting out the date format in my Outlook to Excel importer.

Background
I've got an online form, and when the user submits it their response is sent to my email address.

I receive the form responses in the following format, with a semicolon separating each form field:

message;location;page_url;date time

example:

This is a good article;UK;

http://www.site.com/page.asp;12/03/2010

14:47:00

Every day I transfer all the form responses from Outlook into Excel by clicking a button in Excel which runs the following VB code. This puts each form field (separated by a semicolon) in its own cell.

VB code

Sub Button1_Click()

Set olApp = GetObject("", "Outlook.Application")
Set MyFolder = olApp.ActiveExplorer.CurrentFolder
Set MyItems = MyFolder.Items
i = 0

For Each SpecificItem In MyItems
i = i + 1
'Range("A" & i).Select
'ActiveCell.FormulaR1C1 = SpecificItem.Body
s = s & Left(SpecificItem.Body, Len(SpecificItem.Body) - 1) & vbCrLf

Next

Open "c:\temp\survey.txt" For Output As #1
Print #1, s
Close #1

ChDir "C:\TEMP"
Workbooks.OpenText FileName:="C:\TEMP\survey.txt", Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), _
Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array( _
16, 1), Array(17, 1))
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select

MsgBox (i & " files have been read and imported into Excel.")

End Sub


The problem
The problem is that sometimes the date format screws up. For example in the example given in the background section above, the date appears in Outlook as:

12/03/2010 14:47:00

But comes through to Excel as:

03/12/2010 14:47:00

with the day and month swapped around. This doesn't happen to all dates - some are unaffected, for example 24/03/2010 14:03:20 appears correctly in both Outlook and Excel.

Possible cause
I think all dates with a day of 12 or less are interpreted as dates in an American format and are passed through to Excel with the day and month swapped around, while all dates with a day of 13 or more come through as text and the day and month are unchanged.

How can I get all dates to be interpreted as either a date or text and not a mixture of both?

Hope that makes sense, but please let me know if it doesn't!

Many thanks,

Katie

P.S. I'm using XP and Excel 2003

 

[Bluejay07]

One thing to check is how the computer system date format is set up, specifically the short date.

To verify this using windows XP:
- Control Panel
- Region and Language Options
- Regional Options Tab
- Customize Button
- Date Tab
- Set the short date format to the desired format eg. MM/dd/yyyy

Using Vista or 7:
- Control Panel
- Clock, Language, and Region
- Region and Language
- Formats Tab
- Additional settings button
- Date Tab
- Set the short date format.

I hope this helps.

 

[Katie6]

Many thanks for your speedy response. My problem is that I work in a big company and many people use the Email-to-Excel tool. So there's no way I can set everyone's computers

I was thinking that ideally I'd be able to check for columns that are in the following format:

number number/number number/number number number number number number:number number:number number

For example:
12/03/2010 14:47:00

And any cells that are in this format should be interpreted as a date in British format.

My trouble is that although I have experience in coding in a few languages, Visual Basic is not one of them! So if someone could translate my idea into code or point me in the right direction, that would be amazing! Or perhaps there is a better way to do it?

I think I do need to solve the problem in Visual Basic, though, because of the issue with many people using the Excel tool.

Thanks again

Katie

 

[strongm]

Something like the following should help here

[blue]Workbooks.OpenText FileName:="C:\TEMP\survey.txt", Origin:=xlWindows, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, Comma:=False _
        , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
        Array(3, 1), Array(4, [b][red]4[/red][/b]))[/blue]

 

[Katie6]

Hi strongm,

Many thanks for your help - I'm guessing that changing to a 4 is designed to affect the date column. The problem is that the date could be in any column.

The Excel tool is designed to deal with any form and so the date could be anywhere, not necessarily the fourth column.

Let me know if I've missed the point!

Many thanks,

Katie

 

[strongm]

No, you have not missed the point - but you have moved the goalposts! In your original post you say:

I receive the form responses in the following format, with a semicolon separating each form field:

message;location;page_url;date time

Mind you, in retrospect I'm not certain it will work as it gets trumped by Excel's date recognition algorithms which get to examine the column first ...

 

[Katie6]

Sorry, didn't mean to move the goalposts!
I'm now thinking of a new method: it's only the cells that are automatically interpreted as dates that screw up, so is there a way to force every single cell to come through as text?

 

[SkipVought]

If every column comes thru as TEXT, then your "dates" will not be dates, unless you do another bit of code to CONVERT text to date.

In Excel, turn on your macro recorder and record doing a text to columns, with a column format of TEXT. This will tell you the value that goes in the Array(psoition,column format)

The follow on process will need to look for each date column, logic you need to provide, and then do a Text to Columns on that column, using the appropriate DMY or MDY column conversion format.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]