ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   International HTML Import Problem (https://www.excelbanter.com/excel-programming/333836-international-html-import-problem.html)

aldsv
International HTML Import Problem
 

My users are emailed an "excel" file each day from the USA. This file
is an extract from a website. It is coded as html (see sample below).
The problem is that the date is coded as mm/dd/yyyy. My users are
Excel/German date format is is dd/mm/yyyy.

I am writing a macro that opens this file, copies the data, and pastes
it into another spreadsheet. When my users open the data file, their
native language Excel automatically converts the date (in the example
below, it converts to an text because 25/10/2005 is not a valid date),
and I'm hosed before I begin.

1) The data set is 10,000 elements per day.
2) There are 35 columns. I'm only showing you one column.
3) We are using Excel 2000 & 2002

I can't read this file as a text file because it's in HTML.
I cannot ask users to change their local language settings.

Suggestions? Please help!!!

Is there a way to write code that will temporarily change the Excel
language setting for the user, open the data file, copy the data, and
change the language setting back?


<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"
<html
<head
<meta content="Microsoft Visual Studio 7.0" name=GENERATOR
<meta content=C# name=CODE_LANGUAGE
<meta content=JavaScript name=vs_defaultClientScript
<meta content=http://schemas.microsoft.com/intellisense/ie5
name=vs_targetSchema
</head

<td align="Center"Customer Required Date</td 10/25/2005


--
aldsv
------------------------------------------------------------------------
aldsv's Profile: http://www.excelforum.com/member.php...o&userid=20494
View this thread: http://www.excelforum.com/showthread...hreadid=385185

DM Unseen
International HTML Import Problem
 
aldsv

regional options can be set using API calls, see

http://www.freevbcode.com/ShowCode.asp?ID=116

BTW maybe try ti load the HTML as a webquery might work(VBA or
manually). The queryTable object is quite versatile on loading
webpages.

Dm Unseen

keepITcool
International HTML Import Problem
 


as my fellow dutchman pointed out you need api's.

and I've tried to write a function for it.
the Regional settings in Control Panel ARE updated.
but I'm not sure that excel (or other proggies) are aware of it.

PLEASE LET ME KNOW and i'll figure a way !


here's a function.
It stores the user's original as a static, and changes to MM/DD
call it again and it resets to the users' original.

so be sure to call before and after your import routine.

Option Explicit

Private Const LOCALE_SSHORTDATE As Long = &H1F

Private Declare Function GetUserDefaultLCID Lib _
"kernel32.dll" () As Long

Private Declare Function GetLocaleInfo Lib "kernel32.dll" _
Alias "GetLocaleInfoA" (ByVal Locale As Long, _
ByVal LCType As Long, ByVal lpLCData As String, _
ByVal cchData As Long) As Long

Private Declare Function SetLocaleInfo Lib "kernel32" Alias _
"SetLocaleInfoA" (ByVal Locale As Long, _
ByVal LCType As Long, ByVal lpLCData As String) As Boolean
Private Declare Function SetThreadLocale Lib "kernel32.dll" ( _
ByVal Locale As Long) As Long


Function ToggleLocaleMDY() As String

Dim lPtr&, lRet&, lLen&, sData$
Static sOriginal As String

'more info:
'http://msdn.microsoft.com/library/
default.asp?url=/library/en-us/intl/nls_34rz.asp

'get the address of the user's locale
lPtr = GetUserDefaultLCID()

If sOriginal = vbNullString Then
'1st call: store and change

'prepare buffer
lLen = 256
sData = Space(lLen)
'read data
lRet = GetLocaleInfo(lPtr, LOCALE_SSHORTDATE, sData, lLen)
sOriginal = Left(sData, lLen)

lRet = SetLocaleInfo(lPtr, LOCALE_SSHORTDATE, "MM/dd/yyyy")
If lRet = 0 Then
MsgBox "can't set the date format"
Else
lRet = SetThreadLocale(lPtr)
ToggleLocaleMDY = "MM/dd/yyyy"
End If

Else
'2nd call.. reset to user's original
lRet = SetLocaleInfo(lPtr, LOCALE_SSHORTDATE, sOriginal)

If lRet = 0 Then
MsgBox "can't reset user's original date format"
Else
lRet = SetThreadLocale(lPtr)
ToggleLocaleMDY = sOriginal
sOriginal = vbNullString
End If
End If
End Function

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


