Replies

Hi,

Zeddy

Does the number of rows vary for each Client???? Yes.

Maudibe

And will there always be only 2 clients per text file?
On scrolling the text file i found 2 to 3 records exists.

How do we overcome this difficultly.

Thanks

Hi Maud,

MARVELLOUS Maud,

It’s perfect and appends the data to cells, only the date ‘Sanction Date’ & ‘Closure Date’ gets converted, will have to maintain to date format.

First record is appended properly and how to append the next record of text file to sheet, the text file contains more records.

Many Thanks.

Thanks Paul T for the reply.

The TXT file is not in a format that is easy to import into Excel.
That’s true Paul but only available to me is this and i need to do it.

Do you want a record per page? Well if that is possible then manually data cleaning will be easy for me.

Do you want a page listing all people, then another page for their data?

Any other format?
Paul actually i am not in a position to say anything, infact if you have any way please help me.

cheers, Paul

p.s. I hope that’s not real data in that sample! Yes, but the format is intact.

One more thing i would like to mention that those xxxxx in data is not required to be appended in sheet.

Hope to have a favourable reply.

Thanks Maud,

Yes thats what i wanted, but for sample i had created that if A,B & C match then move to E column, infact there are chances that cell may contain data or may be the next one also so its better that we move to the empty cell instead of fixed cell.

Thanks in advance.

Hi Steve,

It creates the transform sheet superbly, Thanks.

One last thing i export this sheet to .pdf format how to set this sheet to A4 size with 3 sets in each sheet.

Code:

Sub export()
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=”C:UsersfjohanDesktopformatedlist.pdf”
End Sub

fjohan,

Just for another way here’s a powershell script that will work.

Hi, RetiredGeek,

I tried my hands on your powershell script it show an error on line 26 & 27.

You cannot call a method on a null-valued expression.
At C:AutoSubstText.ps1:26 char:14
+ $x = $line.tostring()
+ ~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At C:AutoSubstText.ps1:27 char:14
+ $y = $x.substring(3)
+ ~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull

Hi,

Thanks a lot for every one for wholeheartedly support.

BATcher it works perfectly, I have edited my second post.

Thanks.

Hi,

Thanks a lot BATcher for script.

It works perfectly. many Thanks.

Hi,

Thanks a lot.
=MID(B1,4,3)

Done.

Hi,

Thanks to all for your valuable answers, but now i face a new difficulty if suppose the mid part contains a zero then that gets trimmed in col A.
if its 001 then column A has value of only 1, if its 011 then column A has 11. I want to retain that leading zero’s also.
Please suggest.

fjohan,
Challenging project
Maud

Hi Maud,
Thanks for your efforts on this Challenging project, Highly obliged.

Maud, Sorry to say but in-depth search the script fails to generate result on query.
I think the import xml files on tab (file) imports only one xml and searches for our query, whereas it should import all the xml files from the folder and search our query. Please have a look into this.
Thanks in advance.

Thanks Maud,
Sorry for delay reply, I did a test run on it & found it very useful, successful script on query search for which i am thankful to you and highly obliged.
Maud, I need a small change if its possible, when a particular search is given it display the search result is it possible to display the other details of that tag. If like suppose search for SerialNo= ‘000512’ it will also display the Amount, ItemSeqNo,PresentingBankRoutNo,AccountNo in the descending rows.
Thanks

fjohan,

I had set it up to search for all values for a parameter instead of a certain values for a parameter. It makes sense with what you are looking to do. I had assumed that the folder would be a dedicated folder/subfolder for XML files. I will take a look at it tonight.

Maud

Hi Maudibe,

If you have updated the file please forward me.

Thanks in advance.

fjohan,

I had set it up to search for all values for a parameter instead of a certain values for a parameter. It makes sense with what you are looking to do. I had assumed that the folder would be a dedicated folder/subfolder for XML files. I will take a look at it tonight.

Maud

Hello Maudibe,
If you have updated the file please forward me.

Thanks Maudibe
Its very nice of you and once again i thank you very much for helping me.
Sir, The codes are perfect but for more powerful and to get the dot results i want some small changes to it.

1) Search Only .XML files
2) Search based on input value of criteria, search for all .xml files in folder & sub-folder to the last row of each file for getting value.

Thanks.
35091-search