Posts by asheshrocky

    Hi All ,


    I have a root Folder which consist of Multiple Folder named as "Folder_1_2" , "Folder_10_5" and so on. Each of these Folders contains 100's of files. The name of the files looks similar to this "Value_loop_A_B_1113_1110_human_1.txt".
    The text between 4th and 6th Underscore which is "1113_1110" is termed as "Structure Name" and the value after last underscore "1" represent the test number. For successive test the file name will change as "Value_loop_A_B_1113_1110_human_2.txt" , "Value_loop_A_B_1113_1110_human_3.txt" and so on.


    For the sample data i have , each structure Name is retested for 21 times (But this number can vary , Not fixed). The same folder will contain datalog for multiple Structure Name , like "Value_loop_A_B_1013_1220_human_1.txt" and many others.


    Content of the File :


    WaveLength (nm) GeneratedPower(dBm)
    1.5000000000E+3 -2.5031862552E+1
    1.5000500488E+3 -2.4802969931E+1



    The Wavelength remain the same for all files with same Structure Name , irrespective of re-test. The only value which changes in Re-tested files are the Generated Power column. But the Wavelength value could change for files with different structure Name.


    I want to loop through all the files and create one Master file which consists of data from all the files and output them as a CSV file.
    In Master file the Generated Power value from each Retest file will appear as 1 column column, Like


    GeneratedPower_1 , GeneratedPower_2, GeneratedPower_3 ........GeneratedPower_N


    The outerMost Folder Name contain information about X and Y Co-ordinate which is 1 & 2 for "Folder_1_2" ----> X = 1 and Y = 2
    The Next two column will contain Structure Name (Taken from each filename) and Wavelength Value.


    X, Y, Structure Name, Wavelength, GeneratedPower_1 , GeneratedPower_2, GeneratedPower_3 ........GeneratedPower_N



    You can find the data log and expected output file in the attachment.


    Thanks
    forum.ozgrid.com/index.php?attachment/70738/

    Re: VBA Macro to Write Data into Text File


    Hi Norie ,


    I cannot use that approach to read all at once , Because i need to copy out only Those Data which appears after "Lamba" .
    As mentioned , i had a separate block of code finding term "Lamda" and storing the "lineNumber" of its first occurrence


    I tried to store the Content into an Array and then Dump all at once , That really speed up the things and now it looks ok.




    Hi ,


    I have written a piece of VBA Code which does the Following ,


    1. Read through Line by Line from a given Text file
    2. When Found a line which starts with term "Lamda" then Read all data from Next line onwards till the End of File
    3. Write data into another text file



    I have written another function which find the Line Number in the file when "Lamda" is found and store than into variable named "lineNumber".


    Content of File :


    Code
    Lamda (nm)    Power (dBm)
         1.5000000000E+3        -1.4547327909E+1
         1.5000500488E+3        -1.4640089201E+1
         1.5000999756E+3        -1.4786769540E+1


    The Number of lines in a text file runs to about 3000-4000 lines , The code which i have written works , But its it just too slow. It takes approximately 8 seconds to write just one file content. I have a total number of 650 files to process.


    Can someone help me to optimize my code ,


    Thanks

    Hello All ,


    I have a following requirement which i managed to code using shell script but couldn't get it working using Excel Macro ,
    I have a root Folder Lets name it as WholeDie, There will be two level to Subfolder within this Root Folder as shown below ,


    [ATTACH=CONFIG]70044[/ATTACH]


    There will be one CSV file each inside Para1 & Para2 Folder under each X-Y coordinate folder.
    The Folder name "Para1" & "Para2" will be same for all X-Y combination and so is the Name of CSV file inside these Folder.


    Lets assume the CSV file under all Para1 folder will always be coff.csv and CSV filename under Para2 will always be Ron.csv
    I am needed to create Two Master file namely Master_Coff.csv & Master_RON.csv.


    The content of the Coff.csv file is explained below ,
    It always consist of Two Columns , with the Header name in Row 4 . Thus First Column namely in A4 is "freq"and Second Column in B4 is "coff" (The First three Row content is useless and Need not be Copied into Master file)


    Content of coff.csv

    Code
    Freq                  Coff
    User1                2.1
    User2                3.1
    User3                4.1
    .
    .
    .


    The content of the RON.csv file is explained below ,
    It always consist of Two Columns , with the Header name in Row 4 . Thus First Column namely in A4 is "freq"and Second Column in B4 is "ron" (The First three Row content is useless and Need not be Copied into Master file)


    Content of Ron.csv

    Code
    Freq                  Ron
    User1                2.1
    User2                3.1
    User3                4.1
    .
    .
    .



    The first column namely Freq is same across all coff.csv file and same in all Ron.csv file , so the first column in Master file can be copied from any of the coff.csv and ron.csv file.
    The only column which i need to copy from different coff.csv and Ron.csv file is the second column namely coff and Ron , for Example the Master_Coff.csv will look like this ,


    Content of Master_Coff.csv

    Code
    Freq                   coff-X2-Y5          coff-X3-Y7
    User1                     2.1                    4.1
    User2                     3.1                    5.1
    User3                     4.1                    6.1
    .                             
    .
    .


    As you see i need to append coff column from each coff.csv file into the Master file and Column name is appended with the corresponding Folder name (X2-Y5) AND (x3-Y7)
    The same applied for Master_RON.csv file where instead of copying coff column from each folder , we need to copy Ron Column.


    Content of Master_Ron.csv

    Code
    Freq                   Ron-X2-Y5          Ron-X3-Y7
    User1                     2.1                    4.1
    User2                     3.1                    5.1
    User3                     4.1                    6.1
    .                             
    .
    .


    Let me know if require further clarification on this.

    Hi ,


    I have a requirement to generate an Output Table based on input file.


    I need a Macro which enable User to choose an Input File in CSV format and then click on button named "Generate" to create the Table as explained below,



    [TABLE="width: 500"]

    [tr]


    [TD="align: center"]Sbin[/TD]
    [TD="align: center"]Parameter Name[/TD]
    [TD="align: center"]Number of Dies[/TD]
    [TD="align: center"] % (Total Num/Number of Dies) [/TD]

    [/tr]


    [tr]


    [TD="align: center"]1[/TD]
    [TD="align: center"]Parameter A[/TD]
    [TD="align: center"]16[/TD]
    [TD="align: center"]16%[/TD]

    [/tr]


    [tr]


    [TD="align: center"][/TD]
    [TD="align: center"]Parameter B[/TD]
    [TD="align: center"]4[/TD]
    [TD="align: center"]4%[/TD]

    [/tr]


    [tr]


    [TD="align: center"][/TD]
    [TD="align: center"]Parameter C[/TD]
    [TD="align: center"]10[/TD]
    [TD="align: center"]10%[/TD]

    [/tr]


    [tr]


    [TD="align: center"][/TD]
    [TD="align: center"]Parameter D[/TD]
    [TD="align: center"]5[/TD]
    [TD="align: center"]5%[/TD]

    [/tr]


    [/TABLE]



    I have assumed the Total Count as 100 in above table for calculating the %.
    To generate the above said table , i would need to consider 5 parameter Header from Input File Namely (DieX, DieY,Sbin,PartID,<Last Filled in Parameter>)




    The position of these Headers are always fixed , But i would want to make it flexible to search the same by Name instead of Header position .


    STEPS:

    1.The first step would be to Sort the Column named Sbin (Its a field with Numeric Value).
    2.Enter the smallest value of Sbin (Small to Highest) in the Table in first Cell under Sbin header as shown in Table above.
    3.From the 10th row onward in the Input file we will have Test Parameter Name (1-9) are Fixed headers.I need to count the Parameter Name which was the last tested parameter.
    The last filled in column is considered as the Last Tested Parameter.


    4.Sometime the same DieX, DieY position might be tested twice , in such scenario i want to consider only the row for which the PartID was maximum.
    5.Please refer to the attachment to view the Sample Input data and explanation on how to generate the output.



    The attachment added is in .xlxs format , But the actual file will be in CSV format.
    Since the size is Big, i am not able to attach it here, I can send u the file personally.


    Let me know if you have any query with the requirement.

    Re: Running batch file using Excel Macro


    Hi Guys ,


    Any response to my query,
    The batch file has the following code,


    Code
    @ECHO OFF
    ChDir C:\Users\ashish\AppData\Local\Local_NoBackup\Tester-Correlation\Package
    cls
    java -cp .;JSTDF_UFLEX-0.0.1-SNAPSHOT.jar STDFExportCSVFilesWoutTestnum
    pause


    When i double click on this bat it works perfectly and also i can run it via Command promt without issue.
    Its only when i call the batch file using Excel VBA it fails.


    Any suggestion would be of great help.

    Hi Guys ,


    Am facing some issues while trying to execute Batch file from excel macro.


    Code
    @ECHO OFF
    ChDir C:\Users\E701152\Test\Tester-Correlation
    cls
    C:\WINDOWS\system32\java.exe -cp JSTDF_UFLEX-0.0.1-SNAPSHOT.jar -Xms1024m -Xmx1300m STDFExportCSVFilesWoutTestnum
    pause


    Code
    Private Sub CommandButton1_Click()
    Dim parameter As String
    Dim sh, ReturnCode
           
           Application.ScreenUpdating = True
           Set sh = CreateObject("WScript.Shell")
           parameter = Chr$(34) & ThisWorkbook.Path & "\runCorrelation.bat" & Chr$(34)
           ReturnCode = sh.Run(parameter, vbNormalFocus, True)
    End Sub


    When i double click the runCorrelation.bat file it executes properly without any issue,
    While i make the call from Excel macro it just open and does nothing.......


    Recently we have upgraded our OS from XP to win 7 .......It was working all fine in XP environment.
    Any suggestion on this issue ?


    Thanks

    Hi All ,


    Can someone help me with this , i am stuck with creating an Custom Order List to sort my Column based on Range value .



    I always get an error saying Method 'AddCustomList' of Object '_Application' Failed.


    The sample set of Data in the Range is values as below ,



    Is it because the Value is Number > And if so how can i add the list based on Numbers , Please Help.


    Thanks

    Hi All ,


    I am facing issues trying to implement Custom Sort using VBA ,
    I have recorded Macro as shown below to perform sorting .



    I want to pass the Custom List values as an Array variable ,
    I tried to Store the Range values into Variant Variable and then pass it to CustomOrder , but i get an error saying "Method AddCustomList _Object Failed"


    The values which i would like to add into Custom List is in range "A2:A48".


    Thanks

    Hi All ,


    I have a requirement to achieve something as shown Below ,
    Am having a Certain Values in Column A ,


    Code
    A
    1
    4608
    4609
    4831



    I need to repeat Occurrence of these Values for defined Number of times based on Min and Max from from Another Column , Say for example the Min=0 and Max=3 in this example , then i need the Output to be



    Thus Column A value Repeat itself from Min To Max and its corresponding Column showing the Count Value (Min to Max)


    Thanks

    Re: Count values in Array


    Hi Patel ,


    I mean to say the data in Column A shown in SampleData is stored in VarArrInput1(,4) and Column B is stored in VarArrInput1(,5) ......Hope its clear now.


    Will it be better if i can copy the data from Array to a range within worksheet and then use Normal SumProduct function to get my work done ?
    Will this mechanism be faster than looping entire Array Elements?


    Please suggest !