Prev: Organising sales data by month
Next: Advanced filter VS formula in cell
From: Arvi Laanemets on 23 Mar 2010 07:24
Hi

I have several cases, where I need to read some data from client table
(delivery prognoses), and create a text file based on those data. For every
such project, I create an Excel table where some parameters are defined, and
a macro is started, which asks for source file, opens it, reads data,
creates or opens a text file, and writes a new table into it. So long I had
Excel, text, or SCV files as source data, and Office 2000 on all users
computers, and there were no serious problems fith reading data in.


Now 2 major changes occurred. In some computers, Excel 2007 is installed.
And lately I had to convert a table provided in HTML format (Excel table
saved in HTML format). The table contains some column headings which
originally were dates formatted as "mmmdd", like MAR12, APR26, MAY03, etc.
The problem is, that depending from which computer I'm trying to convert
data, those datetext values are interpreted by Excel differently!
(I haven't tested my older projects on Office2007 jet - maybe the problem is
more serious)

I tested new application on 3 different instances: my computer (Windows 7,
Office 2007), VMWare instance on my computer (Windows XP, Office 2000), and
terminal server (Windows Server 2003, Office 2000)
On all instances, Window's regional settings are set to Estonian (i.e. short
forms for months are p.e. m�rts, apr, mai).

On my computer
when opening the HTML file from Excel: MAR12 is read as "MAR12", APR26
is read as 01.04.2026, MAY03 is interpereted as "MAY03"
when opening the HTML file from VBA: MAR12 is read as "MAR12", APR26 is
read as 01.04.2026, MAY03 is interpereted as "MAY03"

On VMWare instance
when opening the HTML file from Excel: MAR12 is interpreted as "MAR12",
APR26 is interpreted as 01.04.2026, MAY03 is interpereted as "MAY03"
when opening the HTML file from VBA: MAR12 is interpreted as
12.03.2010, APR26 is interpreted as 26.04.2010, MAY03 is interpereted as
03.05.2010

On Terminal Server
when opening the HTML file from Excel: MAR12 is interpreted as "MAR12",
APR26 is interpreted as 01.04.2026, MAY03 is interpereted as "MAY03"
when opening the HTML file from VBA: MAR12 is interpreted as
12.03.2010, APR26 is interpreted as 26.04.2010, MAY03 is interpereted as
03.05.2010


So it looks like Excel 2000 interpreted such date strings in files opened
directly or opened from VBA differently - and VBA handled them right way. In
Excel 2007 such date strings are handled in same way regardless was the file
opened directly or from VBA - and wrong way in both cases!

I need my applications to work regardless Excel version, as I never can
control, which version may be on user's computer.


Is there a way to resolve this problem?


Thanks in advance!
Arvi Laanemets

From: JLatham on 23 Mar 2010 23:56
Sounds like you're going to need separate code to deal with the different
versions of Excel?
You can determine which version is running using
Application.Version
which returns a string with the version number in it. Excel 2007 is "12.0".

So you can make a test like this:

If Val(Application.Version)<12 Then
'running pre-2007 version of Excel
Run "ProcessAsXL2000"
Else
'running Excel 2007 (or later)
Run "ProcessAsXL2007"
End If

"Arvi Laanemets" wrote:

> Hi
>
> I have several cases, where I need to read some data from client table
> (delivery prognoses), and create a text file based on those data. For every
> such project, I create an Excel table where some parameters are defined, and
> a macro is started, which asks for source file, opens it, reads data,
> creates or opens a text file, and writes a new table into it. So long I had
> Excel, text, or SCV files as source data, and Office 2000 on all users
> computers, and there were no serious problems fith reading data in.
>
>
> Now 2 major changes occurred. In some computers, Excel 2007 is installed.
> And lately I had to convert a table provided in HTML format (Excel table
> saved in HTML format). The table contains some column headings which
> originally were dates formatted as "mmmdd", like MAR12, APR26, MAY03, etc.
> The problem is, that depending from which computer I'm trying to convert
> data, those datetext values are interpreted by Excel differently!
> (I haven't tested my older projects on Office2007 jet - maybe the problem is
> more serious)
>
> I tested new application on 3 different instances: my computer (Windows 7,
> Office 2007), VMWare instance on my computer (Windows XP, Office 2000), and
> terminal server (Windows Server 2003, Office 2000)
> On all instances, Window's regional settings are set to Estonian (i.e. short
> forms for months are p.e. märts, apr, mai).
>
> On my computer
> when opening the HTML file from Excel: MAR12 is read as "MAR12", APR26
> is read as 01.04.2026, MAY03 is interpereted as "MAY03"
> when opening the HTML file from VBA: MAR12 is read as "MAR12", APR26 is
> read as 01.04.2026, MAY03 is interpereted as "MAY03"
>
> On VMWare instance
> when opening the HTML file from Excel: MAR12 is interpreted as "MAR12",
> APR26 is interpreted as 01.04.2026, MAY03 is interpereted as "MAY03"
> when opening the HTML file from VBA: MAR12 is interpreted as
> 12.03.2010, APR26 is interpreted as 26.04.2010, MAY03 is interpereted as
> 03.05.2010
>
> On Terminal Server
> when opening the HTML file from Excel: MAR12 is interpreted as "MAR12",
> APR26 is interpreted as 01.04.2026, MAY03 is interpereted as "MAY03"
> when opening the HTML file from VBA: MAR12 is interpreted as
> 12.03.2010, APR26 is interpreted as 26.04.2010, MAY03 is interpereted as
> 03.05.2010
>
>
> So it looks like Excel 2000 interpreted such date strings in files opened
> directly or opened from VBA differently - and VBA handled them right way. In
> Excel 2007 such date strings are handled in same way regardless was the file
> opened directly or from VBA - and wrong way in both cases!
>
> I need my applications to work regardless Excel version, as I never can
> control, which version may be on user's computer.
>
>
> Is there a way to resolve this problem?
>
>
> Thanks in advance!
> Arvi Laanemets
>
> .
>
From: Arvi Laanemets on 24 Mar 2010 02:09
Thanks!

It looks like from Office 20007 on, VBA handles data accordingly to local
regional settings. Earlier versions handled them accordingly to US settings.
Bad part in this is inconsistency!

I think I know another of my projects, which may have problems. It uses a
csv-file from Swedish customer as source, where decimals are marked with
comma, and with Excel 2000 VBA I had to divide all read numbers with 1000.
In Estonian regional settings, comma is decimal delimiter too.


Arvi Laanemets


 | 
Pages: 1
Prev: Organising sales data by month
Next: Advanced filter VS formula in cell