aldsv wrote :


My users are emailed an "excel" file each day from the USA. This file
is an extract from a website. It is coded as html (see sample
below). The problem is that the date is coded as mm/dd/yyyy. My
users are Excel/German date format is is dd/mm/yyyy.

I am writing a macro that opens this file, copies the data, and pastes
it into another spreadsheet. When my users open the data file, their
native language Excel automatically converts the date (in the example
below, it converts to an text because 25/10/2005 is not a valid date),
and I'm hosed before I begin.

1) The data set is 10,000 elements per day.
2) There are 35 columns. I'm only showing you one column.
3) We are using Excel 2000 & 2002

I can't read this file as a text file because it's in HTML.
I cannot ask users to change their local language settings.

Suggestions? Please help!!!

Is there a way to write code that will temporarily change the Excel
language setting for the user, open the data file, copy the data, and
change the language setting back?


<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"
<html
<head
<meta content="Microsoft Visual Studio 7.0" name=GENERATOR
<meta content=C# name=CODE_LANGUAGE
<meta content=JavaScript name=vs_defaultClientScript
<meta content=http://schemas.microsoft.com/intellisense/ie5
name=vs_targetSchema
</head

<td align="Center"Customer Required Date</td 10/25/2005

DM Unseen
International HTML Import Problem
 
Nice code KIC,
might come in handy one day!

DM Unseen

PS (Code on your website, so I can change my link?)

keepITcool
International HTML Import Problem
 

code not properly functioning.

removed the setthreadlocale
added a broadcast..

xl97 and xlXP and my newsreader now properly react.
xl2003 still gives problems.. ARGHH...
I'm doing further research. will get back


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


keepITcool wrote :



as my fellow dutchman pointed out you need api's.

and I've tried to write a function for it.
the Regional settings in Control Panel ARE updated.
but I'm not sure that excel (or other proggies) are aware of it.

PLEASE LET ME KNOW and i'll figure a way !


here's a function.
It stores the user's original as a static, and changes to MM/DD
call it again and it resets to the users' original.

so be sure to call before and after your import routine.

Option Explicit

Private Const LOCALE_SSHORTDATE As Long = &H1F

Private Declare Function GetUserDefaultLCID Lib _
"kernel32.dll" () As Long

Private Declare Function GetLocaleInfo Lib "kernel32.dll" _
Alias "GetLocaleInfoA" (ByVal Locale As Long, _
ByVal LCType As Long, ByVal lpLCData As String, _
ByVal cchData As Long) As Long

Private Declare Function SetLocaleInfo Lib "kernel32" Alias _
"SetLocaleInfoA" (ByVal Locale As Long, _
ByVal LCType As Long, ByVal lpLCData As String) As Boolean
Private Declare Function SetThreadLocale Lib "kernel32.dll" ( _
ByVal Locale As Long) As Long


Function ToggleLocaleMDY() As String

Dim lPtr&, lRet&, lLen&, sData$
Static sOriginal As String

'more info:
'http://msdn.microsoft.com/library/
default.asp?url=/library/en-us/intl/nls_34rz.asp

'get the address of the user's locale
lPtr = GetUserDefaultLCID()

If sOriginal = vbNullString Then
'1st call: store and change

'prepare buffer
lLen = 256
sData = Space(lLen)
'read data
lRet = GetLocaleInfo(lPtr, LOCALE_SSHORTDATE, sData, lLen)
sOriginal = Left(sData, lLen)

lRet = SetLocaleInfo(lPtr, LOCALE_SSHORTDATE, "MM/dd/yyyy")
If lRet = 0 Then
MsgBox "can't set the date format"
Else
lRet = SetThreadLocale(lPtr)
ToggleLocaleMDY = "MM/dd/yyyy"
End If

Else
'2nd call.. reset to user's original
lRet = SetLocaleInfo(lPtr, LOCALE_SSHORTDATE, sOriginal)

If lRet = 0 Then
MsgBox "can't reset user's original date format"
Else
lRet = SetThreadLocale(lPtr)
ToggleLocaleMDY = sOriginal
sOriginal = vbNullString
End If
End If
End Function

keepITcool
International HTML Import Problem
 

it's worse..
all OTHER application react to the broadcast
EXCEPT the excel instance running the code.

somebody has an idea?


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


keepITcool wrote :


