Help with Macro in Excel

[surplusbc]

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

 

[surplusbc]

Is there a way I could say, if the cell contains '/' then skip it?

 

[JE McGimpsey]

One way:

Public Sub QuoteCommaMacro()
Const csQQ As String = """"
Const csDELIM As String = ","
' Dim all variables.
Dim DestFile As Variant
Dim FileNum As Long
Dim rRecord As Range
Dim rField As Range
Dim sOut As String

' Prompt for destination file
DestFile = Application.InputBox( _
Prompt:="Enter the destination filename" & _
vbNewLine & "(with complete path):", _
Title:="Quote-Comma Exporter", _
Default:=CurDir & Application.PathSeparator, _
Type:=2)
If DestFile = False Then Exit Sub 'user cancelled

' Get file handle number.
FileNum = FreeFile()

'Open Output File
On Error Resume Next
Open DestFile For Output As #FileNum
If Err <> 0 Then
MsgBox "Cannot open filename " & DestFile
Exit Sub
End If
On Error GoTo 0

For Each rRecord In Selection.Rows
sOut = Empty
For Each rField In rRecord.Cells
With rField
If IsDate(.Text) Then
sOut = sOut & csDELIM & .Text
Else
sOut = sOut & csDELIM & csQQ & _
.Text & csQQ
End If
End With
Next rField
Print #FileNum, Mid(sOut, 2)
Next rRecord
Close #FileNum
End Sub

 

[Dave Peterson]

You could use instr() to search for slashes, but what happens if the cell is a
date, but isn't formatted that way ("December 3, 2004" or "12-03-2004")?

I think I'd format it the way I wanted--just to make sure.

You could do something like:

Option Explicit
Sub testme()

Dim myStr As String
Dim RowCount As Long
Dim ColumnCount As Long

For RowCount = 1 To Selection.Rows.Count
For ColumnCount = 1 To Selection.Columns.Count
With Selection.Cells(RowCount, ColumnCount)
If IsDate(.Value) Then
myStr = Format(.Value, "mm/dd/yyyy")
Else
myStr = """" & .Text & """"
End If
'Print #FileNum, mystr;
MsgBox myStr
End With
Next ColumnCount
Next RowCount

End Sub

(I used msgboxes for testing.)

 

[Tom Ogilvy]

It appears you use Isdate to determine if the value is a date (as suggested
by JE McGimpsey) - the use of slash appeared to be a suggestion by the OP as
a way to identify if it is a date or not. I only mention it as you seem to
obfuscate your answer with the formatting advice without specifically
pointing out the use of Isdate to determine if the value is a date.

 

[Dave Peterson]

Thanks for the clarification.

It appears you use Isdate to determine if the value is a date (as suggested
by JE McGimpsey) - the use of slash appeared to be a suggestion by the OP as
a way to identify if it is a date or not. I only mention it as you seem to
obfuscate your answer with the formatting advice without specifically
pointing out the use of Isdate to determine if the value is a date.

 

[surplusbc]

Thanks all... my macro is now working appropriately. Again, thank you