How do I find the number of used columns in an Excel sheet

[dkl]

How do I find the number of used columns in an Excel sheet. I'm using LB 4.5.1 not LB 5 or SQLite

I can open a spreadsheet in LB but I want to be able to read the spreadsheet and determine the no. of columns used in the spreadsheet (meaning none blank columns)

I want to open a spreadsheet - read the no.of used columns - open a GUI with X no. of textboxes to display the info.

The coding for the GUI is done and works fine. I just want to be able to randomly open a spreadsheet without having to check the amount of column

If I can determine the width of the columns that would be also great too!

Any ideas would be appreciated

Thanks

[Rod]

When you say you can "open the spreadsheet" do you mean you have it open and can read data or do you just mean you have opened the file for input?

Up to now I have not seen any code to read excel sheets directly. The file is a .zip style file and needs unpacked and then interpreted/parsed.

The usual practice (and not just for Liberty BASIC) is to use excel to export a .csv file and then interact with that directly.


If you get to the csv stage this is code I use to get the number of columns.

'open csv file and get number of columns
   'we are not saving anything here just counting the columns
   open f$ for input as #csv
   line input #csv,l$
   l$=","+l$+","
   col=1 'the column we are at
   pos=1 'the position in l$
   dat$="?"
   while col<=100 and dat$<>""
       'if we are sitting on a [,"] then look for matching [",]
       'else look for [,]
       if mid$(l$,pos,2)=","+chr$(34) then
           pos=pos+2
           pos2=instr(l$,chr$(34)+",",pos)
           dat$=mid$(l$,pos,pos2-pos)
           pos2=pos2+1
       else
           pos=pos+1
           pos2=instr(l$,",",pos)
           dat$=mid$(l$,pos,pos2-pos)
       end if

       'strip down remaining double quotes to single quotes
       a$=chr$(34)+chr$(34)
       b$=chr$(34)
       dat$=replstr$( dat$, a$, b$ )
       pos=pos2
       col=col+1
   wend
   'set the global numCol
   numCol=col-2 'last inc plus "" so -2
   close #csv

[dkl]

When you say you can "open the spreadsheet" do you mean you have it open and can read data or do you just mean you have opened the file for input?

I have it open and can read data. I have no problem with 'input' of the data into LB and displaying it. I see what you have done by searching for a "," and "". but need to study the programme a bit more. I added a 'print' statement so I can see the results.

I've decided to to take a different approach, which is to read in each individual top column and then I can save the info and use that as the Textbox name and I don't have to insert them all separately.

Thank you for kindly providing your own personal programme to assist and for your time. I'll probably be back later!