How to read an Excel file from VBScript?

Question

I am trying to read an Excel file in VBScript, but in the file.Readline I am getting strange characters. Do you have any idea how you could get the value of the cells correctly? Without Excel libraries.

Dim fso,file
Set fso  = Server.CreateObject("Scripting.FileSystemObject")
Set dict = CreateObject("Scripting.Dictionary")        
Set file = fso.OpenTextFile ("C:\myFile.xlsx",1)
row = 0
Do Until file.AtEndOfStream
    line = file.Readline
    dict.Add row, line
    row = row + 1
Loop
file.Close

`FileSystemObject` methods are for processing text files. Excel workbooks are not text files. — Ansgar Wiechers, Dec 28 '17 at 20:55
Take a look at [this](https://gist.github.com/simply-coded/758e2557ccd1a55b46765d8bb1099ec6). Unless you are trying to do this on a computer which does not have excel installed, in which case it will probably be very difficult. — garbb, Dec 28 '17 at 21:02
See ADO which will treat excel files as a database. https://support.microsoft.com/en-us/help/278973/excelado-demonstrates-how-to-use-ado-to-read-and-write-data-in-excel-w and https://support.microsoft.com/en-us/help/257819/how-to-use-ado-with-excel-data-from-visual-basic-or-vba — ACatInLove, Dec 28 '17 at 21:52

score 0 · Answer 1 · answered Dec 28 '17 at 21:11

0

if you are writing a macro in excel (Also visual basic script) there are more than one way getting a cell value. There are range function (example from web:) Worksheets("Sheet1").Range("A5").Value There is cells function (example from web) Cells(1, 1).

The excel file (xslx) should be actually zip file where data is xml. I think thats why you can't read it if you are using VB compiler.

answered Dec 28 '17 at 21:11

Siim Kasepõld

31
3

Excel macros are written in VBA, not VBScript. The languages, despite being similar, have some notable differences. – Ansgar Wiechers Dec 28 '17 at 21:38
VBA has changed quite a bit ever since I studied it used to be quite similar – Siim Kasepõld Dec 28 '17 at 23:02
The excel object model, is the same vbscript/vba or any other COM program. Although the way to connect to it may be different. – ACatInLove Dec 28 '17 at 23:05
The languages are very similar. But like I said, there are [notable differences](http://sdb.planetcobalt.net/vba2vbs.shtml), and have always been. – Ansgar Wiechers Dec 28 '17 at 23:49

score 0 · Answer 2 · answered Dec 29 '17 at 17:12

You most likely need to have set the encoding for the page to UTF-8. See the links before for a simple description:

Classic ASP text substitution and UTF-8 encoding https://www.w3schools.com/asp/prop_charset.asp

So it would look something like below, located near the top of the page:

Response.Charset = "UTF-8"

score 0 · Accepted Answer · answered Jan 02 '18 at 15:01

0

I have solved my problem using the extension .CSV, since this allows me to read the information as a .txt in which each column is separated with commas by default, so my code works normally.

answered Jan 02 '18 at 15:01

jaxonjma

97
1
10

How to read an Excel file from VBScript?

3 Answers3