Excel VB Macro

Peter B

I am creating a VB Macro which opens 10 files, merges the information by copying and pasting the info all into one file and then with the final big spreadsheet it creates a pivot table. However I cannot get it to copy the correct number of rows from each file because the "CTRL-A" function selects all the info but it is not the correct shape when pasting it into the "main" spreadsheet. How can I get it to only select the rows which has information in it and ignore other rows?

Also when I am "appending" the main spreadsheet how do I get the cursor to automatically select the next empty row so the information is pasted at the bottom of the spreadsheet?

Evil Phil

To select only the rows you want you need to look at using Ranges. Range(A1:J7).Select will, IIRC, select all the cells from A1 to J7. If not its not far off what will.

For the empty row I can think of scrolling down selecting each row to see which is empty but there's probably a far smarter way than that.

HTH.

Peter B

Yea think a loop system is best. Have realised this is one problem common to excel macros.

Just when I am opening the files how do I get check if the files exist locally?

Instead of full file paths "c:/some_folder/today/file_name.xls"

How can I insert a relative file path?

If I save the document countaining my macros in my "today" folder would this path do?

"file_name.xls"

cheers

Seth Brundle

use this to select all cells from A1 to J?:
xlsApp.Range("A1", "J" & xlsApp.ActiveSheet.UsedRange.Rows.count).Select

Use the CurDir() function to get the current path and then append the file name onto it.

Use FSO to find out if the file exists.