Thought I'd offer up some basic code for anyone who wants to or does Store and export SV's to analyse via excel. I put this together so I could combine the SV's folder using Powerpivot and analyse 000000's of rows in 1 go. Before Powerpivot couldnt recognise the data, because the SV's don't have their own header.
It took me a fair while to piece this together because I have no coding background, so I'll answer any questions if I can, but others will probably know a lot more about the code than me. It's a bit messy the way it embeds the call functions, but the code works and is quite fast (It should knock out 200 files whilst you make a coffee.)
A Few notes.
- To use the below, in the guardian Rules editor when exporting values, You need to specify export file format - each selection on it's own row.
- You'll need all three bit of code to run it the way I use it, or you can play around and do some other stuff with it.
- Read the notes in the code snippets below, I try to explain what each bit is for, so you can tailor it to suit your needs.
- Code requires some minor adjustment, as it will only reference a folder on the users system obviously.
I find this technique works better than using excel to data capture, Excel just bugs out after running for too long I find.
Anyway, just wanted to give back to the community as there's some nuggets of gold in this forum, and people seem only too willing to share. I hope this helps.
Cheers,
I use convert to XLSX so I can split the worksheet between Pre-race and In-Play. It also seems to make the files quite a bit smaller.
Code: Select all
Sub ConvertCSVToXlsx()
Dim myfile As String
Dim oldfname As String, newfname As String
Dim workfile
Dim folderName As String
Application.DisplayAlerts = False
Application.ScreenUpdating = False
' Capture name of current file
myfile = ActiveWorkbook.Name
' Set folder name to work through - Click and copy the folder address at the top of the finder window and paste into the code. DO NOT
' FORGET TO BACKSPACE THE VERY END OF THE FOLDER PATH NAME . I personally copy and paste all raw data to a new folder and run the code '
' there, in case I muck up the code in some way, I still have the raw data so I can try again. You cannot "undo" VBA.
folderName = "C:\Users\XXXXX\"
' Loop through all CSV filres in folder
workfile = Dir(folderName & "*.CSV")
Do While workfile <> ""
' Open CSV file
Workbooks.Open filename:=folderName & workfile
' Capture name of old CSV file
oldfname = ActiveWorkbook.FullName
' Convert to XLSX
newfname = folderName & Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) & ".xlsx"
ActiveWorkbook.SaveAs filename:=newfname, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWorkbook.Close
' Delete old CSV file
Kill oldfname
Windows(myfile).Activate
workfile = Dir()
Loop
' Loop Through Files will loop through and cleandata using the sub cleandata macro below.
Call LoopThroughFiles
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Code: Select all
Sub LoopThroughFiles()
Application.ScreenUpdating = False
folderName = "C:\Users\XXXX\"
If Right(folderName, 1) <> Application.PathSeparator Then folderName = folderName & Application.PathSeparator
Fname = Dir(folderName & "*.xlsx")
'loop through the files
Do While Len(Fname)
With Workbooks.Open(folderName & Fname)
' here comes the code for the operations on every file the code finds
Call Cleandata
End With
' go to the next file in the folder
Fname = Dir
Loop
Application.ScreenUpdating = True
End Sub
Code: Select all
Sub Cleandata()
'Copy and Paste Single Cell
Range("F2").Copy Range("G1")
Range("H2").Copy Range("I1")
Range("J2").Copy Range("K1")
Range("L2").Copy Range("M1")
Range("N2").Copy Range("O1")
Range("P2").Copy Range("Q1")
Range("R2").Copy Range("S1")
Range("T2").Copy Range("U1")
Range("V2").Copy Range("W1")
Range("X2").Copy Range("Y1")
Range("Z2").Copy Range("AA1")
Range("AB2").Copy Range("AC1")
Range("AD2").Copy Range("AE1")
Range("AF2").Copy Range("AG1")
Range("AH2").Copy Range("AI1")
Range("AJ2").Copy Range("AK1")
Range("AL2").Copy Range("AM1")
Range("AN2").Copy Range("AO1")
Range("AP2").Copy Range("AQ1")
Range("AR2").Copy Range("AS1")
Range("AT2").Copy Range("AU1")
Range("AV2").Copy Range("AW1")
Range("AX2").Copy Range("AY1")
Range("AZ2").Copy Range("BA1")
Range("BB2").Copy Range("BC1")
Range("BD2").Copy Range("BE1")
Range("BF2").Copy Range("BG1")
Range("BH2").Copy Range("BI1")
'Delete Columns
ActiveSheet.Range("$C:$C,$E:$E,$F:$F,$H:$H,$J:$J,$L:$L,$N:$N,$P:$P,$R:$R,$T:$T,$V:$V,$X:$X,$Z:$Z,$AB:$AB,$AD:$AD,$AF:$AF,$AH:$AH,$AJ:$AJ,$AL:$AL,$AN:$AN,$AP:$AP,$AR:$AR,$AT:$AT,$AV:$AV,$AX:$AX,$AZ:$AZ,$BB:$BB,$BD:$BD,$BF:$BF,$BH:$BH,$BJ:$BJ").EntireColumn.Delete
'Set Selection name
Range("C1").Value = "Selection"
' This Just sorts by Selection in numerical order
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort key1:=Range("C1", Range("C1").End(xlDown)), _
order1:=xlAscending, Header:=xlYes
' NOTE: The next two code snippets (Including Delete Data) was for my purposes, I wanted to sort pre-race from In-play data on different worksheets in the same file. I did this by filtering the SP Column to 0, then copying and pasting that to the Pre-Race worksheet, then deleting that from the In-Play worksheet (Which had the original data, it was just renamed), keeping all the In-play data in that worksheet. I then unfilter.
' You will need to change the Autofilter field to filter the SP Column
Sheets(1).Name = "inplay"
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(2).Name = "Pre-Race"
Sheets("inplay").Select
ActiveSheet.Range("A1").AutoFilter Field:=14????, Criteria1:=0
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("pre-race").Select
ActiveSheet.Range("A1").Select
ActiveSheet.Paste
Columns("A:Z").AutoFit
'Delete Data
Sheets("inplay").Select
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete
ActiveSheet.AutoFilterMode = False
Columns("A:Z").AutoFit
'Sort By SP - You probably don't need this but if you do - NOTE You will need to enter the correct new column to sort by, based on how many Stored Values you capture and where the SP column is
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort key1:=Range("??", Range("??").End(xlDown)), _
order1:=xlAscending, Header:=xlYes
ActiveWorkbook.Close SaveChanges:=True
End Sub