Code to edit data and to save as a text file.

[jordanwebb10]

Hello everyone!

I always hate having my first post being a request for help but I just wanted to thank anyone in advance.

I am a sports scientist with a professional team here in the states. We recently purchased some hardware that allow us to take resting heart rate readings and calculate fatigue. I now will be working within excel to a large extend and decided I wanted to learn basic VBA programming to automate some processes. I have been using the record Macro function and then editing different pieces together to help myself learn, but there are some things I can not do. While I have every intention of trying to improve my skills a current project deadline is approaching faster then I can learn VBA!! I was wondering if someone could help with some coding in the intermediate.

We have collected over 200 of the sample files (RtoR Zephyr Data) and will have about 10 more per day that I will need to process and analyze in other software. The problem is that this hardware gives me values that need to be converted into a different format so that the analysis software can read it.



What I would like to do is this.

1. Delete Column A and B (useless data)
2. As you can see in column C I have values that repeat themselves (time periods between heartbeats gathered for every epoch period). I only need one absolute value of each repeated series (or in other words just the time between each beat)put into column D. I have been using this formula in column D after creating a table in column A to get the correct values, =if(c2<>c1,abs(c2),””)*1000 (The *1000 is so I can get the values in milliseconds versus a percentage of a second)
3. Filter out the bad data
4. on a different sheet I would then enter the following information (it is a header necessary for the analysis software to take the file) [Params]
   SMode=000000000
   Date=00000000
   StartTime=00:00:00.0
   Length=00:00:00.0
   Interval=238
   
   [HRData]
5. I would then go back to the main page and select the HR values copy them and then paste them directly under the cell containing [HRData]
6. Finally I would then save that page as a .text file with the original file name and the extension .hrm

It will take me forever! to do this to 200+ files.



If anyone would be kind enough to help me develop a VBA code that could help me convert my data it would be greatly appreciated.



Would it even be possible to create some code that could batch process the files together?



Also if anyone has any suggestions on resources to help educate myself on VBA I would really appreciate that as well.



Jordan Webb-

[jordanwebb10]

Forgot one thing. The length of tests will be different for each file. I am not sure if that makes a difference but I thought it was worth mentioning.

[Kenneth Hobs]

Welcome to the forum!

That is fairly simple but if you would mark up a very short example for the extra sheet for step 4, that would help. I am not sure about [Params] and [HRData] but I gather that the [HRData] is the clean data from column D with no duplicates.

Do you need a prompt to navigate to the folder with the 200 files or would hard coding the path suffice? Is it all of the files in that folder or just certain ones?

[jordanwebb10]

kenneth,

Thank you for your reply. Here is what the final file would need to look like. I hope that clears up the confusion.

[jordanwebb10]

I guess it wont let me upload a .hrm file.

here is the final excel file before saving it as a text .hrm file.

[jordanwebb10]

I could set it up where it would be all of the files in a certain folder. Then after conversion the files could be saved somewhere else? Would that work.

[Kenneth Hobs]

There has to be a logical pattern to name the files and put them in another folder.

Interval=238 will be the same for all files?

[jordanwebb10]

What ever you feel would be the easiest way to save the files.

The interval will always be 238.

[Kenneth Hobs]

I could have used ADO to make this faster but that makes the code seemingly more complicated and other problems can crop up.

I used Name to rename the XLSX file after processing. As always, test on backups of your files.

Insert this into a Module.

Sub CreateHRMFiles()
  Dim oldpath As String, newpath As String
  Dim basename As String
  Dim fso As Object, fn As Object
  Dim wb As Workbook, cell As Range
  Dim hFile As Integer, hrmFile As String
  Dim s As String
oldpath = ThisWorkbook.Path & "\"
  newpath = ThisWorkbook.Path & "\"
Set fso = CreateObject("Scripting.FileSystemObject")
  For Each fn In fso.GetFolder(oldpath).Files
    s = ""
    basename = fso.GetBaseName(fn)
    If fn = ThisWorkbook.FullName Or Right(basename, 3) = "_RR" Or GetAttr(fn) = 8226 Then GoTo NextFN
    Set wb = Workbooks.Open(fn)
    hFile = FreeFile
    hrmFile = newpath & basename & ".hrm"
    Open hrmFile For Output As #hFile
    Print #hFile, "[Params]"
    Print #hFile, "SMode = 0"
    Print #hFile, "Date = 0"
    Print #hFile, "StartTime=00:00:00.0"
    Print #hFile, "Length=00:00:00.0"
    Print #hFile, "Interval = 238"
    Print #hFile, ""
    Print #hFile, "[HRData]"
    For Each cell In wb.ActiveSheet.Range("C2", wb.ActiveSheet.Range("C" & Rows.Count).End(xlUp))
        If Abs(cell.Value) <> s Then
            s = Abs(cell.Value)
            Print #hFile, s
        End If
    Next cell
    Close hFile
    wb.Close False
    DoEvents
    Name fn As (newpath & basename & "_RR." & fso.GetExtensionName(fn))
Next fn
End Sub

[jordanwebb10]

Kenneth,

I might be doing something wrong. I have entered the code into a module and I ran the macro on a file nothing happened.

Am I missing something?

[Kenneth Hobs]

1. Were the files to process in the folder of the workbook with the macro that you ran?
2. Did it not rename the Excel files with a suffix of "_RR" for the basename?
3. Did it not create the HRM files?

Note that I did not edit the Excel files. I just opened them to read them.

Step through the code using F8 if needed.

[jordanwebb10]

I got everything to work with one exception.

Where can I add a piece of code that will multiply the HR values by 1000? The HR values need to be milliseconds instead of a fraction of a second.

[Kenneth Hobs]

Change the Print line.

Print #hFile, (s*1000)

[jordanwebb10]

Kenneth,


I got everything working great. I just wanted to thank you for helping me out. Thanks again.