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]

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?

Yes, you can change wkb.path to something like "c:\users\username\desktop" but if the script runs correctly you might end up with hundreds of excel files on your desktop so I'd make a folder just for that

 

[vaskov17]

Couple of things:

1. I made the macro in a brand new workbook with default sheet names, so try making a new file and put a few dates in your format in column A of Sheet1. Then run the macro in that file. It's possible that if you put the macro in your workbook, something specific is happening and it's not running correctly. Maybe you missed changing "Sheet1" to your sheet name or something else small so make sure the macro runs under the conditions I wrote it in and then we can figure out what's up.

2. In the sub 'macro' comment out both lines Application.ScreenUpdating and when you run the macro you will see the changes to your sheet as they happen.

3. In the sub SaveCSV, comment out both lines Application.DisplayAlerts and the macro will ask you if you want to save the file every single time. That usually happens when you save an Excel file to CSV, the file is saved but when you close it Excel asks if you want to save. Those two lines basically turn off that prompt when the file is closed. If you do this make sure that you put a small number in the for-next statement in 'macro', maybe less than 10. You don't want to be asked 100 times to save a file.

 

[jjaimes83]

Vaskov17: the macro is great, it's amazing what you have done!

Thanks a lot!

I was having problems earlier, but I think because of the size of the template (around 5000 rows). I tried with an smaller template (a single row), and it worked perfectly. Maybe I should leave it running during the night to have more files.

Again, thank you very much!

 

[vaskov17]

I didn't realize you were using that many cells. The issue is that with randbetween(), rand() and all volatile functions every single operation forces excel to recalculate them. So the first macro was going through cell by cell in column A and adding the minutes, which means that you get 5000 recalculations just for adding 15 minutes to column 1. Try the macro below and let me know if it's faster. The only changes are to the AddMinutes sub. What it does now is it uses PasteSpecial option to Add a value to a range of cells, for that I am using the cell AA50 to put the minutes, then copying it and pasting it to the entire range of dates in column A. If your sheet uses AA50 for something change it to an unused cell in the code.

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
    Dim rng As Range
    Dim frmt As String
    
    Set wks = ThisWorkbook.Worksheets("Sheet1")


    LastRow = wks.Cells(wks.Rows.Count, "A").End(xlUp).Row
    
    Set c = wks.Range("AA50") 'this should be an unused cell
    c.Value = (1 / 24 / (60 / minutes))
    c.Copy
    
    Set rng = wks.Range("A2:A" & LastRow)
    
    frmt = rng.NumberFormat 'store the formatting of the date range
    
    rng.PasteSpecial , xlPasteSpecialOperationAdd 'add minutes
    rng.NumberFormat = frmt 'set formatting
    c.ClearContents
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

 

[jjaimes83]

This is amazing, is creating a file a second!

I don't know how to thank you enough!