What I would like to do is fairly straight forward, but I am having difficulty finding an answer that suits my needs. There have been replies to this question in other places, but they have not worked. So this is what I would like to do.
Background: I have built a spreadsheet that analyzes data that I routinely insert into excel via a copy/paste macro in word. But since this is a manual task, I can only insert one set of data at a time. I would like to import several .rpt files at once and modify my data analysis spreadsheet to analyze all of them at once....Instead of opening a word file, run the data select macro, open excel, paste it in ..and so on.
Ideally, I would like some VBA code that will
1. prompt me to select the .rpt files from a folder I wish to import.
-not from a coded directory. Something like a File>Open window.
2. The user will select multiple .rpt files from the folder
3. Import the .rpt files into a single worksheet named 'ANALYSIS' in different columns - preferably each file will be separated by one column of empty space (A..C..E..etc).
-I do not want any kind of delimiters, though. My spreadsheet is already coded to do this later.
Thanks a bunch. Any useful tips would be helpful, as this is the last piece of the puzzle I need for a truly awesome analysis spreadsheet.
--G.
Background: I have built a spreadsheet that analyzes data that I routinely insert into excel via a copy/paste macro in word. But since this is a manual task, I can only insert one set of data at a time. I would like to import several .rpt files at once and modify my data analysis spreadsheet to analyze all of them at once....Instead of opening a word file, run the data select macro, open excel, paste it in ..and so on.
Ideally, I would like some VBA code that will
1. prompt me to select the .rpt files from a folder I wish to import.
-not from a coded directory. Something like a File>Open window.
2. The user will select multiple .rpt files from the folder
3. Import the .rpt files into a single worksheet named 'ANALYSIS' in different columns - preferably each file will be separated by one column of empty space (A..C..E..etc).
-I do not want any kind of delimiters, though. My spreadsheet is already coded to do this later.
Thanks a bunch. Any useful tips would be helpful, as this is the last piece of the puzzle I need for a truly awesome analysis spreadsheet.
--G.
