Importing, looping and saving (not really solved)

I have tried to look for the answers I need, but have not been able to find an exact answer.

I have managed to make macros that suits my specific needs, but I still have a few glitches I need help with, let me try explain as best I can:

I extract information from another program and export it into excel for easier reference and manipulation etc. Most of the time this is a two part process, as this program cannot give me all the info I need in one go. Anyway, the reports are always in a specific location and always have the same file extension (that is my 1st problem) when I run this specific macros, I need it to bring up a save prompt to change that file extension to a desired one, otherwise the 2nd half of my macros will not work and cause the program to crash. I have marked the spot where I need a user prompt to save inserted with a MsgBox "this is where the save function needs to be"

Then, the next part of my problem is that I deal with variable report lengths ranging from less than 100 lines to more than 60 000 lines. The reports are in a set format all the way from the beginning to the end. I need to remove the page breaks in these reports to have the reports 100% the way I need them. The 1st page is 62 lines (line 1 to line 62) then, the page breaks are 9 lines, then from page to onwards each page is exactly 57 lines followed by a 9 line page break. The reports will range from Cell A to Cell S (never more for the final report that I want)

I think I need a loop function? to remove those 9 line page breaks. This can be the last thing that the macros needs to complete, so it can be inserted right at the bottom of what I have so far.

Please excuse my macros, I am quite new to this.

Please Login or Register  to view this content.

I have uploaded an example of what the report looks like after it is imported (currently) and if you scroll down you will see how I need it to be after the areas highlighted in yellow have been removed.

Maybe the first part of your question can be answered by use of the Name As and FileCopy statements rather than opening the file and resaving it with a new name.

For the second part, what do you mean by a 9 line page break? Is it just a gap of nine lines or is there actually a hard page break in there as well?

An example report with before and after would be useful.

Attachment doesn't seem to have worked.

Example which creates a copy and then renames the copy.

Please Login or Register  to view this content.