CODEWITHSUNDEEP
excelcsvexportexport-to-csvvba
Jack Davy
Jack Davy

Reputation: 13

Export worksheet to CSV

I want to export a worksheet to be stored as a CSV, so that we can refer back to and double check the data set. The code I have output the entire sheet in column A, I want the worksheet to output as it is in the worksheet. Can anyone help me?

It is worth noting thaat both the width and length of the table are variable, hence the counters.

Sub DataSets()

Dim shRefData As Worksheet, shInput As Worksheet
Set shRefData = ThisWorkbook.Sheets("ReferenceData")
Dim w, x, y, z As Integer
Dim filepath As String

filepath = ThisWorkbook.Path + "\"
w = 3
z = 1
x = 1

Open ThisWorkbook.Path + "\" + "Dataset" & Format(Now, "DDMMYY") & ".csv" For Output As #1

Do While shRefData.Cells(w, 1) <> ""
    w = w + 1
Loop
Do While shRefData.Cells(3, z) <> ""
    z = z + 1
Loop

For x = 1 To w
    For y = 1 To z
        Print #1, Cells(x, y)
        y = y + 1
    Next
    x = x + 1
Next

Close #1

End Sub

Upvotes: 1

Views: 222

Answers (1)

FreeMan
FreeMan

Reputation: 5687

It's been a mighty long time since I've used direct output to write to a file, but I believe the error is here:

For x = 1 To w
  For y = 1 To z
    'this line is the issue:
    Print #1, Cells(x, y)
    y = y + 1
  Next
  x = x + 1
Next

I believe that when you Print #1 it automatically puts a CRLF (Carriage Return, Line Feed) after it prints. Therefore everything is ending up in one column. To fix your existing code, I'd try this:

Dim out as string
For x = 1 To w
  out = ""
  For y = 1 To z
    'this builds a string of "value, value, value"
    out = out & Cells(x,y) & ", "
    y = y + 1
  Next
  Print #1, out
  x = x + 1
Next

However, I believe it would be much easier to do something like this*note, code off the top of my head, not fully debugged:

Sub DataSets()

  Dim shRefData As Worksheet
  Dim NewBook as Workbook
  DiM NewName as String

  Set shRefData = ThisWorkbook.Sheets("ReferenceData")
  NewName = ThisWorkbook.Path & "\" + "Dataset" & Format(Now, "DDMMYY") & ".csv"

  set NewBook = workbooks.add
  shRefData.copy Before:=NewBook.sheets(1)
  'Application.DisplayAlerts = False
  NewBook.SaveAs FileName:=NewName, FileFormat:=xlCSV
  'Application.DisplayAlerts = True
  NewBook.close
  set NewBook = Nothing

End Sub

That should save the single sheet in NewBook as a .CSV file. It will automatically remove all the extra sheets in NewBook because .CSV doesn't support multiple sheets (that's why you have to copy it Before:= Sheet1 of the new workbook.

Once you've confirmed everything is working as expected, uncomment the two Application.DisplayAlerts lines to disable any warnings Excel wants to pop up about losing the extra sheets, etc.

Upvotes: 1

Related Questions