Question regarding interacting with Excel files

[LM]

I am wondering whether anyone has looked into interacting with Excel files (reading and/or writing), preferably in the .xlsx format but .ods (OpenDocument Spreadsheet) format would be useful too.  I use CSV files a lot (readable and writable by Excel) but could really use a way to read and write directly to XLSX.

Has anyone tried this or does anyone have any suggestions or pointers to how it can be done?  I would be happy if I could get as far as just reading and writing data to cells. Accessing formatting and graphs and such is not as important.

Thanks in advance for any input.

LM

[Cobalt]

Not to sure about more modern formats, So I don't know how helpful this will be.
But I once played with the old Excel format from Excel97. Was able to read and write to cells, but I never tried any kind of formatting.
Not sure if it still exists or has modern formats but I used a site called Wotsit's format. not sure if it is even still around.

You'll want to search around and try to find information about the formatting of the xlsx files. Once you know just how the data is stored its not too hard to GET and PUT to play around with it.

[david_uwi]

Not sure about reading, but simple writing can be easy. My employer used* to insist on data being in excel files. So the easy way to do this is to get QB64 to open a text file and write the data separating them with a tab (chr$(9)) and LF, CR (CHR$(10)(13)) at the end of each line you can then paste it into an excel file.

*I am now happily retired

[SMcNeill]

I don't use Excel much -- all my work is usually in Word instead -- but the xmlx and docx formats aren't much different.   

A few things to be aware of:

1) If your file extension has 4 letters, and they end in X, it's not a file.  It's a ZIP directory!
2) If you change the extension of that file to .ZIP, you can open it up and see the other individual files contained within.
3) Most of the contained files are in simple TEXT format, regardless of their extension.

So with those points in mind, to work with the files in QB64, you'd need to:

1) Rename the extension and unzip the directory.
2) Find the proper file you want to access in that uncompressed directory.  GET/PUT from it.
3) Save the altered file back to that directory.
4) Zip that directory back to compress it all together,.
5) And finally, rename it back to the proper XLSX extension.

[LM]

Thanks all for the input!  It has given me lots to think about and explore.

LM

[NOVARSEG]

Hi ya NcNeil
I have been away for awhile a much needed break from programming in general.

Excel went downhill after the .XLS format was obsoleted.
  Excel was my go to for number crunching because I could do graphs easily with maths

[SMcNeill]

Hi ya NcNeil
I have been away for awhile a much needed break from programming in general.

Excel went downhill after the .XLS format was obsoleted.
  Excel was my go to for number crunching because I could do graphs easily with maths

As far as I know, XLS never got obsoleted.  It just got upgraded to XLSX, which is a specially named ZIP folder which holds the XLM files inside it along with all your resource files like embedded fonts, sounds,  and graphics. 

