[EASY] Code modification needed

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!

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 Function



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?
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Use GetSaveAsFilename

It's basically the same as the excel save as dialog except it doesn't save the file, it just returns the full path to the file.


Code:
MsgBox Application.GetSaveAsFilename("c:\test.txt")


I'm not sure I understand what you mean by exporting the headers.
 
Upvote 0
What I meant was: the data gets exported to the text file, but not the column headers.

Like, if I have this table:

Player Runs
John 10
Smith 2

Then, only the last two rows get exported, not the top (header) row.

I use Excel 2007 and I have formatted the range as a table.
 
Upvote 0
I don't have 2007 so I can't do any testing.


I would try using the union method to add the headers to the export range and see if that works.

Code:
set exportrange = union(headerrange,exportrange)
 
Upvote 0

Forum statistics

Threads
1,214,622
Messages
6,120,580
Members
448,972
Latest member
Shantanu2024

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top