Excel Project - insert contents of second file between heade


New Member
Oct 8, 2002
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

This message was edited by beth on 2002-10-09 16:54

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Hello Beth:

Because you are only looking for an opinion at this time, here's mine. Based on your detailed description (one of the better outlines of a posted challenge I've seen; good for you), I'd say everything appears do-able, barring unmentioned unusual obstacles.

Isolating your challenges one by one, if you search this board you'll find many related questions that were answered by knowledgeable programmers and formula experts who frequent this board.

If you run into difficulties as you progress through the project, just post the question with specific details as you did here, and you can probably successfully complete your project without too many bruises.

Good luck.
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back