Helo with excel marco

hi,
With the grateful assistance i have been able to create a macro to help me process large quanitites of data.

Its a crude system at present but its works.

The first part of the macro opens files in a specific folder and transfers each file in a separate worksheet of a excel file.

This works great but I would like to improve on it by:

I think I need a dialog box to locate the folder and files. I will be dealing with numerous folder types with the raw data. Editing the macro to locate file in each specific folder will be a big pain. The reason the data will be in different folder could be based on the instrument that produces all our data.

I would also love if the macro could recognise that the data files regardless of file extension will be comma separated values but will not necessarily have a CSV extension, They could have rep, txt, CSV or even no extension depending on the setup of instrument processing software.

The macro works beautifully in opening each file separately and putting the data into each worksheet. Is there a more efficent method of doing this process rather opening multiple excel files and closing them again? Should this macro be run from VB or can excel run the process? These are just techincal queries. I am happy at the moment that it works!!!

I would like when all the files are insterted into the single excel file that a blank row is inserted above A1 cell in each sheet. I need to insert a blank row above A1 cell using this type of code but for all the new worksheets!:

Sub insertrow()
Range("A1").Select
Selection.EntireRow.Insert
End Sub

Or (b) if possible import the files into a worksheet at A2 instead of A1, which ever is the easier process!!
i need this blank row as my data macro needs it to rearrange my data in the proper format and graph it.

Once all the data is in different sheets my next task/challenge will to be run my second macro on all newly created sheets!! :-)

The graphing part of macro two is one of my problems in that it names a specific sheet with a name! but I will be working with sheets with potentially different names. I need the macro to automatically run for each sheet. I am including a attachment files of what i am trying to achieve.

I am 95% of the way to solving my problem so hopefully somebody outthere can help.
Regards,
Frank

Hi Frank,

for

"if possible import the files into a worksheet at A2 instead of A1, which ever is the easier process!!
i need this blank row as my data macro needs it to rearrange my data in the proper format and graph it. "


I think this line in your code will make it.

Please Login or Register  to view this content.

Search in your code this part

Please Login or Register  to view this content.

And insert the line as shown.

Hope this help...


REgards

JOse Luis

Originally Posted by frankcase

hi,
With the grateful assistance i have been able to create a macro to help me process large quanitites of data.

Its a crude system at present but its works.

The first part of the macro opens files in a specific folder and transfers each file in a separate worksheet of a excel file.

This works great but I would like to improve on it by:

I think I need a dialog box to locate the folder and files. I will be dealing with numerous folder types with the raw data. Editing the macro to locate file in each specific folder will be a big pain. The reason the data will be in different folder could be based on the instrument that produces all our data.

I would also love if the macro could recognise that the data files regardless of file extension will be comma separated values but will not necessarily have a CSV extension, They could have rep, txt, CSV or even no extension depending on the setup of instrument processing software.

The macro works beautifully in opening each file separately and putting the data into each worksheet. Is there a more efficent method of doing this process rather opening multiple excel files and closing them again? Should this macro be run from VB or can excel run the process? These are just techincal queries. I am happy at the moment that it works!!!

I would like when all the files are insterted into the single excel file that a blank row is inserted above A1 cell in each sheet. I need to insert a blank row above A1 cell using this type of code but for all the new worksheets!:

Sub insertrow()
Range("A1").Select
Selection.EntireRow.Insert
End Sub

Or (b) if possible import the files into a worksheet at A2 instead of A1, which ever is the easier process!!
i need this blank row as my data macro needs it to rearrange my data in the proper format and graph it.

Once all the data is in different sheets my next task/challenge will to be run my second macro on all newly created sheets!! :-)

The graphing part of macro two is one of my problems in that it names a specific sheet with a name! but I will be working with sheets with potentially different names. I need the macro to automatically run for each sheet. I am including a attachment files of what i am trying to achieve.

I am 95% of the way to solving my problem so hopefully somebody outthere can help.
Regards,
Frank