PDA

View Full Version : Creating a new CSV file using VBA code



volabos
03-20-2010, 09:00 AM
Dear all, Here I have following VBA code :

Sub calculation()
Dim test(1 To 5, 1 To 5) As Double
Dim i, j As Integer
For i = 1 To 5
For j = 1 To 5
test(i, j) = 5
Next
Next

end sub

Here I want to save the above "test" array into a CSV file, which itself needs to be generated on the fly once the sub "calculation" is being executed. Can anyone please tell me how to do that? Your help will be highly appreciated.

Thanks

mdmackillop
03-20-2010, 09:21 AM
Simplest to write to a new sheet and save that

Sub calculation()
Dim test(1 To 5, 1 To 5) As Double
Dim i, j As Integer
Dim sh As Worksheet
Dim Pth As String

Pth = ActiveWorkbook.Path
Set sh = Sheets.Add
For i = 1 To 5
For j = 1 To 5
sh.Cells(i, j) = 5
Next
Next
sh.Move
ActiveWorkbook.SaveAs Filename:=Pth & "\Test.csv", FileFormat:=xlCSV
End Sub

volabos
03-20-2010, 10:22 AM
Thanks for this reply. This is working fine. However here "test" becomes visible and it becomes the active workbook. Is there any trick to save "test" in some invisible way and keep existing workbook as active?

lucas
03-20-2010, 10:27 AM
Try savecopyas instead of saveas

mdmackillop
03-20-2010, 10:29 AM
Sub calculation()
Dim test(1 To 5, 1 To 5) As Double
Dim i, j As Integer
Dim sh As Worksheet
Dim Pth As String

Application.ScreenUpdating = False

Pth = ActiveWorkbook.Path
Set sh = Sheets.Add
For i = 1 To 5
For j = 1 To 5
sh.Cells(i, j) = 5
Next
Next
sh.Move
With ActiveWorkbook
.SaveAs Filename:=Pth & "\Test1.csv", FileFormat:=xlCSV
.Close False
End With
Application.ScreenUpdating = True
End Sub

volabos
03-20-2010, 01:07 PM
Thanks it worked.