When did XLS get obsoleted?  Did I miss something?  (As I said, I don't hardly ever use Excel or Power Point -- almost all my work is always in Word.)

[NOVARSEG]

Well the Excel as found in Office 2003 was XLS and you can't get Office 2003 anymore even as a download so in that regard MS doe snot support even though it should run on 32 bit machines.

Back Story. Been away and looking to start a tech company I will need nerdy programmers.
Also Im breaking with the traditional company as we all know and love. No more employees and no more employers. We all set at the round table of tech and if you want to be boss you can.

[SMcNeill]

Well the Excel as found in Office 2003 was XLS and you can't get Office 2003 anymore even as a download...

https://archive.org/details/MicrosoftOffice2003StandardEdition11.0.5510.02003English

It's the age of the internet.  Nothing ever really disappears anymore.

[madscijr]

I am wondering whether anyone has looked into interacting with Excel files (reading and/or writing), preferably in the .xlsx format but .ods (OpenDocument Spreadsheet) format would be useful too.  I use CSV files a lot (readable and writable by Excel) but could really use a way to read and write directly to XLSX.

Has anyone tried this or does anyone have any suggestions or pointers to how it can be done?  I would be happy if I could get as far as just reading and writing data to cells. Accessing formatting and graphs and such is not as important.

Thanks in advance for any input.

LM

I haven't done it in QB64, but if you're in Windows and have Excel installed, it's easily done from vbscript by instantiating an Excel application object like this

Code: [Select]

' Write to Excel file from vbscript
Dim xlApplication ' As Excel.Application
Dim xlWorkbook ' As Excel.Workbook
Dim sFile ' As String

sFile = "C:\Temp\MyWorkbook.xlsx"
Set xlApplication = CreateObject("Excel.Application")
xlApplication.Visible = False

On Error Resume Next
Set xlWorkbook = xlApplication.Workbooks.Open(sFile)
If Err.Number <> 0 Then
    MsgBox "Error #" & CStr(Err.Number) & ": " & Err.Description
    Err.Clear
Else
    'manipulate file, like:
    xlApplication.Cells(1,2).Value = 255 ' Set row 1, column 2 to 255
    xlWorkbook.Save
    'etc.
End If
On Error Goto 0

xlApplication.Quit
xlWorkbook.Close False

Set xlWorkbook = Nothing
Set xlApplication = Nothing

I'm not sure how to do COM calls from QB64 (@SpriggsySpriggs is the API guru and might know) but alternately you could write a vbscript that reads & writes from/to Excel, which your QB64 can shell out to and pass command line params to and/or transfer parameters & data via a text or other file, and let the vbscript do the talking to Excel in its own native format.

Good luck!

[madscijr]

When did XLS get obsoleted?  Did I miss something?  (As I said, I don't hardly ever use Excel or Power Point -- almost all my work is always in Word.)

Microsoft introduced XLSX with Office 2007, the main practical benefit of it being that it supports bigger workbooks (1 million rows to a sheet, 16384 columns, no more 255 character limit for a cell, etc.) and also I think it's more secure (the file can be loaded without autorunning the macros).

The xlsx and docx, etc. file format is also different in that they are really ZIP files that contain XML files with everything - try renaming your XLSX or DOCX adding file extension .ZIP at the end and double click the file. You will see a bunch of XML files that can be opened in your favorite editor. The only thing not included in that are the VBA macros and userforms which are still in some binary format. These can be exported from inside the VBE (the VBA macro editor) to .BAS, .CLS, .FRM, etc. But I digress.

Anyway, Microsoft released a free upgrade or add-on for the old Office 2003 that enables it to read/write the new Office file formats (not sure how well Excel 2003 plays with the larger sheet sizes, I think it just truncates anything past the old 64738 row / 255  column limit, etc.) - but it at least can access the newer file formats.

Don't ask me where to find that update anymore - MS is notorious for taking down drivers, installers and updates after a while, though they can usually be found with much searching, or on sketchy 3rd party sites, or archive.org.

Cheers

[euklides]

I work fluently with Excel and his visual basic excel programming tool.
It is very easy to export data from XL to QB. It all depends on what you want to to transfer.

The simplest way is to write a macro in VBA Excel which generates a text type file with the wanted datas.
The reverse is of course possible, via text files.

For instance
Sub ExportData()
'Exporting "C5:D20" from sheets "page2"
'output file="D:\temp\myexport.txt"
Sheets("page2").Select
Close 1: Open "D:\temp\myexport.txt" For Output As 1
For Linez = 5 To 20
For Columz = 3 To 4
Adr$ = Cells(Linez, Columz).AddressLocal()
Valu$ = Range(Adr$).Text
Print #1, Adr$ + ":" + Valu$
Next Columz
Next Linez
Close 1
End Sub


The text file look like this:
$C$5:
$D$5:
$C$6:
$D$6:
$C$7:important information
$D$7:10,5
$C$8:
$D$8:


Reverse now...

Sub ImportData()
'Import from text file  "D:\temp\myexport.txt"

Sheets("page2").Select
Close 1: Open "D:\temp\myexport.txt" For Input As 1
While Not EOF(1): Line Input #1, A$
j = InStr(A$, ":"): Adr$ = Left$(A$, j - 1): Valu$ = Right$(A$, Len(A$) - j)
Range(Adr$).Value = Valu$
Wend: Close 1
End Sub



The main thing is to know what really you want to transfer from one system to another.

[euklides]

From QB64 to XL, an other way could be using  _clipboard$ (for one value at time )


For instance: put in sheet "page3", Cell B35 , 500.01

QB 64 programm:
ZZZ$="(page3)B35:500,01":_clipboard$=ZZZ$


In excel in the arrival XLS file: this little programm (example):

Sub onedatafromqb64
Dim objData As New MSForms.DataObject
objData.GetFromClipboard
INFO$= objData.GetText()
if INFO$="" then goto fino
J=instr(INFO$+"x",")"):if J=0 then goto fino
PAGE$=left$(INFO$,J):INFO$=right$(info$,len(info$)-J)
PAGE$=right$(page$,len(page$)-1):page$=left$(page$,len(page$)-1)
j = InStr(info$, ":"): Adr$ = Left$(info$, j - 1): Valu$ = Right$(info$, Len(info$) - j)
Sheets(page$).Select:Range(Adr$).Value = Valu$
fino:
end sub

Excel file and QB64 program must be both opened at the same time, here.

[madscijr]

From QB64 to XL, an other way could be using  _clipboard$ (for one value at time )

Ooooh, that's an interesting idea as an alternate way to get two EXEs on the same computer to talk to each other (other than using files).
What are some other methods we could use to get Excel to talk to QB64 - how about a network connection on the local machine??

[SMcNeill]

Here's a working example for you:

Code: QB64: [Select]

1. _Title "XLSX Example data retrieval"
2. If _FileExists("Example.xlsx") = 0 Then
3.     Print "File not found in base directory.  We can't work with what doesn't exist!"
4.     End
5. End If
6.  
7. 'Make a copy of the original so we don't screw anything up.
8. Open "Example.xlsx" For Binary As #1
9. l = LOF(1)
10. s$ = Space$(l)
11. Get #1, 1, s$
12. Close
13.  
14. Open "Temp.zip" For Binary As #1 'copy the data and rename the xlsx extension to a zip file.
15. Put #1, 1, s$
16. Close
17.  
18. Shell _Hide "PowerShell Expand-Archive Temp.Zip" 'Expand the zip file
19.  
20. Open "./temp/xl/sharedStrings.xml" For Input As #1 'open it up
21. Do Until EOF(1)
22.     Line Input #1, temp$ 'get the data
23.     Print temp$ 'print the data
24. Loop
25.  
26. Close
27.  
28. Kill "Temp.Zip" 'clean up the temp file from the drive
29.  
30. Print
31. Print "Remember to clean up your Temp directory and remove it when finished!"
32.  
33.  

This opens the xlsx file, copies it, renames it to become a zip file, extracts the contents of the zip file to a Temp directory, then reads the main data file and prints it to the screen, before finishing up.

Note that I'm not extracting any of the data from the xls file itself, so you'd need to parse your results, which are in the following style format:

Code: [Select]

<si>
<t>Name,First</t>
</si>
<si>
<t>Name,Last</t>
</si>
<si>
<t>Age</t>
</si>
<si>
<t>Sex</t>
</si>
<si>
<t>Junk</t>
</si>
*Which all of this is on my very limited usage of Excel -- which I proudly have proclaimed repeatedly that I don't use personally.  Your actual data might be in a different file inside the xlsx folder, but I can't imagine that extracting the information inside it would be much different than what I have here.  You just need to know the actual internal file that you're looking for, to access the right one inside the folder, and then you're good to go!