code not properly functioning.

removed the setthreadlocale
added a broadcast..

xl97 and xlXP and my newsreader now properly react.
xl2003 still gives problems.. ARGHH...
I'm doing further research. will get back

aldsv[_2_]
International HTML Import Problem
 

Thanks. I'm not much of a programmer. Hopefully I can understand wha
you post.

I guess it was wishful thinking that that there would be a simpl
"flag" that could be flipped back and forth in Excel.

--alds

--
alds
-----------------------------------------------------------------------
aldsv's Profile: http://www.excelforum.com/member.php...fo&userid=2049
View this thread: http://www.excelforum.com/showthread.php?threadid=38518

keepITcool
International HTML Import Problem
 

walked the dog..ended up in the pub...

BUT I set down and managed to finalize the code
hope it goes ok and you understand.

copy the thing the a new module.

I'm off to my bed
Cheerz!


Option Explicit
Private Const LOCALE_USER_DEFAULT As Long = &H400
Private Const LOCALE_SSHORTDATE As Long = &H1F
Private Const HWND_BROADCAST As Long = &HFFFF&
Private Const WM_SETTINGCHANGE As Long = &H1A

Private Declare Function GetLocaleInfo Lib "kernel32.dll" _
Alias "GetLocaleInfoA" ( _
ByVal Locale As Long, _
ByVal LCType As Long, _
ByVal lpLCData As String, _
ByVal cchData As Long) As Long
Private Declare Function SetLocaleInfo Lib "kernel32.dll" _
Alias "SetLocaleInfoA" ( _
ByVal Locale As Long, _
ByVal LCType As Long, _
ByVal lpLCData As String) As Long
Private Declare Function SendMessage Lib "user32.dll" _
Alias "SendMessageA" ( _
ByVal hwnd As Long, _
ByVal wMsg As Long, _
ByVal wParam As Long, _
ByRef lParam As Any) As Long

Sub OpenUShtmlfile()

Dim appXLS As Application
Dim wkb As Workbook
Dim sHtm$, sXls$

sHtm = Application.GetOpenFilename( _
"html datafiles,*.htm*")
If sHtm = [False] Then Beep: Exit Sub

sXls = Replace(sHtm, ".html", ".xls", compa=1)
sXls = Replace(sXls, ".htm", ".xls", compa=1)
If Dir(sXls) < vbNullString Then
If vbOK = MsgBox("Target file (" & sXls & _
") already exists." & vbLf & "Delete?", _
vbOKCancel + vbQuestion) Then
On Error Resume Next
Kill sXls
If Err Then
MsgBox "Error deleting " & sXls & vbLf & _
Err.Description & vbLf & "aborting..", vbCritical
Exit Sub
End If
On Error GoTo 0
Else
Exit Sub
End If
End If

'Change MDY setting (alas: doesn't change this instance)
ToggleMDY
'open a new instance of excel (hidden)
Set appXLS = New Excel.Application
'open the html
Set wkb = appXLS.Workbooks.Open(sHtm)
'save as an xls
wkb.SaveAs sXls, xlWorkbookNormal
'close book
wkb.Close
'deref
Set wkb = Nothing
'close the extra instance
appXLS.Quit
'deref
Set appXLS = Nothing

'set MDY back to the old settings
ToggleMDY

'open in THIS instance
Set wkb = Workbooks.Open(sXls)

End Sub


Function ToggleMDY() As String
'Toggles the setting of the ControlPanels "Shortdate" string.
'affects all running applications EXCEPT this instance of excel.

Dim lRet&, lLen&, sData$
Static sUser$

Const MDY_MASK = "MM/dd/yy"

If sUser = vbNullString Then
'1st call: store and change
'prepare buffer
lLen = 96: sData = Space(lLen)
'read data
lRet = GetLocaleInfo(LOCALE_USER_DEFAULT, LOCALE_SSHORTDATE, _
sData, lLen)
sUser = Trim(sData)
'set data
lRet = SetLocaleInfo(LOCALE_USER_DEFAULT, LOCALE_SSHORTDATE, _
MDY_MASK)
ToggleMDY = "MDY"
Else
'2nd call.. reset the original string
lRet = SetLocaleInfo(LOCALE_USER_DEFAULT, LOCALE_SSHORTDATE, _
sUser)
sUser = vbNullString
ToggleMDY = "USR"
End If

