VB Code to transfer some data from excel to txt file

[rizmomin]

Hello:

Please refer to attached file.
I need to create txt file following manner from the data in Sheet1 as below.

For each employee there will be set of data which needs to be in the format shown in attached txt file. Employee name is in column C.


Line 1 : !Type:Bank
Line 2 : Date in column A for new employee. (D12/14/2016)
Line 3 : Find "NET PAY in Column E and put the value from column F (T-629.80)
Line 4 : Check # as in Column B (N1554)
Line 5 : Employee Name (PALI Momin)
Line 6 : Text from column D (SPayroll:Gross)
Line 7 : Text from column E (Spayroll:Taxes)
Line 8 : Text from column F ($-800)
Now need to skip Date,Check# and Employee Name until next employee

Line 9 : Text from column D (SPayroll:Taxes)
Line 10 : Text from column E (Esocial Security - Company)
Line 11 : Text from column F ($-49.6)

And so on…until the next employee.
There needs to be ^ character after each employee (before the start of new employee.

I have attached text file with this to understand.

Please let me know if any questions.
Thanks

RM

[rizmomin]

Hello:

Please use atatched txt file for referance.
Thanks
RM

[jindon]

See if this is how you wanted.

Please Login or Register  to view this content.

[rizmomin]

Hi Jindon:

Looks like working, i will do more testing and may need little tweaking to complete.
Will let you know for sure.
Once again thank you so much for such a great help.
RM

[rizmomin]

Hello jindon:

Needs little tweaking as follows.
Please refer to attached files.
I have attached file and look at Sheet3 to show you how txt file should be.
On that Sheet3, column B, i have pit some explanation which will help you understand.

I have included the txt file which is how it needs to be.

Also i have put the name and location of the file which needs to be saved at Sheet1, cell I1

Please let me know if you have any questions.
Thanks.

RM

[rizmomin]

Hi Jindon:

After careful reviewing following changes are needed.
!TYPE:Bank ......ONLY NEED ONCE AT THE BEGINING OF THE FILE.
At the end of each employee need "^" character....not forgetting only 1 ^ character at the end of file.
Save the file as shown in the location I1.
I think this should do it.
PLEASE REVIEW the files i have attached earlier
Please let me know if you have any questions.
Thanks.

Riz

[rizmomin]

Hi JinDon:

Also would like to point out that each of the column of data have formulas in it so can you please code so that the formula is not affected.
Thanks and let me know if any questions.
Riz

[jindon]

Try this one.

Please Login or Register  to view this content.

[rizmomin]

Hello Jindon:

Thanks for the update.
Still some problems.
Have a look at attached excel file and txt file.
I have highlighted showing the data for 2 employees.
For this file
Row 1 to 17 belongs to Angela Momin highlighted yellow.
Row 18 to 13 belongs to Humazehra Momin highlighted gold.

1st you need to get Net Pay for each employee.
Then you need to complete all data pass Net Pay if the employee data is there.
Your code is thinking to have new employee after Net Pay.
Its liitle difficult to explain but i have atatched the corrected txt file which how it needs to be.
Have a look and let me know if any questions.
Thanks
RM

[jindon]

Now confusing.
Second person in your text file only has

D12/16/2016
T-683.89
N1001
PHumazehra N.Momin
SPayroll:Gross
ERegular Hourly Wages
$-825
SPayroll:Taxes
ESocial Security - Company
$-51.15
SPayroll:Taxes
EMedicare - Company
$-11.96
SPayroll:Taxes
EFederal Unemployment Insurance
$-4.95
SPayroll:Taxes
EGA Administrative Assessment
$-0.66
SPayroll:Taxes
EGA Unemployment Insurance
$-3.3

Why?

[rizmomin]

Hello:

Sorry that was my mistake.
Doing in a rush....error.
Please have a look at corrected txt file.
Once again I am sorry for any confusion but i beleive this will hep you understand.
Please let me know if you have any questions.
Thanks.

RM

[jindon]

Try

Please Login or Register  to view this content.

[rizmomin]

Hello:

Seems to be correct format now.
I will test it to import into Quicken and see if all is ok.
Will let you know if any furtrher modification is needed.
Thanks a lot
RM

[rizmomin]

Hi Jindon :

Works great so far without any problem.
Need to add the code to make sure that it only pull the data until the last valid row of data.
Probably the best way is to check in column A , pull until there is D as first character.
Please let me know if you have any questions.
Thanks.

Riz

[jindon]

Please Login or Register  to view this content.

[rizmomin]

Hi Jindon:

I get attached error when i use above code.
Please have a look at attached 2 pics
Please let me know if you have any questions.
Thanks.

Riz

[jindon]

Picture doesn't help at all.

I need to see your file.

[rizmomin]

Hello Jindon:

Please refer to attached ACTUAL file.
Here I import data file in sheet "Payroll".
Then i have formulas in sheet "Create QIF" where it populated data from Payroll to "Create QIF".
In "Create QIF" I run your smart created macro to generate txt/qif file.

Please let me know if you have any questions.
Thanks.

Riz

[jindon]

Change to

Please Login or Register  to view this content.

[rizmomin]

Hi Jindon:

Thanks a lot........................
Works
Riz