Creating a csv file from anform

QuestionEdit

QUESTION: I have several worksheets that have specific cells that the user fills in, some manually typed, others from using a combo-box to populate the preset data. My question is how can I translate that data from those worksheets to a CSV file? I know it sounds redundant, since I am already using Excel, but the CSV file is being imported into another program that only accepts CSV files. I do know that the CSV file has a specific file format as far as column names and I have that information.

Thanks in advance for your help, Jeff

ANSWER: Jeff,

the easiest way is to build another file that has your data laid out as you wish

Header1 Header2 Header3 AAAA 21 East BBBB 12 West CCCC 10 North

then just save it as a CSV file. if the comboboxes are done with data validation, and your data entry form is similar to the above, then you can just save your workbook as CSV. Choose CSV in the file type when you do Save AS. Since you are doing a save As, it won't disturb the original XLS file.

If you want to do it in VBA it is just

ActiveWorkbook.SaveAs FileName:="C:\TextFiles\Myfile.CSV", FileFormat:=xlCSV ActiveWorkbook.Close SaveChanges:=False ' changes have already been saved.

Only the first sheet and data on the first sheet is saved.

Actually, CSV is a very simple text file format. It has no actual structure except that each column of data is separated by a delimiter. The default in US regional settings is a comma.

so the example data would be saved as

Header1,Header2,Header3 AAAA,21,East BBBB,12,West CCCC,10,North

If the data has an embedded comma in it such as

AAAA 23 The dog, fido, barked June

would be saved as:

AAAA,23,"The dog, fido, barked",June

The double quote is the default text delimiter.

>I do know that the CSV file has a specific file format as far as column names and I have that information.

The CSV file format itself doesn't have any special requirements for the headers in the first row of the file. In fact, they don't have to be headers. However, the application you are working with my expect headers in a certain format in the first row. If so, that is a requirement of the application.

Again, CSV is a format for data arranged like a database. You say from anForm, so unless the form is set up like a database, you might have to copy the data to another workbook where it can be organized like a database.

Does that answer the question?

---------- FOLLOW-UP ----------

QUESTION: Thanks That explains alot about how/what CSV files do/are. I think I understand now how the CSV files are layed out, I unfortunately did not lay my worksheets out that easy. I have data scattered across various cells, ex: H24, J16, B39, etc... You mentioned at the end of your reply I might have to copy that data to another file, how would I go about "grabbing" that data and organize it to fit a CSV file format?

Jeff

ANSWER: Jeff,

It was never really clear whether you wanted to use macros or this was all manual.

Personally I would use a macro and just design it to write the CSV file. If you want to pursue that, send me a sample workbook explaining what you want to do. I have the impression now that you have several sheets each being a "form" and you want certain cells of each sheet written to a CSV file - each sheet being a separate row in the CSV file.

If manual,

then in an additional sheet you would create linking formulas

Let's say I would put the sheet names in row 1 starting in A1 and the cell addresses I want from each sheet in row 2 starting in A2

then in say A4 I would put in a formula like

=Indirect(OFFSET($A$1,0,ROW()-4)&"!"&OFFSET($A$1,1,COLUMN()-1))

then drag fill it down an across to retrieve all my data. Then I would select all the cells with the formula and do Edit=>Copy, then immediately Edit=>Paste Special and select values (in xl2007, use teh copy button on the Home tab, then in the paste dropdown, select paste values).

Then delete rows 1 to 3 and then copy that sheet to a new workbook and Save it as CSV.

I have attached a picture to show the layout. I populated the cells with this much of the formula =OFFSET($A$1,0,ROW()-4)&"!"&OFFSET($A$1,1,COLUMN()-1)

so you can see how it builds the references to the specified sheets and their cells.

---------- FOLLOW-UP ----------

QUESTION: Thanks I apologize, I did not make clear that I was looking for a macro. I can however use the manual way for another workbook I am working on right now as well. I see no way to attach a file to this follow-up question. It will only let me attach an image. So I will be sending to you offline.

Your impression was right-on. I do have several sheets each being a "form" and I do want certain cells of each sheet written to a CSV file - each sheet being a separate row in the CSV file.

Jeff

AnswerEdit

Jeff,

I responded via eamail with a more detailed explanation of this demo code:

Sub ABC() Dim cell As Range, s As String, sFname As String Dim sh As Worksheet, v() As Variant i = 0 For Each sh In Worksheets

 If sh.Visible = xlSheetVisible Then
   If sh.Name <> Worksheets("Main Menu").Name Then
    s = ""
    For Each drpdwn In sh.DropDowns
      Set r = sh.Range(drpdwn.ListFillRange)
      s = s & r(drpdwn.ListIndex) & ","
    Next
    If sh.CheckBoxes(1).Value = xlOn Then
       s = s & "Yes" & ","
    ElseIf sh.CheckBoxes(2).Value = xlOn Then
       s = s & "No" & ","
    End If
    For Each cell In sh.UsedRange
      If cell.Locked = False Then
       If Len(Trim(cell.Text)) > 0 Then
         s = s & cell.Text & ","
       End If
      End If
    Next
    If Len(Trim(s)) > 0 Then
      i = i + 1
      ReDim Preserve v(1 To i)
       v(i) = Left(s, Len(s) - 1)
    End If
   End If
 End If

Next On Error Resume Next

 MkDir "C:\MyTextFiles"

On Error GoTo 0

 sFname = "C:\MyTextFiles\Output_" & Format(Now(), "yyyymmdd_hh_mm") & ".csv"
 Open sFname For Output As #1
 For i = 1 To UBound(v)
   Print #1, v(i)
 Next
 Close #1
 

End Sub