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
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?
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.
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
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.
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
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