Data Consolidation Project (Thread #6)

G

goastros

Registered Users (C)
INSTRUCTIONS TO RUN THE EXCEL MACRO

1> Download the attached Excel file.

2> Open this file and check if there is a Worksheet with the name
of the dates you want to check.
for eg - if you are searching for SRC02-176, look for a
Worksheet with the name SRC02-176.
If you find one, simply rename it to maintain that data in case
needed in the future.
Then, run the macro.

3> Before you run the macro check the security level of your Excel
file.
Security Level tab is found at (Tools menu, Macro
submenu, Security command).
The level cannot be at High, it should be changed to Medium
or Low.
Now you are ready to run the macro.

4> To run the macro you have to go to the menu at the top of the
screen and look for Tools,
then look for the submenu Macro and then Macros.
It will pop up a box titled Macro.
You can see two Macros listed (I485Scan & MakeChart).
Select I485Scan[\B] and hit the Run button at the
right.
Another box will pup up titled Excel.
There, you have to enter the first five digits corresponding to
the SRC range you want
to check.
for eg - 01-001 and press OK.
Another box will pup up and enter the range of the last five
digits to check.
Start from 50000 to 51000 and go all the way to 56000.
Note - at a time, you can only scan for 1000 cases from the
same IP address before
'We are sorry' message. After that you have to change
your IP or wait for 24 hours
before you can scan again.
Important Note - When you try to scan more than
3 invalid SRCs, you get blocked as well.
So be wise in choosing the right range of SRC numbers.

5> The macro will run and will show the results found.
It will create a excel sheet with SRC and write down only the
485 cases.

6> If you want, you can also run the second macro (MakeChart)
to obtain a summary and chart of the results gathered.

7> After your 1st run is complete, for next set of 1000 nos,
rename the excel sheet by clicking on the excel tab. Then
repeat step 4>.
After this scan is over combine the 2 results.
Repeat this until you have exhausted finding the date range
or you feel exhausted..

8> Re-scan - Once you have found the SRC numbers,
re-scan the already existing Excel sheet from time to time.

And don't forget to post your results here or add to the consolidated file.
 
eb2_I485_RD0901 's insctructions for rescan.


ONE easy way to scan using the consolidated file use the following steps:


1. Open Consolidated file
2. Open a new excel doc ( give it the name temp )
3. Right click on the "Summary" sheet and select "Move or Copy .."
4. Now from the "To book" item on top selcet the "temp" file
5. Click OK
6. The summary sheet is now "moved from the Consolidated file to the temp file"
7. Save and close the temp file.
8. NOW it is easy to run the "ModifiedScan" Macro because excel does not need to keep calculating the summary.
7. Run the ModifiedMarco on as many sheets as you can.
8. One this is done , the summary sheet must be copied back.
9. Open the "temp" excel file.
10. Select the "summary" sheet
11. Right click on the "Summary" sheet and select "Move or Copy .."
12. Now from the "To book" item on top selcet the "consolidated" file
13. Click OK
14. Update the "scan date" on the sheets you have scanned.
15. Save , ZIP and post the new file on this thread.




Last edited by eb2_I485_RD0901 on 21st November 2003 at 09:57 AM
 
ver 55.0

Here is version 55

So far 110 approvals for Nov 03.

+ picsarus 01-156
+ IndianinDallas 03-091 & 092
+ Patient2002 02-260
+ Yellowknight 01-226 ( could you please check your data, yellowknight. cos i find the RD is 07/24/01 and there is a pproval for 07/24/01).

Updated certain dates.
 
goastros

how can you copy a sheet and ensure the macros are copied too? that is the reason i am not able to do more of the files out there:rolleyes:
 
Re: goastros

Originally posted by tombaan
how can you copy a sheet and ensure the macros are copied too? that is the reason i am not able to do more of the files out there:rolleyes:
Click to expand...

Tombaan, good question - too don't know how to copy a macro from 1 sheet to another.
I usually copy a sheet from the consolidated file into a
excel sheet which already has a macro. ( as detailed by eb2_I485_RD0901 's instructions) .

What I can suggest is -
1> open both the attached 485rescan file (which has the modified rescan macro) & the consolidated file.
2> Copy the required sheets from the consolidated file to the 485 rescan file.
3> And now Close the consolidated file ( cos it take lot of RAM ).
4> Run the rescan.
Let me know if this helps.
 
copy macro from one sheet to another

These are the steps to copy a macro from one sheet to another
1. In the Macro window, select the macro you want to copy, click on Edit, this should open the Visual Basic window.
2. Under VBAProject - Modules - select the module which runs the macro. Right click and select export file
3. Open any other excel file, in the macro window, type in anything, this will enable the create button, click on create button
(this should open the Visual Basic window. right click on the Modules folder and select import file. Now you can import the file you exported in step 3.
4. Close the visual basic window.
 
I will start scanning for 02-071

Can anyone tell whether the macro skips already approved cases during rescan ?
 
It looks like the party is over

Goastro,

It looks like the party is over for the 01258 range. No approvals yesterday or today. Only two or three RFE. The 48 received ones are still forgotten, no movement on that category.
I am starting to doubt that the online status tell any closer thruth because we know for sure that it is inacurate, at least when it is regarding to finger prints. For example my finger print is not shown up to today, also I hasd one case on todays scan that said they received his finger print on Feb 02 and yesterday they sent a RFE. A finger print from Feb 02 should be expired already and instead of RFE they should sent another FP notice. So or they skiped the second finger print status or they sent a FP and said that it was RFE. Who knows? The point is may be we have a lot of more approvals then we think because of these status that are never updated. Also there are the transfers that looks like are never updated. What happen to all those people transferred? Just The Lord knows...
Attached is today's file for the data consolidation project. By the way I noticed that there is one incorrect information on version 56. There are 108 approvals on 01-258 not 107 as stated. Would you please correct it in the next version?

Best regards,

JLSBR
 
Re: It looks like the party is over

Originally posted by jlsbr
Goastro,

It looks like the party is over for the 01258 range. No approvals yesterday or today. Only two or three RFE. The 48 received ones are still forgotten, no movement on that category.
I am starting to doubt that the online status tell any closer thruth because we know for sure that it is inacurate, at least when it is regarding to finger prints. For example my finger print is not shown up to today, also I hasd one case on todays scan that said they received his finger print on Feb 02 and yesterday they sent a RFE. A finger print from Feb 02 should be expired already and instead of RFE they should sent another FP notice. So or they skiped the second finger print status or they sent a FP and said that it was RFE. Who knows? The point is may be we have a lot of more approvals then we think because of these status that are never updated. Also there are the transfers that looks like are never updated. What happen to all those people transferred? Just The Lord knows...
Attached is today's file for the data consolidation project. By the way I noticed that there is one incorrect information on version 56. There are 108 approvals on 01-258 not 107 as stated. Would you please correct it in the next version?

Best regards,

JLSBR
Click to expand...

Jlsbr, don't give up, I hope your good Karma of finding approvals continues.
Even though there were no approvals yesterday & today for SRC01-258, Surya13 found 2 for SRC01-255.
In fact is it possible for you to scan other dates around your date
say SRC01-254 to SRC01-258.
All the best.
 
Need Excel Help

Can any one excel/Vb guru do this -
Instead of asking for 1 sheet to be Re-scanned, if it could ask for
a range of sheets and scan multiple sheets.
That would be great help in rescanning the available SRCs.
 
consolidated_v57.zip

Changes since 56

I have checked src03-05,06,07,09
rescanned src03-03,04

Also copied those individually posted sheets after consolidated_v56 file

ie src01-253, 258 src02-175,208


pr
 
Two new approvals for SRC02074 on 12/02/03.

They are

SRC02074523xx
SRC02074523xx
 
Attached File: consolidated_v58.zip

Re-scanned the following:
SRC-02-039: 50000 - 53375

Last Scan Date: 12/03/2003
Notice Date: 11/17/2001
Last Activity: 12/02/2003 (Misc - "Approval or Registration Mailed Directly")!

Last Approval: 10/20/2003
Last Transfer: 07/23/2003
Last Resumed/FP Recv: 11/17/2003

Total Cases: 197

Approved/Completed: 86 (44%)
Withdrawn: 1 (1%)
FP sent: 1 (1%)
Misc: 9 (5%)
Received: 34 (17%)
Resumed/FP recvd: 44 (22%)
RFE Sent: 1 (1%)
Transferred: 21 (11%)
 
Scanned 01-271

The consolidated sheet has only 95 cases for 01-271

I rescanned the range 50000 to 60000 and here is the new file which has 255 entries.

Can we incorportae this in the consolidated file?

Is 50000 to 60000 the only range I should be scanning for?

Thanks

tembatsc
 
You must log in or register to reply here.
Top