Handling multiple txt files within single excel sheet

Hi guy,

I only registered in this site today since I am going to be spending some of my time from now on to perfect my Excel programmimg skills from, well scratch really. So, I would need you experts help.

My first task is to write a macro that will:

-Allow me to selects a number of files in a directory (so when I run it opens the directory to allow me to select a number of files in one go)
- Selects same two coloumns in all files
-plots on the same scale

To start with, I would appraciate if I get some heads up. Right now, as a first exercise, I am recording a macro to see the automatically generated code.
Thanks.

Sound approach. That will give you the chart building VBA code.

You may either produce the chart as you open each file or leave that and batch chart them when all the files have been opened.
Batching allows you to know the range of values in the different files and fix the axis dimensions so they are all the same and allow good visual comparison (if desirable).

Here is a functionthat asks the user to select a file (or files) to open:

Public Function fncFileSelected(prompt As String, Optional myPath As String = "")
Dim FD As FileDialog
Dim WKB(10) As Workbook
On Error GoTo Err_fncFileSelected
Set FD = Application.FileDialog(msoFileDialogFilePicker)
FD.AllowMultiSelect = False ' make this True to collect all at one prompt*
FD.ButtonName = "Select " & prompt & " file(s)"
FD.Filters.Clear
FD.Add "Excel Files", "" * .xls * ""
FD.Title = "Select needed files"

If myPath <> "" Then ChDir (myPath)

If FD.Show = True Then
fncFileSelected = FD.SelectedItems()
Else
fncFileSelected = ""
End If

Set FD = Nothing

Err_fncFileSelected:
Exit Function
End Function


Here's how to call it. For my preference, open the files one at a time (facilitates different folders)
if you choose to open the files in a single user prompt, you'll get an array of file details to unpack
For x = 1 To upperlimit(WKB)
myFile = fncFileSelected("File #" & x)
If myFile = "" Then Exit For
Set WKB(x) = Workbook.Open(FileMame:=myFile)
Next

Here add your recorded code for the chart...

And follow that with

Workbooks.Close True ' save them with the charts
For x = 1 To x - 1: Set WKB(x) = Nothing: End Sub



Best wishes