I'm a newbie to the board and to both Excel applications and VB.
Not looking for detailed help just now -- but more a view as to whether my project is realistic, achievable, and that I am approaching it in the right way. I hope you will feel able to offer constructive comment.
We have to produce (weekly) an output file in .csv format. This file has a header row in Row 1. And a footer row. The footer row counts the records inbetween the header and footer and totals the cash value of all the inserted rows. Both header and footer have tightly defined formats that we must ahere to. Each week's file must be differently named as all must be kept for audit purposes.
The data to be inserted between the header and the footer comes from a feeder system. The system can export in .xls or (supposedly) in .csv.
One of the columns from the feeder data includes a leading zero. This is Problem 1. I must retain the zero. (It's a Bank Sort Code)
Problem 2: the insertion of data from the feeder file is to be done by end users with little IT knowledge/skill.
I have a template file with the correct formatting in .xls format. And VB written that will handle the automatic sequential naming of the file and the insertion of the filename into the final output sheet. This originates from a closely related project that I have successfully working (in this case the user "manually" inserts records using a command button I have placed on the sheet to insert and format a new row plus add two fixed data fields -- the user adds a further three fields per inserted row).
To meet the additional requirement of inserting the output data from the feeder system I would like to modify my working spreadsheet to do the following:-
Add a new command button that:
1) throws up an input box for the user to input the filename and path for the feeder sheet;
2) Gets the file (probably .csv) and opens it in Excel;
3) strips the header and footer from it (these are applied by the feeder system and are not required); Then
4) counts the rows; and
5) inserts the same number of rows between the header and footer in my spreadsheet project; the data is then
6) copied to the clipboard from the feeder sheet; and
7) pasted into place in my spreadsheet so that the footer row is moved down and not overwritten;
8) I can then use my existing code to handle the name and save operations.
The theory sounds OK to me and I've stepped through the process manually. I just want to know if it is sensible to automate in the same way or if there are better approaches.
My significant challenges are:
A) Attempting to make the process almost a single button operation for the user. They have little or no knowledge of Excel.
B) Keeping the leading zero in the Sort Code. Manual exploration suggests that this is most easily done by taking input as .csv. This may be a quirk of the feeder system. It has many.
C) The feeder system is currently separating fields in .csv output with ..... SEMI COLONS!!
D) Challenges B and C above appear to me to be incompatible.
I welcome your comments and apologise for the length of my introductory post.
thank you
_________________
Beth
This message was edited by beth on 2002-10-09 16:54
Not looking for detailed help just now -- but more a view as to whether my project is realistic, achievable, and that I am approaching it in the right way. I hope you will feel able to offer constructive comment.
We have to produce (weekly) an output file in .csv format. This file has a header row in Row 1. And a footer row. The footer row counts the records inbetween the header and footer and totals the cash value of all the inserted rows. Both header and footer have tightly defined formats that we must ahere to. Each week's file must be differently named as all must be kept for audit purposes.
The data to be inserted between the header and the footer comes from a feeder system. The system can export in .xls or (supposedly) in .csv.
One of the columns from the feeder data includes a leading zero. This is Problem 1. I must retain the zero. (It's a Bank Sort Code)
Problem 2: the insertion of data from the feeder file is to be done by end users with little IT knowledge/skill.
I have a template file with the correct formatting in .xls format. And VB written that will handle the automatic sequential naming of the file and the insertion of the filename into the final output sheet. This originates from a closely related project that I have successfully working (in this case the user "manually" inserts records using a command button I have placed on the sheet to insert and format a new row plus add two fixed data fields -- the user adds a further three fields per inserted row).
To meet the additional requirement of inserting the output data from the feeder system I would like to modify my working spreadsheet to do the following:-
Add a new command button that:
1) throws up an input box for the user to input the filename and path for the feeder sheet;
2) Gets the file (probably .csv) and opens it in Excel;
3) strips the header and footer from it (these are applied by the feeder system and are not required); Then
4) counts the rows; and
5) inserts the same number of rows between the header and footer in my spreadsheet project; the data is then
6) copied to the clipboard from the feeder sheet; and
7) pasted into place in my spreadsheet so that the footer row is moved down and not overwritten;
8) I can then use my existing code to handle the name and save operations.
The theory sounds OK to me and I've stepped through the process manually. I just want to know if it is sensible to automate in the same way or if there are better approaches.
My significant challenges are:
A) Attempting to make the process almost a single button operation for the user. They have little or no knowledge of Excel.
B) Keeping the leading zero in the Sort Code. Manual exploration suggests that this is most easily done by taking input as .csv. This may be a quirk of the feeder system. It has many.
C) The feeder system is currently separating fields in .csv output with ..... SEMI COLONS!!
D) Challenges B and C above appear to me to be incompatible.
I welcome your comments and apologise for the length of my introductory post.
thank you
_________________
Beth
This message was edited by beth on 2002-10-09 16:54
