Jump to content
Macro Express Forums

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


Recommended Posts

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
Link to comment
Share on other sites

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.   

 

Link to comment
Share on other sites

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"/>

 

Link to comment
Share on other sites

  • 2 months later...

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.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...