Hi all,
I have been set a task that involves modifying the way that a macro works for a client.. whilst I know how to do what i want to do, i do not know how to get excel to do it for me, so I will be learning VBA over the coming days
My first step is to understand what the author has written in the first place so I know what I'm working with.
I have annotated the code to try to explain what I think its saying.
would someone be willing to give up a little time to check my annotations and expand upon anything that I haven't understood or have missed? the simplicity of my annotations should be a good indication of my level; of experience in VBA (close to zero)?
This is what I have so far, I'll work on the other modules as the days pass..... The whole program takes a .csv file and converts it to a .xlsx file and organises data in a meaningful way and plots a graph from it (the graph has a zoom function too) and certain points of interest are read from the graph and reported in numbers.
There is probably a fair bit that I haven't understood, so help would be great and really appreciated!
my comments follow '-JS-
is it easier to upload my workbook?
I have been set a task that involves modifying the way that a macro works for a client.. whilst I know how to do what i want to do, i do not know how to get excel to do it for me, so I will be learning VBA over the coming days
My first step is to understand what the author has written in the first place so I know what I'm working with.
I have annotated the code to try to explain what I think its saying.
would someone be willing to give up a little time to check my annotations and expand upon anything that I haven't understood or have missed? the simplicity of my annotations should be a good indication of my level; of experience in VBA (close to zero)?
This is what I have so far, I'll work on the other modules as the days pass..... The whole program takes a .csv file and converts it to a .xlsx file and organises data in a meaningful way and plots a graph from it (the graph has a zoom function too) and certain points of interest are read from the graph and reported in numbers.
There is probably a fair bit that I haven't understood, so help would be great and really appreciated!
my comments follow '-JS-
is it easier to upload my workbook?
Code:
Option Explicit
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.
Do While MyFile <> "" '-JS- If files DO NOT exist, then do the following
On Error GoTo Done '-JS- If files DO exist, then skip to "Done" (~16 lines below)
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 is both already exist, then go to "Done"
DoEvents '-JS- awaits completion of previous task before proceeding
Workbooks.Open (SubFolder.Path & "\Exports\" & MyFile) '-JS- open the .xlsb workbook
DoEvents '-JS- wait till workbook is open before proceeding
Do While ActiveWorkbook.Name <> MyFile
Application.Wait Now + TimeValue("00:00:01") '-JS- Once the workbook is opne, 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 cal ne applied to the active workbook.
Call show_bounce '-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
DoEvents
Workbooks(Replace(MyFile, "csv", "xlsb")).Close False
Done: '-JS- If files DO exist, then do the following
On Error GoTo 0 '-JS- If an error occurs, then ignore it
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
End Sub
