Jump to content
Macro Express Forums

Excel Data Extraction


cyberchief
 Share

Recommended Posts

wondering if ME can do this... I need ME to extract the second row of multiple spreadsheets (96) and put this into a new Excel file. can excel do this without opening every single excel file? Or does it need to access every one?

 

Problem I am having is that I am pulling up information in a system that is not very freindly... It has no feilds to copy from. The only thing I can do is save the data to excel. I have 96 accounts that it needs to pull... other problem is that it pulls aged information and I only need the most current (row 2). Any ideas?

Link to comment
Share on other sites

Hi,

My Excel function will do that in the latest version, but I have not heard if the last one is running on your machine yet so have not posted.

Best, Randall

You need new ExcelComWIZ;

And Only the "exe" in MEX directory from the zip;

{_ExcelComWIZ}.mxe

MEbasicWizard.zip

// ***  Requires {_ExcelCOMWIZ} as macro imported; and Widard.exe" file in mE directory

Variable Set %T2% to ASCII Char of 9

Variable Modify String: Save %T2% to Environment Variable

Set Variable %T4% to "Installation Path"

Variable Set String %T31% "%T4%\Book2.xls"

Variable Modify String: Save %T31% to Environment Variable

Variable Modify String: Convert %T2% to integer %N2%

Variable Set Integer %N1% to 1

// Retrieve row 2 lines one  file [T30] at a time from Sheet1 [?] in Folder; append to Excel sheet

Repeat with Folder

  // Exclude any file which is already openbed (~temp") or has no sensible row in that sheet

  If Variable %T30% contains ".xls"

    AND

  If Variable %T30% does not contain "Book2"

    AND

  If Variable %T30% does not contain "test"

    AND

  If Variable %T30% does not contain "~"

    Variable Modify String: Append %T30% to Text File

    // Copy From in loop

    Variable Set String %T1% "ExcelCopy$["%T30%","2","C","1","NoSave","A2:IV2","InVisible","NoExit","0","0"]"

    Macro Run: {_ExcelComWIZ}

    // Copy To in loop

    Variable Set String %T1% "ExcelCopyTo$["%AnswerFile%","1","A","%N1%","NoSave","%String_T10%","InVisible","NoExit","0","0"]"

    Macro Run: {_ExcelComWIZ}

    Variable Modify String: Append ".../...%T30%" to %T40%

    Variable Modify Integer: Inc (%N1%)

  End If

Repeat End

Variable Modify Integer: Inc (%N1%)

Variable Set String %T1% "ExcelReadCell$["%AnswerFile%","1","A","%N1%","Save","Hi","Visible","NoExit","0","0"]"

Macro Run: {_ExcelComWIZ}

// Copy To AnswerFile

Text Box Display: About to Exit

Link to comment
Share on other sites

Randall... you are a paradigm... or a pair of dimes... not sure. but you are amazing nonetheless. I am lost as what to do next or where to even start with this. I got the files... Here is what i need to do. I have 1 macro that pulls up each account and dumps the data into an excel spreadsheet in a specific folder. Each spreadsheet has the account number as the title. I need to extract line 2 from every one. Now, I am thinking I should use the spreadsheet that contains the account numbers in the column, copy the account number and set to variable... and use that variable in the macro to process the spreadsheet. Can you give me some pointers here??? I am looking at your code and having a hard time deciphering.

Link to comment
Share on other sites

Hi, I'd be interested to hear;

1. I guess I need to know if you have the Wizard working; it may only work in 2003 as it does not in Office97 for this task; aand may not even in XP?

[check the ExcelWiz [and attached macro] macro is imported to your libraray with the correct name (see macrorun command), and Wizard. exe is in your MEX directory]

2. The macro I have sets T4 to the folder to process; change to the folder you wish to process

3. ; it will process all Excel files in that folder unless they fall out of the 4 "If" criteria; change the criteria as you find necessary.

4. Run the macro; outputs to "AnswerFile.xls" in your set directory unless you change it.

5. The extra line for name would go like so; change the row length to be imported else it won't fit if it starts at column B; thus [A2:IU2, instead of A2:IV2] [remember the second parameter is the sheet number or name too; I have them set to "1" at present]

    // Copy From in loop  [returns a file name/ sheet number/ range to "%String_T10%"]

    Variable Set String %T1% "ExcelCopy$["%T30%","1","C","1","NoSave","A2:IU2","InVisible","NoExit","0","0"]"

    Macro Run: {_ExcelComWIZ}

    Variable Set String %T99% "%String_T10%"

    // Copy To in loop

    Variable Set String %T1% "ExcelCopyTo$["%AnswerFile%","1","B","%N1%","NoSave","%String_T10%","InVisible","NoExit","0","0"]"

    Macro Run: {_ExcelComWIZ}

    // Add FileName to column"A" on that row in AnswerFile;  in loop ; procees this string to strip out the name first (I have not done that yet;  from %T99%)

    Variable Set String %T1% "ExcelIntoCell$["%AnswerFile%","1","A","%N1%","NoSave","%T99%","InVisible","NoExit","0","0"]"

    Macro Run: {_ExcelComWIZ}

Let me know specifically what you cannot interpret;

N1 for the file number in the folder you are processinng [to row number in answer sheet]

XLRowsToSheet2.mxe

Link to comment
Share on other sites

should use the spreadsheet that contains the account numbers in the column, copy the account number and set to variable... and use that variable in the macro to process the spreadsheet
I am not sure what you mean here; if you do not want to use my folder process, the spreadsheeewt name goes into T30 for each run of the loop;

Best, randall

Link to comment
Share on other sites

  • 4 weeks later...

Randall,

 

Finally got time to play with this... but I seem to be getting an error...

 

AutoIT Error

 

Line 0 (File "C:\Program Files\MacroExpress3\MEbasicWizard.exe"):

$CopySheet=$ExcelValuePass[2]

$CopySheet=^ERROR

 

Error: Array variable has incorrect number of subscripts or subscript dimension range exceeded.

 

 

Any ideas?

 

Also, do I need to have a blank xls set in the same directory before starting this? I thought I did... so I have and I set that in the macro.

Link to comment
Share on other sites

Fogive me for jumpiForgive me for jumping in here as I have only skimmed Randall's posts but Cybercheif asked if there was a way to access those cells in Excel without having Excel open them all. There is. Just create a new workbook and link to all those cells.ng in here as I have only skimmed Randall's posts very

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...
 Share

×
×
  • Create New...