Excel VBA code to loop through folders, combine files, create graphs then save in a different location

I am trying to write some VBA code however I am quite new to it.
Every month I need to make 40+ reports for different sites from csv files. So far I have code to combine csv files and make one graph, however I need to make two more graphs from files that have near enough the same name only similarity is the beginning of "SimHistory".
My thinking was to get all of the csv files combine them per site, put that data into tables, then to create graphs from that and then to save them in a specified folder for these reports.
Each site has its own folder with 4+csv files and there are around 40+ sites
I am able to get one of the graphs and combine the files but I cant work out how to loop through all of the folders and achieve everything that I want. Any help or skeleton code would be greatly appreciated
Here is what I have been using so far:
Combine:
Option Explicit

Sub CombineFiles()

Dim Path As String

Dim FileName As String

Dim Wkb As Workbook

Dim WS As Worksheet



Application.EnableEvents = False

Application.ScreenUpdating = False

Path = "C:UsersSupportDesktopautomation test stuffLuncarty" 'Change as needed

FileName = Dir(Path & "*.csv", vbNormal)

Do Until FileName = ""

Set Wkb = Workbooks.Open(FileName:=Path & "" & FileName)

For Each WS In Wkb.Worksheets

WS.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

Next WS

Wkb.Close False

FileName = Dir()

Loop

Application.EnableEvents = True

Application.ScreenUpdating = True



End Sub

Client count chart sheet:
Sub ClientcountChart()

Dim Client_count As Chart

Set Client_count = Charts.Add

With Client_count

.SetSourceData Source:=Sheets("client_count (2018-06-01)").Range("B1:C28")

.ChartType = xlColumnClustered

.ChartTitle.Text = "Daily Client Count"

End With

End Sub


Most of the csv files have the same names apart from the sim history sheets in which there could be one or there could be four individual csv files.
again any help or guidance would be greatly appreciated

1 Answer
1

@Ross:

You need to use a trick based on the FileSystemObject, a powerful way for Excel to access the system folders and files.

You just declare some variables as object:

Dim oFSO, oFolder, Ofile as Object
...
Set oFSO = CreateObject("scripting.filesystemobject")
Set oFolder = oFSO.getfolder(sOrigen)


If you want to process a folder subfolders, you just do:

For each oSubFolder in oFolder.subfolders
...
Next oSubFolder


If you want to process the files a folder keeps inside, you just do:

For each oFile in oFolder.files
...
Next oFile


I hope this is aimin your effort in the proper direction
Best regards.
Antonio

In addition, you can also use the .type property of oFile to allow a mixed bag in the folder(s) and just get your CSV/Excel files from this.
– Nathan_Sav
Jun 29 at 10:11

.type

oFile

Hi thank you for this will implement it in and try and get this working :D
– Ross Clarke
Jun 29 at 10:18

With the string after the CreateObject will that be the destination folder?
– Ross Clarke
Jun 29 at 10:23

@Ross: I forgot declaring sOrigen as a string and mention it shall be the string devoted to identify the root folder you want to work with. Hope this completes the approach and you can start doing actual work...
– Antonio Rodulfo
Jun 29 at 10:39

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.