Excel FieldInfo parameter: what works?

General
1

Folks: am converting Excel OpenText from VBA. Here is the Xojo definition of OpenText in MSOfficeAutomation.rbx:

Sub OpenText(Filename_Param as string, Origin_Param as variant, StartRow_Param as variant, DataType_Param as variant, TextQualifier_Param as integer, ConsecutiveDelimiter_Param as variant, Tab_Param as variant, Semicolon_Param as variant, Comma_Param as variant, Space_Param as variant, Other_Param as variant, OtherChar_Param as variant, **FieldInfo_Param as variant**, TextVisualLayout_Param as variant, DecimalSeparator_Param as variant, ThousandsSeparator_Param as variant)

After consulting the VBA documentation and Eugene Dakin’s 2019 answer, this is straightforward up to FieldInfo. I don’t need it, but I do need DecimalSeparator which comes later. Here is my Xojo code:

xlapp.Workbooks.OpenText (file, xlWindows, 1, xlDelimited, xlTextQualifierDoublequote, False, True, False, False, False, False, chr(9), **???** , True, myexceldecimal, myexcelthousands)

What works in the FieldInfo parameter position as a filler ?

I have tried an Array of Arrays, an Array, False, Nil, but no luck: OLE Exception or compiler error every time.

2

there is a Description for FieldInfo

3

Hi MarkusR:

Thanks, yes, but that description does not explain how to write or bypass FieldInfo in Xojo. Any ideas?

4

i don’t know, not using ms office apps.
i would expect something like
Var a(5,1) As Integer
for 6 columns
with column number and XlColumnDataType value
and just use a as argument

5

Here is my latest effort at the FieldInfo parameter. Any suggestions for improvements, anyone?

Dim Arrayofarrays() As Variant
Redim Arrayofarrays(0)
Dim arraycol1(1) As Integer  ' or As Variant, same result
arraycol1(0) = 1  ' column 1
arraycol1(1) = 1  ' xlGeneralFormat
Arrayofarrays(0)  = arraycol1

' this works and outputs an Excel worksheet
xlapp.Workbooks.OpenText (file, xlWindows, 1,  xlDelimited,  xlTextQualifierDoublequote,  False, True, False, False, False, False, Chr(9))

' this fails with an OLE Exception
xlapp.Workbooks.OpenText (file, xlWindows, 1,  xlDelimited,  xlTextQualifierDoublequote,  False, True, False, False, False, False, Chr(9), Arrayofarrays)
6

Hello Mike,

I slightly modified the available code, and this code seemed to work:

Sub Pressed() Handles Pressed
  Var excel as new ExcelApplication
  excel.Visible = true
  
  Var Arrayofarrays() As Variant
  Redim Arrayofarrays(0)
  Var arraycol1(1) As Integer  ' or As Variant, same result
  arraycol1(0) = 1  ' column 1
  arraycol1(1) = Office.xlGeneralFormat //=1
  Arrayofarrays(0)  = arraycol1
  Var FileNam as String = "C:\Users\eugen\OneDrive\Desktop\New\Excel\OpenText\Book1.csv"
  ' This works and outputs an Excel worksheet
  'Excel.Workbooks.OpenText(FileNam, Office.xlWindows, 1,  Office.xlDelimited,  Office.xlTextQualifierDoublequote,  False, True, False, False, False, False, Chr(9))
  
  ' This works well and also outputs an Excel worksheet
  Excel.Workbooks.OpenText (FileNam, Office.xlWindows, 1,  Office.xlDelimited,  Office.xlTextQualifierDoublequote,  False, True, False, False, False, False, Chr(9), Arrayofarrays)
End Sub

Here is the contents of the example Book.csv file:

MyA,My B,MyC
9,1,10
8,2,10
7,3,10
6,4,10
5,5,10
4,6,10
3,7,10

Does this work on your computer?

Tested on:
Version: Xojo 2023 Release 3.1
OS: Windows 11
Date: 20 Nov 2023
Plugin: MSOfficeAutomation.rbx

7

Thanks, Eugene, now 50% success …

Copied your code unchanged except for the path to Book1.csv

The Arrayofarrays code gives the OLE Exception with Xojo 2023R3 and Windows 10 and Office 2019 (version 2309, the current version)

but Arrayofarrays code works with Xojo 2023R1 and Windows 8.1 and Office 2007.

So I built the app on the Windows 8.1 computer, then launched it on the Windows 10 computer. It failed with an “OLE Exception” error box. Excel 2019 also said “Sorry, something went wrong.”

The original VBA “OpenText” code runs on all Windows computers back to XP (and probably earlier) and Office back to 2007 (and probably earlier), so it looks like the culprit is MSOfficeAutomation.rbx

Perhaps a solution is to ReadLine the csv file, split it, then write the values into the Excel worksheet cells in the appropriate format. Slow but sure.

Anyone have better suggestions?

8

Hello @Mike_Linacre1,

Not sure why there is an error with the example program. Here is a link to download both the CSV file and the demonstration code to try.

Xojo and CSV file

Warm regards.

9

Thanks Eugene.

Ran your binary_project. It works with Excel 2007 and fails with Excel 2019 (now version 2310). Have updated Windows 10 and also to Xojo 2023R3.1 but no improvement.

