What's the easiest way to scrape residential phone numbers and names into .csv format?

Creating a csv file from anform

• 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

• Answer:

  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