Writing CSV files

Tags
Multiple choice

Which code is best, when reading a CSV file that has column headers in the first row?

Saving
A
  • Open ThisWorkbook.Path & "\super-models.csv" For Input As #1
  • Line Input #1, tTempLine
  • Input #1, tFirstName, tLastName, sTongueWidth, sNostrilHairDensity
  • Close #1
B
  • Open Workbook.Path & "\super-models.csv" For Input As #11
  • Line Input #1, tTempLine
  • Input #1, tFirstName, tLastName, sTongueWidth, sNostrilHairDensity
  • Close #1
C
  • Open ThisWorkbook & Path & "\super-models.csv" For Input As #1
  • Input #1, tTempLine
  • Line Input #1, tFirstName, tLastName, sTongueWidth, sNostrilHairDensity
  • Close #1
D
  • Open ThisWorkbook.Path & "super-models.csv" For Input As 1
  • Line Input 1, tTempLine
  • Input 1, tFirstName, tLastName, sTongueWidth, sNostrilHairDensity
  • Close 1
E
  • Open ThisWorkbook.Path & "\super-models.csv" For File Input As #1
  • Line Input #1, tTempLine
  • Input #1, tFirstName, tLastName, sTongueWidth, sNostrilHairDensity
  • Close File #1
F
  • Open ThisWorkbook.Path & "\super-models.csv" For Input As #1
  • Line Input #1, tTempLine
  • Input #1, tFirstName, tLastName, sTongueWidth, sNostrilHairDensity
G
  • Open This.Workbook & Path . "\super-models.csv" For Input As #1
  • Line Input #1, tTempLine
  • Input #1, tFirstName, tLastName, sTongueWidth, sNostrilHairDensity
  • Close #1
H
  • Open ThisWorkbook.Path & "\super-models.csv" For Input As #1
  • Line Input #1, tTempLine
  • Input #1, tFirstName tLastName sTongueWidth sNostrilHairDensity
  • Close #1
I
  • Open ThisWorkbook.Path & "\super-models.csv" For Output As #1
  • Line Output #1, tTempLine
  • Output #1, tFirstName, tLastName, sTongueWidth, sNostrilHairDensity
  • Close #1

Lesson contents

Opening and closing files

Much the same as before.

  • Open ThisWorkbook.Path & "\things.csv" For Output As #1
  • ...
  • Close #1

If things.csv doesn't exist, then VBA will create it. If it already exists, then VBA will erase the file, and make a new empty one.

We're doing output so this is in the O part of an IPO program.

Writing variables

Say you have the variables tProductName, a string, and sPrice, a single. Output them to a CSV file like this:

  • Write #1, tProductName, sPrice

If tProductName was Goat t-shirt, and price was 17.95, you'd get:

  • "Goat t-shirt",17.95

The Write statement puts quotes around strings for you. M0000t!

Exercise

Exercise

Aussie rules with CSV output

Repeat the Aussie rules exercise, but with output to a CSV file. The worksheet starts like this:

Start

Your program should:

  • Compute the score and show it on the worksheet.
  • Output to a CSV file with the name the user types in the worksheet.
  • Show when the operation is complete.

Here's some output:

Output

Here, your program would make a file called rules.csv, with this:

  • 5,8,38

Notice that your program sends output to two places: the worksheet, and a file.

Here's another example. Suppose the user put this data in:

Another start screen

They click Run, and see:

Output

The program would create moose.csv. Its contents:

  • 8,1,49

Upload your worksheet.