Excel VBA: Reading data from HTML-file

[Arvi Laanemets]

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

 

[JLatham]

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]

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