Macro to Refresh and Rename .xlsx

[jjaimes83]

Hi, everybody.

First, thank you in advance for assisting me with this issue: I've created a .xlsx template to generate certain information. I used the RANDBETWEEN function in most of the cells as to create different values to them, so each time that I change a cell or refresh the sheet, everything is recalculated, which is exactly what I want to do. Now, I have to create hundreds of files based on this template, thing that I'm currently doing manually. I'm trying to find a way to automatically open the file, change the values of a column (which in consequence will change the rest of the "random" values), and save this updated file under a new extension (.csv).

So, basically, this is the process that I'm doing by hand:

1. Open template.xlsx. (has two sheets; "book1" is the one that I save, "book2" has some formulas that are used by cells in "book1").
2. Take Column A ("start time"), which has the following format: yyyy-mm-dd hh:mm:ss. Then, change the minutes by 15, for example: 2015-06-15 00:00:00 to 2015-06-15 00:15:00 (so, 4 files per each hour).
3. Save template as .csv file with the following format: NewFile_20150615-0015. (same date/time as the "start time" column, but the date as a single string and the hour as a second string).

So, any pointers/suggestions will be greatly appreciated.

 

[vaskov17]

One way would be to write a macro which triggers on workbook_open. The macro would then adjust all dates in column A by 15 minutes, save the csv file and then save the template (which I assume is needed). Then you can just use a vbs script that opens the template every 15 minutes thus triggering the workbook_open macro.

 

[jjaimes83]

One way would be to write a macro which triggers on workbook_open. The macro would then adjust all dates in column A by 15 minutes, save the csv file and then save the template (which I assume is needed). Then you can just use a vbs script that opens the template every 15 minutes thus triggering the workbook_open macro.

Thank you for your response friend, but does this mean that it will generate a new file every 15 minutes? Is there a way to, for example, run a cycle 100 times as to create 100 new files?

Makes sense to save the template, to know how to increase the values in the start time column.

 

[vaskov17]

You can set up a vbs script that opens the Excel template at whatever interval you need, so yes it can generate a new file every 15 minutes.

As far as the 100 cycles are concerned, do you need 100 new files every 15 minutes?

 

[jjaimes83]

No, actually I don't need to generate files every 15 minutes. What I want is to create as many files as I desire in a single time with the condition that the time in every file is increased by 15 minutes (and in consequence, every 4 files the hour will change, every 96 files the day will change). Let's say I make 4 files; it will represent 1 hour of data.

 

[vaskov17]

So you basically want to open the file and run a macro that creates 100 (or whatever number) of csv files at once each 15 minutes later than the previous? How often would you open the file and do this? I guess I was thrown off when you said you want to automatically open the file.

 

[vaskov17]

Here are a couple of macros that might do what you want:

Sub macro()
    Application.ScreenUpdating = False
    Dim i As Integer
    
    'second number sets how many CSV files to create
    For i = 1 To 100
        AddMinutes 15 'how many minutes to add
        SaveCSV
    Next
    
    ThisWorkbook.Save
    Application.ScreenUpdating = True
End Sub


Private Sub AddMinutes(minutes As Integer)
    Dim wks As Worksheet
    Dim LastRow As Long
    Dim c As Range
    
    Set wks = ThisWorkbook.Worksheets("Sheet1")


    LastRow = wks.Cells(wks.Rows.Count, "A").End(xlUp).Row
    
    For Each c In wks.Range("A2:A" & LastRow)
        c.Value = c.Value + 1 / 24 / (60 / minutes)
    Next
End Sub


Private Sub SaveCSV()
    Dim strFile As String
    Dim rng As Range
    Dim wks As Worksheet
    Dim newBook As Workbook
    Dim wkb As Workbook
    
    Set wkb = ThisWorkbook
    Set wks = Worksheets("Sheet1")
    Set rng = wks.Range("A2")
    
    Set newBook = Workbooks.Add
    
    wkb.Sheets("Sheet1").Copy before:=newBook.Sheets(1)
    
    strFile = "NewFile_" & Format(Year(rng.Value), "0000") & Format(Month(rng.Value), "00") & Format(Day(rng.Value), "00") & "-" & Format(Hour(rng.Value), "00") & Format(Minute(rng.Value), "00")
    
    Application.DisplayAlerts = False
    newBook.SaveAs wkb.Path & "\" & strFile & ".csv", xlCSV
    newBook.Close
    Set newBook = Nothing
    Application.DisplayAlerts = True
End Sub

The code assumes the following:
- the sheet that holds the dates is called "Sheet1"
- the dates that need updating are in column A, starting at row 2 (assumes A1 is a header)

If your sheet is named something else, change all instances of Sheet1 in the code to whatever it is.

Put this code into a module (go to VBA editor Insert -> Module) and then run the macro named 'macro'

 

[jjaimes83]

Thanks a lot for following the thread, vaskov17.

Yeah, I think maybe I didn't explain myself very well, sorry for the confusion.

The idea is to create several files based on the template, and the time in each file (and also the file name, which is based on this time) must be increased by 15 minutes. This won't be very often, maybe one day y need 200 files, and a week later I will need 50 more, and so on... Automatically open the file maybe is not the best term to describe what's intended.

Now, the simplest option is to not care about the contents of the "start_time" column and simply increase the name of the .csv file by 15 minutes (every 4 files the hour will change, and every 24 hours the date will change), as the file name is more important that the value on this column. The hardest way would be change both the "start_time" contents as well as the file name.

I will try your macros, and let you know how it goes.

Again, thanks a lot.

 

[jjaimes83]

Hi, vaskov17.

I'm running the macro and it *does* something. When I press "run" I can see that the file name in the macro header changes to "Book1" [running] and it increases each that that I run it. The data in the template changes as well, but I don't know where these books are being stored.

Is the wkb.Path (Ln 50) used to define where the new file is saved? Can I change it to the desktop, for example?

 

[jjaimes83]

Oh, wait.

I noticed that two files were created in the folder that contains the template. However, I ran the macro at least 20 times...