thunderhead
New Member
- Joined
- Aug 3, 2007
- Messages
- 27
I have the following VBA code to export a range of text (I managed to do it...). Instead of saving the file with a specific name, I want to throw open the "Save As..." dialog box and a provide a default file name as the active work sheet's name + "_" + "table/range name" + "current date/time" [I display the current date and time using the NOW() function in every worksheet, but not in the same cell].
EDIT: Sorry, I completely forgot about the code!
How do I do this? Thanks a ton for the help.
I just noticed: I am unable to export the headers of the table. How do I do that too?
EDIT: Sorry, I completely forgot about the code!
Code:
Sub CallExport()
'ExportRange(range,where,delimiter)
Call ExportRange(Sheet1.Range("Batsman"), _
"C:\details.txt", ",")
End Sub
Function ExportRange(WhatRange As Range, _
Where As String, Delimiter As String) As String
Dim HoldRow As Long 'test for new row variable
HoldRow = WhatRange.Row
Dim c As Range 'loop through range variable
For Each c In WhatRange
If HoldRow <> c.Row Then
'add linebreak and remove extra delimeter
ExportRange = Left(ExportRange, Len(ExportRange) - 1) _
& vbCrLf & vbCrLf & c.Text & " " & Delimiter & " "
HoldRow = c.Row
Else
ExportRange = ExportRange & c.Text & " " & Delimiter & " "
End If
Next c
'Trim extra delimiter
ExportRange = Left(ExportRange, Len(ExportRange) - 1)
'Kill the file if it already exists
If Len(Dir(Where)) > 0 Then
Kill Where
End If
Open Where For Append As #1 'write the new file
Print #1, ExportRange
Close #1
End FunctionHow do I do this? Thanks a ton for the help.
I just noticed: I am unable to export the headers of the table. How do I do that too?
