VBA to gather data

[levy77]

Hi,

I have a folder of CSV files downloaded from yahoo finance and I have written code to get data from each of them. It took a long time and is a lengthy process if I want to add another 5-10 files. The code is below. The first block is some preliminary set up then the 2nd, 3rd and 4th are gathering data. I have about 10 CSV files I'm doing this for. Is there a way to make this automation even more automated. It would be good if I could just drop a CSV file into the folder, ran the macro and Excel picked up there was a new file in there and got the data from it.

Thanks for any help

Dim xjo As Workbook
Set xjo = Workbooks.Open("C:\Users\Username\Desktop\Asset Allocation\Stock Data\Monthly\^AXJO.csv", True, True)
Dim lrow As Long
lrow = Cells(Rows.Count, 1).End(xlUp).Row
ThisWorkbook.Activate
Worksheets("Sheet1").Range("A1:A" & lrow).Formula = xjo.Worksheets("^AXJO").Range("A1:A" & lrow).Formula
Range("A1:A" & lrow).NumberFormat = "dd/mm/yyyy"
Worksheets("Sheet1").Range("B2:B" & lrow).Formula = xjo.Worksheets("^AXJO").Range("F2:F" & lrow).Formula
Range("B1").Value = "XJO"
Workbooks("^AXJO").Close


Dim bboz As Workbook
Set bboz = Workbooks.Open("C:\Users\Username\Desktop\Asset Allocation\Stock Data\Monthly\BBOZ.AX.csv", True, True)
ThisWorkbook.Activate
Worksheets("Sheet1").Range("C2:C" & lrow).Formula = bboz.Worksheets("BBOZ.AX").Range("F2:F" & lrow).Formula
Range("C1").Value = "BBOZ"
Workbooks("BBOZ.AX").Close

Dim gear As Workbook
Set gear = Workbooks.Open("C:\Users\Username\Desktop\Asset Allocation\Stock Data\Monthly\GEAR.AX.csv", True, True)
ThisWorkbook.Activate
Worksheets("Sheet1").Range("D2:D" & lrow).Formula = gear.Worksheets("GEAR.AX").Range("F2:F" & lrow).Formula
Range("D1").Value = "GEAR"
Workbooks("GEAR.AX").Close

Dim csl As Workbook
Set csl = Workbooks.Open("C:\Users\Username\Desktop\Asset Allocation\Stock Data\Monthly\CSL.AX.csv", True, True)
ThisWorkbook.Activate
Worksheets("Sheet1").Range("E2:E" & lrow).Formula = csl.Worksheets("CSL.AX").Range("F2:F" & lrow).Formula
Range("E1").Value = "CSL"
Workbooks("CSL.AX").Close

 

[Rijnsent]

Hi Levy,
something like this might do the trick (untested):

Sub LoopThroughFiles()
    Dim xjo As Workbook
    Dim StrFile As String
    Dim lrow As Long
    MyDir = "C:\Users\Username\Desktop\Asset Allocation\Stock Data\Monthly\"
    StrFile = Dir(MyDir & "*.csv")
    Set wb = ActiveWorkbook
    Set ResultSht = wb.Worksheets("Sheet1")
    FileNr = 1
   
    Do While Len(StrFile) > 0
        Debug.Print MyDir & StrFile
       
        Set xjo = Workbooks.Open(MyDir & StrFile, True, True)
        lrow = ResultSht.Cells(Rows.Count, 1).End(xlUp).Row
        ResultSht.Range("A1:A" & lrow).Formula = xjo.Worksheets(1).Range("A1:A" & lrow).Formula
        ResultSht.Range("A1:A" & lrow).NumberFormat = "dd/mm/yyyy"
        ResultSht.Range("A2:A" & lrow).Offset(0, FileNr).Formula = xjo.Worksheets(1).Range("F2:F" & lrow).Formula
        ResultSht.Range("A1").Offset(0, FileNr).Value = StrFile
        xjo.Close
        FileNr = FileNr + 1
        StrFile = Dir
    Loop
End Sub

Cheers,
Koen