Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbooks.Open oddity
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbooks.Open oddity
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. -- (please remember to click yes if replies you receive are helpful to you) "minimaster" wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbooks.Open oddity
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbooks.Open oddity
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbooks.Open oddity
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA Excel.WorkBooks.Open can't open an XML format xls file | Excel Programming | |||
When I open Excel, workbooks open automatically. How can I stop t | Excel Discussion (Misc queries) | |||
workbooks.open function fails to open an existing excel file when used in ASP, but works in VB. | Excel Programming | |||
Excel 2003 Workbooks.Open with CorruptLoad=xlRepairFile fails on Excel 5.0/95 file due to Chart, with Error 1004 Method 'Open' of object 'Workbooks' failed | Excel Programming | |||
Workbooks.Open / .Open Text - How do you stop the .xls addition? | Excel Programming |