Import Historical Stock Price Data

[london735]

OK...I figured it out, the second parameter in Array(Array(1, 2) should be changed to 1
I have one more question:
How do I prevent the import of the text files which have already been analyzed? The text file names are recoded in the columns C of the A+B workbook.
I need this small modification to the macro given above. I guess the code should check if the name of the text file to be imported is already present in this column...I ask for your help in adding this modification because the workbook cycles through all the available text files whenever I start the macro - even if there in only ONE new file in the folder that needs to be analyzed.

[SAS888]

We have subline of the code FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2),Array(6, 2)).
In each Array(x, y) - x is number imported column, y - is option.
There is explanation in reference (Help for VBA Excel):

XlColumnDataType can be one of these XlColumnDataType constants.
xlGeneralFormat General
xlTextFormat Text
xlMDYFormat MDY date
xlDMYFormat DMY date
xlYMDFormat YMD date
xlMYDFormat MYD date
xlDYMFormat DYM date
xlYDMFormat YDM date
xlEMDFormat EMD date
xlSkipColumn Skip Column

For example, Array(1, 2) - format of column 1 is "Text", Array(5, 1) - format of column 5 is "General", Array(6, 9) - column 6 will be absent.

That macro processed all files in folder (without the ".txt" suffix), line of the macro's code

FName = Dir(FPath & "*.txt")

change on

FName = Dir(FPath & "*")

To prevent the import of the text files, which are already analysed, possible so:

Sub AllFilesProcessing()
    Dim FPath As String, FName As String, x As Range: Application.ScreenUpdating = False: Application.DisplayAlerts = False
    FPath = "C:\MyTemp\" 'Insert path
    FName = Dir(FPath & "*")
    Do While FName <> ""
        Set x = Sheets("A+B").Columns("C").Find(what:=FName, LookAt:=xlWhole)
        If x Is Nothing Then
            Range([A50], Cells(Rows.Count, "F")).ClearContents
            Workbooks.OpenText Filename:=FPath & FName, Origin:=xlWindows, DataType:=xlDelimited, _
                Space:=True, FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2), Array(6, 1))
            With ThisWorkbook.Sheets("1")
                Range([A1], Cells(Rows.Count, "F").End(xlUp)).Copy: .[A50].PasteSpecial Paste:=xlPasteValues: .[B48] = FName
            End With
            ActiveWorkbook.Close
            GATHERCORRECTED
        End If
        FName = Dir
    Loop
    [C1].Select: MsgBox "Done"
End Sub

[london735]

thank you!!! I used the correction that you provided to record the file without the TXT suffix but it continues to record them this way. How can this be corrected?
Hoping to hear form you soon!))

[london735]

BTW that part of the macro which checks if the text files has already been analyzed - works perfectly!)
THANK YOU VERY MUCH FOR IT!)

[SAS888]

Do you want to write the name of the processed files without the TXT suffix in cell of worksheet? This possible, but then compare the current filename with names, which is processed already, will not comfortable. Or do you want something another? Where are names without the TXT suffix must be written?

[london735]

thanks you - I just need ot record text file names without the ".TXT" suffix - I will rename the records of the files which have already been analyzed. There needs to be no change for the location of the files - only the names of the recorded files should change to be pure - without TXT.
Thank you for your continuing help and I hope to hear form you soon!

[SAS888]

If I have understood You it is correct, that You need for following:
You should change code

.[B48] = FName

on code

.[B48] = Left(FName, Len(FName) - 4)

If extension can contain free amount a symbols, that you may use method "GetBaseName" from FileSystemObject.

[london735]

Сообщение от SAS888

If I have understood You it is correct, that You need for following:
You should change code

.[B48] = FName

on code

.[B48] = Left(FName, Len(FName) - 4)

If extension can contain free amount a symbols, that you may use method "GetBaseName" from FileSystemObject.

Thank you very much!))))) Got home very late this evening - going to test it tomorrow and let you knwo how it goes....Have a great weekend!)

[london735]

thanks! I checked and it work but that part of the macro that looks for the file names does not appear to be working now (as it is searching for the full text name while it should look for the clean filename, without TXT suffix)....do I change this line:

Find(what:=FName, LookAt:=xlWhole)

to

Find(what:=Left(FName, Len(FName) - 4), LookAt:=xlWhole)

Thanks again!

[SAS888]

Just so. If we write the name of the files without TXT suffix, signifies and search for it is necessary also.