0

I developed an Access database solution that is using Excel automation to open xls and xlsx files so I can import specific cells that I need.

Now I had to deploy my software to an user that does not have Office nor Excel installed and is using Runtime do run my program and I can not use automation any more.

Is there any way I can open an Excel file without Excel and import lets say cell B7 and cell E4 ? I dont need to import it in the table directly but to operate with results from xls in the memory (as I did with Excel object) and save it later.

Thanks in advance.

exe.bat
  • 340
  • 1
  • 2
  • 17

2 Answers2

1

With some (quite severe) limitations, it is possible to use Jet (i.e., the Access database engine, an ageing version of which is a standard Windows component) to read XLS files at least. For the limitations see here:

http://support.microsoft.com/kb/257819/en-gb

As for an example...

Function ReadCell(XLSFileName As String, SheetName As String, CellName As String)
  Dim DB As DAO.Database, RS As DAO.Recordset
  Set DB = DBEngine.OpenDatabase(XLSFileName, False, True, "Excel 8.0;HDR=No;")
  Set RS = DB.OpenRecordset("SELECT * FROM [" + SheetName + "$" + CellName + ":" + CellName "]")
  ReadCell = RS(0)
  RS.Close
  DB.Close
End Function

Sub Foo
   MsgBox ReadCell("C:\Users\ExeBat\Documents\Test.xls", "Summary Details", "C5")
End Sub
Chris Rolliston
  • 4,788
  • 1
  • 16
  • 20
0

My guess is not without a 3rd party library of some sort. Potentially you could read the file as text if it was stored as office open XML, my guess is that MS encrypts/obfuscates your standard xls/xlsx file by default so you cannot though. If Excel isn't available on your user machines in all cases you might need to look into having the source data in another format (text, csv, etc), I know that is probably not an ideal answer though.

Shawn
  • 869
  • 1
  • 9
  • 27
  • I am also exporting the same file (that I need to import) from my software so I could also export it as csv or something like that. – exe.bat Feb 27 '14 at 20:07
  • 1
    re: "my guess is that MS encrypts/obfuscates your standard xls/xlsx file by default" - No, .xlsx files are just compressed using standard ZIP compression. Save an Excel sheet as .xlsx, rename it to .zip and then extract the contents and you will see all of the OpenXML goodness. (Even so, parsing those XML files yourself would be... challenging... to say the least!) – Gord Thompson Feb 27 '14 at 20:10
  • But how can I manipulate the CSV from code as I did with Excel ? It was very practical because I could use for example fill cells A1-DocumentID, A2-Date, A3-INVNumber, B2-CreatedBy... And Range A10-F10 filled with invoice items. I imported the file back the same way and restored everything from xls back to the right tables. I use the file to transfer invoices from one location to another. – exe.bat Feb 27 '14 at 20:13
  • You would write vba code to read in a text file and do the splitting yourself at the comma or whatever delimiter you choose. I just did a quick google search (so I am sure there is something better) but here is a sample of vba code to read text files: http://stackoverflow.com/questions/10744113/access-and-vba-read-text-file-with-more-than-255-characters-per-line Thanks for the correction Gord, I didn't know that about the zip that will be useful to me in the future probably. – Shawn Feb 28 '14 at 00:27