'Notify system
lRet = SendMessage(HWND_BROADCAST, WM_SETTINGCHANGE, 0&, ByVal 0&)

End Function





--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


aldsv wrote :


Thanks. I'm not much of a programmer. Hopefully I can understand
what you post.

I guess it was wishful thinking that that there would be a simple
"flag" that could be flipped back and forth in Excel.

--aldsv

DM Unseen
International HTML Import Problem
 
KIC,

slept well?<vbg

I see the issue is somewhat trickier that we thought. I suspect the
reason for your problem is that XL stil has its own date settings
internally (like XL 97) and hence actually caches date settings (but
you probably already guessed that)

(PS I think using a querytable and creaing a webquery and setting
WebDisableDateRecognition to true and then do some extra formula/VBA
work would be better, but that's just my 2p.)

aldsv, if tyou find the code is too tricky you could try the folloiwng:

enable Macro Recorder and start recording
go to Data -Get External Data-new webquery and select/enter your
local HTML file, Also click "advanced"and disable date recognition! You
might need to tweak some other settings as well.
get the data back and close the recorder.
Now use formulas(or VBA) to convert the dates manually, see
http://www.ozgrid.com/Excel/convert-...te-formats.htm for examples
on formulas.

Combine these and put it in your own code.

DM Unseen

keepITcool
International HTML Import Problem
 

datesettings cached.. hmm but
excel will react imm. to (applied) changes on the control panel
(which CP also does via a broadcast of setting change msg.)


your solution wont work, or work the same as opening the html.
if xl in "DMY mode" then dates stored in MDY are read
and interpreted.. producing a mixed bag of "swapped" dates or strings
when the day 12.

my solution needs to open a new instance. because the instance running
the proc will "drop" or miss the broadcasted message. all other excel
instances DO respond to the broadcast.

but it DOES work.


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


DM Unseen wrote :

KIC,

slept well?<vbg

I see the issue is somewhat trickier that we thought. I suspect the
reason for your problem is that XL stil has its own date settings
internally (like XL 97) and hence actually caches date settings (but
you probably already guessed that)

(PS I think using a querytable and creaing a webquery and setting
WebDisableDateRecognition to true and then do some extra formula/VBA
work would be better, but that's just my 2p.)

aldsv, if tyou find the code is too tricky you could try the
folloiwng:

enable Macro Recorder and start recording
go to Data -Get External Data-new webquery and select/enter your
local HTML file, Also click "advanced"and disable date recognition!
You might need to tweak some other settings as well.
get the data back and close the recorder.
Now use formulas(or VBA) to convert the dates manually, see
http://www.ozgrid.com/Excel/convert-...te-formats.htm for
examples on formulas.

Combine these and put it in your own code.

DM Unseen

keepITcool
International HTML Import Problem
 

walked the dog again (no pub).. did some thinking.

Just open the file then run following

If imported NUMBERS are already converted to dates
this thing cant handle it. But does a nice job on "mixed bags"
of "swapped" dates and datestrings and other text.


Sub DMYDateSwapper()
Dim rCol As Range
If Selection.Count = 1 Then
ActiveSheet.UsedRange.Select
Else
Intersect(Selection, Selection.Parent.UsedRange).Select
End If

For Each rCol In Selection.Columns
With rCol
If Application.CountA(.Cells) 0 Then
.TextToColumns Destination:=.Cells(1), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=False, _
FieldInfo:=Array(0, xlDMYFormat), _
DecimalSeparator:=".", _
ThousandsSeparator:="'", _
TrailingMinusNumbers:=True
'Note:
' Deci/Thou are only applic in newer xlVersions.
' remove if you need to work in xl97
End If
End With
Next

End Sub





--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


aldsv wrote :


Thanks. I'm not much of a programmer. Hopefully I can understand
what you post.

I guess it was wishful thinking that that there would be a simple
"flag" that could be flipped back and forth in Excel.

--aldsv

DM Unseen
International HTML Import Problem
 
OK. I see what you're getting at, XL cannot process the broadcast
because it is actually processing the VBA, but it should queu the
messages , and process it afterwards?

My solution should import the HTML dates as pure text(that is how I
read the XL help on that), and will not produce any dates(corret me if
i'm wrong;) Datefixing is then another action.

DM Unseen

aldsv[_3_]
International HTML Import Problem
 

Guys,

Thank you for your help. The short answer is that there is no "simple"
way, but I'm going to take you suggestion and use the WebQuery & some
datefixing to format the data.

Then I'm going to go beat our WebDev team and get them to start
exporting the data as either CSV or native XLS format (I have no idea
why you would export data into an html format?).

Cheers--Al


--
aldsv
------------------------------------------------------------------------
aldsv's Profile: http://www.excelforum.com/member.php...o&userid=20494
View this thread: http://www.excelforum.com/showthread...hreadid=385185

tui
International HTML Import Problem
 

Copy and paste the following html/xml code to an ascii file and give it
an extension of '.xls' . Click on it to open an Excel spreadsheet with
a number of differently formatted dates in it. Some of these use the
english date formatting characters 'y d and m' which may cuase problems
in Germany as they use 't m and j' but there is a format of 'Medium
Date' that might work.

All of the opening HTML header tag is required for this to work. The
rest is standard HTML with the exception of the MSO style setting.

I'm trying to solve the same problem myself and this is by way of an
experiment. Unfortunately I don't have a native German Windows OS PC
to work on. Changing the Regional Options to German on a US PC doesn't
work because the characters used for date formatting are still English
'd m y' so I'm looking for something that is language neutral.

OK, I am also entering the date in MS internal format (count of days
from 01-01-1900. You can calculate that in the application that writes
out the file.


<!-- this block enables the XML statements to work --
<html
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40"

<head
<titleTest HTML table to Excel</title

</head
<body
<table
<!-- use col tags to simplify column width settings --
<col width=20
<col width=150
<col width=100
<col width=100
<col width=100

<tr
<td</td
<td colspan=4 align="center" bgcolor=#ddffffControlling Date
Formats</td
</tr

<tr
<td</td
<td align=right bgcolor=#ddffffDisplayed</td
<td bgcolor=#ddffff</td
<td colspan=2 bgcolor=#ddffffFormat Mask</td
</tr

<tr
<td</td
<td style="mso-number-format:'yyyy\/mmm\/dd'" x:num="38548"</td
<td</td
<td colspan=2"yyyy\/mmm\/dd"</td
</tr

<tr
<td</td
<td style="mso-number-format:'mm\.dd\.yyyy'" x:num="38548"</td
<td</td
<td colspan=2"mm\.dd\.yyyy"</td
</tr

<tr
<td</td
<td style="mso-number-format:'mm\.yyyy\.dd'" x:num="38548"</td
<td</td
<td colspan=2"mm\.yyyy\.dd"</td
</tr

<tr
<td</td
<td style="mso-number-format:'mm\$yyyy\$dd'" x:num="38548"</td
<td</td
<td colspan=2"mm\$yyyy\$dd"</td
</tr

<tr
<td</td
<td style="mso-number-format:'dd\/mm\/yy'" x:num="38548"</td
<td</td
<td colspan=2"dd\/mm\/yy"</td
</tr

<tr
<td</td
<td style="mso-number-format:'0'" x:num="38548"</td
<td</td
<td colspan=2 align=left"0"</td
</tr


<tr
<td</td
<td style="mso-number-format:'Medium Date'" x:num="38548"</td
<td</td
<td colspan=2 align=left"Medium Date"</td
</tr

<tr
<td</td
<td style="mso-number-format:'Long Date'" x:num="38548"</td
<td</td
<td colspan=2 align=left"Long Date"</td
</tr

<tr
<td</td
<td style="mso-number-format:'Short Date'" x:num="38548"</td
<td</td
<td colspan=2 align=left"Short Date"</td
</tr

<tr
<td</td
<td align=rightIllegal format</td
<td</td
<td colspan=2 align=left"Date"</td
</tr


<tr
<td</td
<td colspan=4 bgcolor=#ddffff
Note: In all cells above the date was entered as a
whole number (38548) not in a date format using day, month and year.
&nbsp;&nbsp;The value was entered as an Excel XML statement
(x:num="38548")
while the formatting was done with a style setting
(style="mso-number-format:'dd\/mmm\/yyyy'")
</td
</tr


</table
</body
</html


--
tui
------------------------------------------------------------------------
tui's Profile: http://www.excelforum.com/member.php...o&userid=25252
View this thread: http://www.excelforum.com/showthread...hreadid=385185


All times are GMT +1. The time now is 02:09 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com