Fast Export

[Kelly]

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.

 

[William]

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
(e-mail address removed)

| 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.

 

[Kirk Lingner]

William,

I'm exploring this code. I am unfamiliar with the -4167 parameter on the
end of the Workbooks.Add. What is that doing and what are some other
options?

Thanks.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!