Excel Data Extraction

[cyberchief]

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?

[randallc]

Hi,

I have a 14-liner that will do this; but no point unless you can tell me the "Wizard" is working on your computer (ie see link in signature; ) let me know;

best, randall

[randallc]

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

[cyberchief]

Sorry Randall.... been pretty busy... haven't had a chance to respond. I do not have the wizard installed yet... but I will look into doing this.

[cyberchief]

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.

[randallc]

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

[randallc]

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

[cyberchief]

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.

[randallc]

Hi,

Tahnks for looking at it; I have found some errors and corrected them elsewhere; I'll try to re-do it for MEx and let you know.

Best, randall

PS - I get the same error now; it was working when I poosted it! -Oh well, I'll let you know

[randallc]

Hi,

It ran the second time on a new machine, but not the first; can you please try it again sometime?..

i imagine I need to declare the registry items once first in future...

Here it is (unchanged), but so it runs "visible"; easy enough to change those lines back to "InVisible"..

Best, randall

XLRowsToSheet2a.mxe

[Cory]

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

[randallc]

Good point; I was only thinking of an opportunity for someone to check my macro, I guess!

Can you link even if you don't know the names of the workbook files?; maybe he does...

Randall

[Cory]

Wow, what went wrong with my post? Weird. No, you need to know the name of the files.