Code for defining columns in an excel sheet on open

[Matthew Hirsch]

I have an excel file that I am trying to write a MB script so I can open it and not have to define the three columns each time.     This script is pasted into the MB code window and not run in an MBX.

 

The file is called zips.xlsx.  

Column 1 is Zipcode which needs to be set to character 5.

Column 2 is Date which needs to be set to date, not date/time

Column 3 is Sales which needs to be set to float.

 

I can get the file to open in MapInfo, but it always prompts for column types.

 

I’ve looked around and cannot find anything clean on how to do this.

Thank you.

Matthew

 

[Marijan]

Matthew, 

I don' think you can do this programmatically. As MapInfo Reference states, only parameters you can use are charset, titles and range. One thing you can do is to create MapBasic (or any other supported language code) program  to generate TAB file for your XLS with desired field types.

Let's say you create template TAB file:

!table
!version 1000
!charset WindowsLatin2

Definition Table
  File "MyExcelFile.xlsx"
  Type XLS Titles Range "Sheet1!A2:C2"
  Fields 3
    ZIP_code Char(5);
    Date Date ;
    Sales Float ;
begin_metadata
"\IsReadOnly" = "FALSE"
"\MapInfo" = ""
"\MapInfo\TableID" = "5e3900ea-baf2-4677-95a4-51d4746c6183"
end_metadata

As you can see, you could probably create program that would do search and replace of word "MyExcelFile.xlsx" with your xls file name, and place newly created TAB at same location as your xls file. One thing you should take care of is  MapInfo\TableID parameter, it is unique for every table. Try removing it completely - it should work fine. Off course, sheet name and range should be same in every xls. It would be best to take one TAB that you have already created and use it to create template.