Writing simulations as .csv files

**lukegerd** · 01-29-2013, 02:43 PM

I've used excel to build a simulator. I plan to analyze the results of this simulation using an outside program that requires data to be formatted as .csv files. I want to use VBA code to automatically generate these files, so that I don't have to manually process hundreds of files.

My simulation makes use of the rand() function, so the possible output values change (as they should) every time you run the simulation. I'd like to be able to tweak a parameter in VBA to set the number of iterations. Put differently, I want to tell excel "run the simulation 200 times." I then want VBA to write the output cells (M3:R13) to 200 different .csv files, each with a unique file name. Ideally, the VBA code should write the .csv files to the same folder where I have saved the simulator.

If it matters, cell M3 is blank; cells N3:R3 contain column names, and cells M4:M13 contain row names. Cells N4:R13 contain numeric values. The program that I'm using for subsequent analysis requires both row and column headers, so it's important that the .csv files contain everything that appears in M3:R13.

If the problem that I've outlined isn't clear, please ping me with questions. I'll be more than happy to answer them. And I hope that it goes without saying, but I'll greatly appreciate any help that the forum members can offer. Thanks for your time and attention.

**Alf** · 01-29-2013, 03:27 PM

Prehaps a macro along thes lines?

Option Explicit

Sub WritePDF()
Dim i As Integer

For i = 1 To 200 ' This will loop

' Your macro to update range M3:R13 goes here

Range("M3:R13").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Temp\" & "Result" & i & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True _
        , IgnorePrintAreas:=False, OpenAfterPublish:=False
        
 ' or you can put it here
 
 Next i


End Sub

Filename will go from "Result1.pdf" to "Result200.pdf" where files will be saved in folder "C:\Temp\" this you have to change to fit your needs.

Works with Excel 2007 but not quite sure it works with Excel 2010 so you better test.

Alf

**lukegerd** · 01-31-2013, 08:19 PM

Thanks for the attempt, but it doesn't work at all. I'm looking for something that creates csv files, not pdfs. I tried simply changing the file type specified in the line of code that begins with the file reference (C:"\Temp\"), but it didn't work. I also tried running the macro as currently written, in order to see if it would even generate pdfs. I didn't have any luck.

Ultimately, I don't have enough knowledge of VBA to make any other attempts at modification. Does anyone else have any suggestions?

**Alf** · 02-01-2013, 03:09 AM

I'm looking for something that creates csv files, not pdfs.

Sorry my bad perhaps this macro could be of help?

Macro creates a csv file (comma delimetered) using the active sheet name as the file name.

Option Explicit

Sub SaveAsCsv()

ActiveSheet.Copy
ActiveWorkbook.SaveAs Filename:="C:\Temp\" & ActiveSheet.Name, FileFormat:=xlCSVWindows
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
End Sub