What version of Office are you using?

10

My version of Excel is 2019, version 2310, Build 16.0.1624.20054, and its 64-bit.

I am not sure what to say about the failure on your computer.

11

Thank you, Eugene.

Yes, your built Xojo code works fine on another Windows 10 computer which has Excel 2016 and no Xojo installation.

So must be that computer. Will try reinstalling Office.

Thanks again.

12

I was able to figure out what was causing a crash on a Windows 10 with the above code… The array is not in the same format with Xojo as it is in Excel.

I haven’t figured out how to create a double array that is compatible with Excel from Xojo yet. The good news is that this is the problem.

13

I figured out how to create an array in Xojo that works with Xojo. Here is the code which works on all my computers. Could I ask you to check it on your computers too?

Sub Pressed() Handles Pressed
  Var excel as new ExcelApplication
  excel.Visible = true
  
  Var Arrayofarrays() As Variant
  Arrayofarrays.Add 1 //Column 1
  Arrayofarrays.Add Office.xlGeneralFormat //=1
  
  Var FileNam as String = "C:\Users\eugen\Desktop\New\ExcelFIeldInfoParameter\ExcelParameter\Book1.csv"
  // This works and outputs an Excel worksheet
  //Excel.Workbooks.OpenText(FileNam, Office.xlWindows, 1,  Office.xlDelimited,  Office.xlTextQualifierDoublequote,  False, True, False, False, False, False, Chr(9))
  
  //This also outputs an Excel worksheet
  Excel.Workbooks.OpenText (FileNam, Office.xlWindows, 1,  Office.xlDelimited,  Office.xlTextQualifierDoublequote,  False, True, False, False, False, False, Chr(9), Arrayofarrays)
End Sub

If this works on your computers, then I’ll add it to the Excel book and provide updates to everyone.

Warm regards.

14

Thank you, Eugene. I have also tried re.installing Excel and also moving the arrays to global so that they don’t go out of scope, but those made no difference.

Yes, if we could build a VBA array of arrays in a MemoryBlock, that might solve the problem. You are the person to do it!

15

Thank you, Eugene. Yes, your code works for me as a place-filler. :grinning:
The column and format values appear to be ignored.

16

And this works:

Excel.Workbooks.OpenText (FileNam, Office.xlWindows, 1, Office.xlDelimited, Office.xlTextQualifierDoublequote, False, True, False, False, False, False, Chr(9), Arrayofarrays, 1, ".", ",")

17

Hi Mike,

I have been working on this all day, and have not been able to get it to work in Xojo. My next step was that I programmed an example in Excel VBA to see if it would work, and I couldn’t get it to work either.

Not sure what to say, just trying stuff out.

18

Hi Eugene:

I tried recording an Excel VBA macro for what I thought would be OpenText. The VBA macro editor showed the code below. Perhaps I should switch from OpenText to QueryTables.Add and so avoid the array-of-arrays problem?:

    With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\book1.csv", _
        Destination:=Range("$A$1"))
        .Name = "book1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 2, 2)  ' I chose general, text, text
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
19

OK - this works for me instead of OpenText. Does it work for you, Eugene?

Var Excel as new ExcelApplication
Excel.Visible = true
Excel.Workbooks.add
var mytable as excelquerytables
mytable = Excel.Activesheet.QueryTables.Add("TEXT;C:\book1.csv", excel.Range("$A$1"))
mytable.Name = "book1"
mytable.FieldNames = True
mytable.RowNumbers = False
mytable.FillAdjacentFormulas = False
mytable.PreserveFormatting = True
mytable.RefreshOnFileOpen = False
mytable.RefreshStyle = office.xlInsertDeleteCells
mytable.SavePassword = False
mytable.SaveData = True
mytable.AdjustColumnWidth = True
mytable.RefreshPeriod = 0
mytable.TextFilePromptOnRefresh = False
mytable.TextFilePlatform = Office.xlWindows
mytable.TextFileStartRow = 1
mytable.TextFileParseType = office.xlDelimited
mytable.TextFileTextQualifier = office.xlTextQualifierDoubleQuote
mytable.TextFileConsecutiveDelimiter = False
mytable.TextFileTabDelimiter = True
mytable.TextFileSemicolonDelimiter = False
mytable.TextFileCommaDelimiter = True
mytable.TextFileSpaceDelimiter = False
mytable.TextFileColumnDataTypes = Array(1, 2, 2)  ' 3 columns: general, text, text
mytable.TextFileTrailingMinusNumbers = True
mytable.Refresh ' BackgroundQuery.False ' Refresh must be last
20

Hi Mike,

I ran the code for VBA in Excel and it worked perfectly. However, I ran the Xojo code and it fails with

mytable.Refresh

The error is:
ErrorExcel

To try and prevent this error, I moved it in different locations and still had the same error.

There were a few things that I changed to see if it would work, such as the way that Xojo works with an array…

Var AnArray() as Variant
AnArray.add 1
AnArray.add 2
AnArray.add 2
mytable.TextFileColumnDataTypes = AnArray  ' 3 columns: general, text, text

and the platform because xlWindows is a different value than 437…

mytable.TextFilePlatform = 437

and this command, since the name increments each time I run the program…

mytable.Name = excel.ActiveWorkbook.Name //changed