Thanks for the pointers,
I see now then that the
was used to return to default after the previous command (~18 lines up) was
.
I also see how jumping forwards and backwards like this can get confusing, but i suppose as its a short script it may be acceptable?
I've tried to split up my post into comments and code to make it easier to read, and have taken your comments on board...
Sub Open_CSVs()
Dim FSO As Scripting.FileSystemObject
Dim FSO2 As Object
Dim MyFolder As String
Dim SourceFolder As Scripting.Folder, SubFolder As Scripting.Folder
Dim r As Long
Dim MyFile As String
Application.ScreenUpdating = False
'-JS- suspend screen updating
If Range("Source").Value = "" Then
'-JS- "source" is the range in which the root directory is specified by the user
MyFolder = GetFolder("C:\")
'-JS- If no root directory was specified, the default will be C:\
Else
MyFolder = GetFolder(Range("Source").Value)
'-JS- If root directory is specified, specification is prioritised
End If
ThisWorkbook.VBProject.VBComponents("Graph_Scaling").Export (MyFolder & "\Graph_Scaling.bas")
'-JS- Exports The rescaling module as a .bas file (to the root directory?)
Set FSO = New Scripting.FileSystemObject
'-JS- Sets abbreviation for access to file system object functions
Set SourceFolder = FSO.GetFolder(MyFolder)
'-JS- Source folder is set as the root directory (with file system object functions enabled)
For Each SubFolder In SourceFolder.SubFolders
MyFile = Dir(SubFolder.Path & "\Exports\*.csv")
'-JS- sets "MyFile" as the .csv file within each sub-sub-folder ("Exports") in the source folder.
'-JS- I think this section searches for a folder called exports and converts a .csv file to a .xslsb file,
' then it processed the file by calling another subroutine ("show bounce" which is within the previously exported module "graph_scaling"),
' then continues this process for any nyumber of folders in the root directory.
'-JS- If files DO NOT exist, then do the following
On Error GoTo Done
Set FSO2 = CreateObject("scripting.filesystemobject")
If FSO2.FileExists(SubFolder.Path & "\Exports\" & Replace(MyFile, "csv", "xlsb")) = True Then GoTo Done
'-JS- Convert .csv file to .xslb, but if both already exist, then go to "Done"
Workbooks.Open (SubFolder.Path & "\Exports\" & MyFile)
'-JS- open the .xlsb workbook
'-JS- wait till workbook has finshed opening before proceeding
Do While ActiveWorkbook.Name <> MyFile
'-JS- If files DO exist, then do the following
Application.Wait Now + TimeValue("00:00:01")
'-JS- Once the workbook is open, wait this length of time before proceeding
DoEvents
Workbooks(MyFile).Activate
'-JS- Once the workbook is open, ensure it is the active workbook
Loop
DoEvents
ActiveWorkbook.VBProject.VBComponents.Import (MyFolder & "\Graph_Scaling.bas")
'-JS- Imports the module into the active workbook, presumably so that the subroutine within it can be applied to the active workbook.
'-JS- calls and runs the sub "show bounce"
ActiveWorkbook.SaveAs SubFolder.Path & "\Exports\" & Replace(MyFile, ".csv", ""), FileFormat:=50
'-JS- saves processed file as a macro enabled .xslb file
Workbooks(Replace(MyFile, "csv", "xlsb")).Close False
Done:
On Error GoTo 0
'-JS- return to default error handling
MyFile = Dir
Loop
Next SubFolder
Kill (MyFolder & "\Graph_Scaling.bas")
Set SubFolder = Nothing
Set SourceFolder = Nothing
Set FSO = Nothing
Application.ScreenUpdating = True
'-JS- resore screen updating
Bookmarks