Help with Macro in Excel
 

Hi Everybody,

I have a bit of a problem with the below Macro.
This macro basically takes a selected area in a spreadsheet and creates
a text file. It adds quotes around each cell value and separates cells
within a row with commas. Each row has it's own line in the txt
format. So basically, click on any cell with a value, hit CTRL+Shft+*
to highlight the whole area, and then run the macro. You will be asked
to put a file name and path such as c:\mytextfiles\textfile.txt

As soon as you run the macro, a txt file will appear. It will show
something like:

"a","b","c","d"
"1","2","3","4"

Finally, my question. What I want to do is, if any value is in the
format of a date, I don't want quotes around it. Any ideas would be
appreciated.


-------------------------------------------
Sub QuoteCommaMacro()
' Dim all variables.
Dim DestFile As String
Dim FileNum As Integer
Dim ColumnCount As Integer
Dim RowCount As Integer

' Prompt for destination file
DestFile = InputBox("Enter the destination filename" _
& Chr(10) & "(with complete path):", "Quote-Comma Exporter")

' Get file handle number.
FileNum = FreeFile()

'Turn off error handling
On Error Resume Next

'Open Output File
Open DestFile For Output As #FileNum

'If err - report and end
If Err < 0 Then
MsgBox "Cannot open filename " & DestFile
End
End If

' Turn on error Handling
On Error GoTo 0

' Loop for each row
For RowCount = 1 To Selection.Rows.Count

' Look for each column
For ColumnCount = 1 To Selection.Columns.Count

' Write cell text to file with " marks
Print #FileNum, """" & Selection.Cells(RowCount, _
ColumnCount).Text & """";

' Is last column?
If ColumnCount = Selection.Columns.Count Then
' then write a blank line.
Print #FileNum,
Else
' Else write a comma.
Print #FileNum, ",";
End If
' Next column loop...
Next ColumnCount
' Next row loop...
Next RowCount
' Close output file and end
Close #FileNum
End Sub


---
Message posted from http://www.ExcelForum.com/

Help with Macro in Excel
 

Here is a cut down version of your code.
Declaring MyValue As String ensures that Excel does the necessar
conversions automatically.
You may wish to change the date format check etc. to something mor
appropriate.

'---------------------------------------------------
Sub ADD_TO_TEXTFILE2()
Dim FileNum As Integer
Dim MyFormat As String
Dim MyValue As String ' ensures text conversion
Dim ColumnCount As Integer
Dim RowCount As Long
Dim NumRows As Long
Dim NumCols As Long
'--------------------------
FileNum = FreeFile()
Open "C:\test.txt" For Append As #FileNum
'-----------------------------------
NumRows = Selection.Rows.Count
NumCols = Selection.Columns.Count
For RowCount = 1 To NumRows
For ColumnCount = 1 To NumCols
MyValue = _
Selection.Cells(RowCount, ColumnCount)
MyFormat = _
Selection.Cells(RowCount, ColumnCount).NumberFormat
'- check for date format
If InStr(1, MyFormat, "-") = 0 _
And InStr(1, MyFormat, "/") = 0 Then
MyValue = """" & MyValue & """"
End If
Print #FileNum, MyValue;
If ColumnCount < NumCols Then
Print #FileNum, ",";
End If
Next
Print #FileNum,
Next
'----------------------------------
Close #FileNum
End Sub
'-- eop ---------------------------------------

--
Message posted from http://www.ExcelForum.com