Excel importing dates from a RTF in a macro

  • Thread starter Thread starter Andy D
  • Start date Start date
A

Andy D

If I open a RTF with a date in, the date appears as it does in the
original RTF. If however, if I run Office 2000 and record the above
event using a macro, when played the date swaps the mm and dd round
(without changing the properties, so in fact the mm is now the dd and
the dd is now the mm) This only occurs if the dd is 12 or below in
value for obvious reasons.
I then tried to open the RTF date column as text, and then change the
column properties after it is imported. This does work fine, but I am
using the Application.Filesearch and am not sure how to integrate the
OpenText function which can then go on to specify to open column 2 as
text.

Here is an extract out of the macro:

For i = 1 To .FoundFiles.Count
Application.DisplayAlerts = False
Set wkb = Workbooks.Open(.FoundFiles(i),
False)

Somehow i need to integrate this coding:

Workbooks.OpenText Filename:=[filename]_
, Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited,
TextQualifier _
:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:= _
False, Comma:=False, Space:=False, Other:=False,
FieldInfo:=Array(Array _
(1, 1), Array(2, 2))


Have fun, I cant come up with an idea that works!
Andy
 
Excel respects your windows settings on how you do dates.

VBA is USA centric and doesn't even look at your settings.

But if you're using the code you recorded, you should be able to set the date
the way you want.

You can specify which order your date is in: mdy, dmy, ymd, etc.

This is the portion that'll get changed:

Array(Array(1, 1), Array(2, 2))

The second number is the type of field you want (1=General, 2=Text, then come a
bunch of date formats.)





Andy said:
If I open a RTF with a date in, the date appears as it does in the
original RTF. If however, if I run Office 2000 and record the above
event using a macro, when played the date swaps the mm and dd round
(without changing the properties, so in fact the mm is now the dd and
the dd is now the mm) This only occurs if the dd is 12 or below in
value for obvious reasons.
I then tried to open the RTF date column as text, and then change the
column properties after it is imported. This does work fine, but I am
using the Application.Filesearch and am not sure how to integrate the
OpenText function which can then go on to specify to open column 2 as
text.

Here is an extract out of the macro:

For i = 1 To .FoundFiles.Count
Application.DisplayAlerts = False
Set wkb = Workbooks.Open(.FoundFiles(i),
False)

Somehow i need to integrate this coding:

Workbooks.OpenText Filename:=[filename]_
, Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited,
TextQualifier _
:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:= _
False, Comma:=False, Space:=False, Other:=False,
FieldInfo:=Array(Array _
(1, 1), Array(2, 2))

Have fun, I cant come up with an idea that works!
Andy
Click to expand...
 
Back
Top