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.