Workbooks.Open oddity

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

minimaster - 27 Jan 2008 09:41 GMT

I'm struggeling to understand this.

I need to work with some data columns from a xml file. The columns do
contain numbers with a comma as decimal seperator. Same as in the
control panel settings.

Sub OpenXML()
Workbooks.Open Filename:="PAGE1.xml"
End Sub

When I start this procedure with Macros... run or directly in the VBE
it runs fine, the file is opened and all numbers are recognized as
numbers in Excel.

When I start the macro via a commandbarbutton from one of my add-ins
with commandbars the numbers are not recognized correctly and all
numbers are strings.

I've tried to start the macro via Excel.Application.Run "openXML" but
that didn't help either. (though I didn't believe it would make a
difference, but just in case)

Can anybody explain this?

Reply to this Message

SeanC UK - 27 Jan 2008 13:21 GMT

Hi,

Try using:

Workbooks.Open Filename:="Page1.xml" Local:=True

There is a difference in how VBA tries to open or save workbooks to the way
in which Excel does it. Local = False, as is default, uses the VBA language
and setting Local to True uses control panel settings. This might be the
issue, so try this and see if it works.

Good luck,

Sean.

Signature

(please remember to click yes if replies you receive are helpful to you)

> I'm struggeling to understand this.
>
[quoted text clipped - 19 lines]
>
> Can anybody explain this?

Reply to this Message

minimaster - 27 Jan 2008 13:51 GMT

Thx but that does not help, I've already explored that option. In fact
the same applies to the openxml method and not even multiplying all
"number" cells with 1 via paste special makes Excel recognize the
number. However after the procedure has finished Excel marks all
"number cells" with green triangle error comments. The error comment
being "Number stored as text". I can then convert these "text-numbers"
by hand to real numbers, but within the macro it doesn't work (unless
I start the macro not via the commandbar). It is strange but probably
requires only one little trick or switch that I don't know yet.

Reply to this Message

minimaster - 27 Jan 2008 16:51 GMT

Dim n As Range
Dim c As Range

'
Workbooks.Open Filename:="PAGE1.xml"
'
myOpenXML
prepare_open_xml ' delete all columns that we
don't need & format nicely
saveasXLS

Set n = Range("A3:F" & LastCell(ActiveSheet).Row)

' Atempt with PasteSpecial and multiplication with 1 : doesn't
work
' Range("A1").FormulaR1C1 = "1"
' Range("A1").Copy
' n.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply,
SkipBlanks:=True, Transpose:=False
' Application.CutCopyMode = False
' Range("F3").Select

' Atempt with replace and a new comma : doesn't work
' n.Replace What:=",", Replacement:=",", LookAt:=xlPart

' attempt with type conversion of every single cell : at least this
one works!
For Each c In n
If TypeName(c.Value) = "String" Then
If InStr(1, c.Value, ",") Then
c.Value = CDbl(c.Value)
Else
c.Value = CInt(c.Value)
End If
End If
Next c

Reply to this Message

minimaster - 27 Jan 2008 17:01 GMT

After I discovered that "pastespecial" and "replace" method are not
able to make Excel recognize the numbers as numbers I've implemented
now a work around by converting each single cell separately with a
typeconversion. Not very fast but at least it works.

Sub myOpenXML()

Dim n As Range
Dim c As Range

Workbooks.Open Filename:="PAGE1.xml"

Set n = Range("A3:F" & LastCell(ActiveSheet).Row)

For Each c In n ' type conversion of every single cell : at
least this one works!
If TypeName(c.Value) = "String" Then
If InStr(1, c.Value, ",") Then
c.Value = CDbl(c.Value)
Else
c.Value = CInt(c.Value)
End If
End If
Next c

End Sub

Reply to this Message