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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.