Workbooks.Open oddity

[minimaster]

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?

 

[SeanC UK]

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.

 

[minimaster]

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.

 

[minimaster]

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

 

[minimaster]

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