parsing data from xlsx file?

[Ouka]

Hi all,

Stupid, basic question I am sure, but I am having trouble having a VBScript file open & access data from an xlsx file. My method works fine for .txt and .csv files, but not sure on the syntax needed for .xlsx files.



set FSO_source = createobject("Excel.Application")
set source_file = FSO_source.workbooks.open("C:\Data\file.xlsx", 1)

source_content = source_file.ReadAll 'trouble is here
parse_content = split(source_content, " ")
count = parse_content(2)

msgbox count

the excel object doesn't seem to like .ReadAll, tho it seems to work fine for .csv and .txt files

Essentially what I am trying to do is get 3rd word of the source document, located in A1 if opened in excel.

Thanks in advance!
-o

 

[SkipVought]

your .xls* files are NOT text files.

you must either use creatobject to create an excel application object and open the excel file as an excle file, or have excel saveas a .csv in a prior processs and then you can access this text file.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Ouka]

Isn't that what I am doing in my first two lines? The file is opening fine, I just apparently don't know the syntax for reading the data once it is open.

saving as csv really isn't an option, since downstream scientific machine processes require the file in xlsx format.

 

[SkipVought]

you cannot use a text function to read an excel file.

You have OBJECTS and other stuff in an excel file

You must reference a worksheet and in the worksheet a range.

what is it that you want to access in this workbook?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Ouka]

ah. i wasn't aware i could use the standard excel object hierarchy directly from a vbs script. handy that.

replacing

source_content = source_file.ReadAll function

with

source_content = source_file.activesheet.cells(1,1).value

seemed to work fine.

I guess my worry is that if something changes in the upstream processes that somehow moves the data I'm trying to get out of A1, then the vbs I'm writing will cease to function.

Is there an excel function equivalent to the .Readall txt function, or would I have to write some sort of looping structure to look at each cell individually to look for cells that contain data LIKE the data I'm looking for, then parse those strings for the particular data I need?

 

[SkipVought]

dim a as variant, r as long, c as integer

a = source_file.worksheets(1).usedrange

for r = 0 to ubound(a, 1)
  for c = 0 to ubound(a, 2)
     msgbox a(r,c)
  next
next

usedrange is the range containing DATA.

DATA can be values and/or OTHER RANGE PROPERTIES. So a could contain null elements

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Ouka]

Ah, thank you very much. Much cleaner than how I was trying to manage it.