I am developing a workbook for other users to use that they will enter data into, then export the main worksheet as a CSV for importing into a database.
The columns/fields are member_id, Category Name, and Category Text. Category Name must match a very restricted list of options, so using Data Validation, I've made that a list pulled from another sheet in the workbook. The user sees that as a pull-down menu in the Category Name field.
I have a macro that saves the main worksheet as a CSV--that part works fine.
My problem is that I need the data validation list in each row for the user to use. I thought I could just paste it to about 50 rows. But if I export the CSV leaving some rows blank, it still exports those blank rows, which causes confusion for the database I'm importing the CSV to. It looks like this:
Those blank entries ("") cause trouble for my import. The import tool (which I can't modify) sees those as invalid entries, and won't take my import.
"member_id","Category Name","Category Text"
"73","Category 1","Test text, it goes here."
I need a way to have a user enter a row of data, and when they go to the next row, it creates the data validation list for that row on the fly. Or if there is a way to clear those out of the CSV as part of the macro that would work also. Hope that's clear. I'd be happy to clarify if needed. Thanks for any help!
Here is the macro I'm using for the CSV export:
Dim TheFileSaveName As String, vFileNum As Integer, qcq As String, tempStr As String
Dim i As Long, j As Integer
TheFileSaveName = Application.GetSaveAsFilename(initialfilename:="NotesExport_" & Format(Date, "mmddyyyy") & "_" & Format(Time, "hh-mm-ss"), filefilter:= _
"CSV (Comma delimited) (*.csv), *.csv", Title:="Please enter filename to export to")
If TheFileSaveName = "False" Then End
vFileNum = FreeFile()
On Error Resume Next
Open TheFileSaveName For Output As #vFileNum
If Err <> 0 Then MsgBox "Cannot save to filename " & TheFileSaveName: End
On Error Goto 0
qcq = Chr(34) & Chr(44) & Chr(34)
For i = 1 To [a1].SpecialCells(xlLastCell).Row
For j = 1 To Cells(i, 256).End(xlToLeft).Column
If j = 1 Then tempStr = Cells(i, j).Text Else tempStr = tempStr & qcq & Cells(i, j).Text
Print #vFileNum, Chr(34) & tempStr & Chr(34)