Helo with excel marco

[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


+-------------------------------------------------------------------+
|Filename: CE DATA.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3563 |
+-------------------------------------------------------------------+

 

[jose luis]

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.



Code:
--------------------
Sheets(1).Rows("1:1").Insert Shift:=xlDown
--------------------



Search in your code this part


Code:
--------------------
For Each wkbBook In Workbooks
If wkbBook.Name <> strName Then
wkbBook.Worksheets(1).Copy before:=Workbooks(strName).Worksheets(1)
Sheets(1).Rows("1:1").Insert Shift:=xlDown
wkbBook.Close savechanges:=False
End If
--------------------



And insert the line as shown.

Hope this help...


REgards

JOse Luis