How to open an Excel file and store the cells value in tabl variable

[hufi]

Hello

 

I wonder if it is possible or not ot open an excel file and store the values in an array to use in my macro epxress script.

Then I will use the values stored in my algorithm

 

Example

 

For each line

   connect to application and select project

   simulate the nb of tab 

   click on the report 

   delay of (time in second to generate)

   saved report with the Saved report name

end for

 

 

Project	Nb Tabs	Report	Saved report Name	Time in second to generate
Projet 1	16	Report Name 1	Save Name 1	600
Project 2	17	Report Name 2	Save Name 2	300

[Cory]

Yes. I wrote this article. Does that help?

[rberq]

1. Open spreadsheet (manually or by macro commands)

2. Save spreadsheet (manually or by macro commands) as a CSV or other file format that can be processed by the ASCII FILE BEGIN PROCESS through ASCII FILE END PROCESS macro commands

3. As the ASCII file process returns individual lines to the macro, do the processing you outlined for each line

 

This is accomplishing the same thing as Cory's elegant technique.  His method brings the entire spreadsheet into a variable -- all the lines at once into a single variable -- then uses macro commands to split out individual lines and parse the several columns of each line.  My suggested method relies on Excel / Macro Express to parse / store / retrieve the columns, one line at a time, into an array.  Cory's method is more fun.  Mine is more plodding and perhaps easier to visualize for your specific project.   

 

[hufi]

thanks both of you, I will keep you informed

[Cartwheels]

I often use Go To in Excel to get to a desired range or cell, use the F5 key. Macro Express code below for that. Save it as a Snippet, it comes in handy! Then I usually do what rberq describes.

<COMMENT Value="Excel F5 Go To"/>
<TEXT TYPE Action="0" Text="<F5>"/>
<WAIT FOR WINDOW TITLE Title="Go To" Partial="TRUE" Wildcards="FALSE" Indefinite="TRUE" Hours="0" Minutes="0" Seconds="0"/>
<TEXT TYPE Action="0" Text="Sheet1!A1" _COMMENT="Change this to your desired range or cell"/>
<TEXT TYPE Action="0" Text="<ENTER>"/>
<DELAY Flags="\x01" Time=".4"/>

 

[Cory]

That's a great suggestion Cartwheels. I do the same. I think many people don't realize one can specify a range with it. 

[Hamidi Zare]

first you have to get the number of records. you may use a macro within excel, define it globally to insert a line above all records in each file, get the number of the records below it using:

(in excel) in cell B1 write "=counta(a:a)"

 

you have to put it elswhere, not in A column!

then, you may use that number generated in B1 to tell you macro express that how many records are there, or the length of the array!

in that case, you have to repeat the comand to read your a1 record till a(n) for the entire table to last column(n)

 

if you did not get what I am saying, you can pass same excel file you desire to do, I will provide you the ME file which it works.