Hi Kelly
I'm sure there are faster ways, but....
Sub Test2()
Dim wb As Workbook, wb1 As Workbook
Dim ws As Worksheet, ws1 As Worksheet, r As Range
Application.ScreenUpdating = False
Application.Calculation = xlManual
Application.EnableEvents = False
Application.DisplayAlerts = False
Set wb = ThisWorkbook
Set ws = wb.Sheets("Sheet1")
Set r = ws.Range("A2:Z50000")
Workbooks.Add -4167
Set wb1 = ActiveWorkbook
Set ws1 = wb1.ActiveSheet
ws1.Range("A2:Z50000").Formula = r.Value
wb1.SaveAs Filename:=ThisWorkbook.Path & "\MyFile.csv"
wb1.Close
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
Application.EnableEvents = True
Application.DisplayAlerts = True
End Sub
--
XL2002
Regards
William
"Kelly" wrote in message
om...
| I'm addressing the age-old question of exporting data from Excel to
| (fill in the blank).
|
| I need speed (dealing with a large amount of data many times over)
|
| The goal of the Excel spreadsheet I've written is to take a range and
| save it in a file (.txt, .csv, .whatever) so that it can be read in
| later by another Excel spreadsheet.
|
| What I know so far is that if I (programatically via VBA) cut the
| range, open a new workbook, paste the values and save the workbook,
| this seems ? to be the fastest. However, I'm told this is not best
| practices.
|
| I've also tried looping through the range and assigning the values to
| an array then write the array out. Takes too long.
|
| Does anyone know the most efficient way to capture a range and save it
| to a file?
|
| All ideas are welcome. Thanks a ton.