Procedure to long Excel

[DocCon]

I am very new a Macros and VB. I am trying to export data to a file to be imported to a new Template. I am using the below Macro that works fine but with the 64k limit I can't export all the data that i need to. Is there a way to steamline the macro? Any suggestion would be helpfull. Thanks DeWight


Sub ExportToFile(ByVal strWorksheet As String)
Dim strFN As String
Dim fso As New Scripting.FileSystemObject
Dim txtFile As Variant
Dim strData As String

On Error GoTo ErrHandler

Application.Cursor = xlDefault

Select Case strWorksheet

Case wsFIN_STMT

MsgBox "Cannot Export Financial Statement!", vbCritical + vbOKOnly, "Test"
Exit Sub
'strData = wsFIN_STMT & vbNewLine

Case wsCash_Flow

MsgBox "Cannot Export Cash Flow!", vbCritical + vbOKOnly, "test"
Exit Sub
'strData = wsCash_Flow & vbNewLine

Case wsSCORE

MsgBox "Cannot Export SCORE!", vbCritical + vbOKOnly, "Test"
Exit Sub
'strData = wsSCORE & vbNewLine


Case wsFS_AG: 'strData = wsFS_AG & vbNewLine

Case wsSchedC: 'strData = wsSchedC & vbNewLine

Case wsSchedE: 'strData = wsSchedE & vbNewLine

Case wsSchedF: 'strData = wsSchedF & vbNewLine

Case ws1040: 'strData = ws1040 & vbNewLine

Case "All"


Case Else

MsgBox "Cannot Export " & strWorksheet & "!", vbInformation + vbOKOnly, "Test"
Exit Sub

End Select

If Not FileDLG.FileSaveDLG(Application.hwnd, strFN, "Save " & strWorksheet & " export file as...", enmTest) Then

MsgBox "Export canceled by user", vbExclamation + vbOKOnly, "Test"
Exit Sub

End If

'put some header information at the top of export file.
'-------------------------------------------------------
strData = strData & wsFIN_STMT & pipe & "o3" & pipe & Sheets(wsFIN_STMT).Range("o3") & vbNewLine 'market
strData = strData & wsFIN_STMT & pipe & "o4" & pipe & Sheets(wsFIN_STMT).Range("o4") & vbNewLine 'market
strData = strData & wsFIN_STMT & pipe & "o5" & pipe & Sheets(wsFIN_STMT).Range("o5") & vbNewLine 'market
strData = strData & wsFIN_STMT & pipe & "o6" & pipe & Sheets(wsFIN_STMT).Range("o6") & vbNewLine 'market
strData = strData & wsFIN_STMT & pipe & "o7" & pipe & Sheets(wsFIN_STMT).Range("o7") & vbNewLine 'market
strData = strData & wsFIN_STMT & pipe & "o8" & pipe & Sheets(wsFIN_STMT).Range("o8") & vbNewLine 'market
strData = strData & wsFIN_STMT & pipe & "o11" & pipe & Sheets(wsFIN_STMT).Range("o11") & vbNewLine 'market
strData = strData & wsFIN_STMT & pipe & "o12" & pipe & Sheets(wsFIN_STMT).Range("o12") & vbNewLine 'market
strData = strData & wsFIN_STMT & pipe & "o13" & pipe & Sheets(wsFIN_STMT).Range("o13") & vbNewLine 'market

'Select which spreadsheet to export.
'The strData variable will contain
'all data to be exported to file.
'-----------------------------------
Select Case strWorksheet


Case "All"

If Not bExport.expFSAG(strData) Then

End If
If Not bExport.expSchedC(strData) Then

End If
If Not bExport.expSchedE(strData) Then

End If
If Not bExport.expSchedF(strData) Then

End If
If Not bExport.exp1040(strData) Then

End If


Case wsFIN_STMT

If Not bExport.expFS(strData) Then

End If

Case wsCash_Flow

If Not bExport.expCF(strData) Then

End If

Case wsFS_AG

If Not bExport.expFSAG(strData) Then

End If

Case wsSchedC

If Not bExport.expSchedC(strData) Then

End If

Case wsSchedE

If Not bExport.expSchedE(strData) Then

End If

Case wsSchedF

If Not bExport.expSchedF(strData) Then

End If

Case ws1040

If Not bExport.exp1040(strData) Then

End If

Case wsSCORE

If Not bExport.expSCORE(strData) Then

End If


End Select

'Create, write & close the export file.
'-------------------------------
Set txtFile = fso.CreateTextFile(strFN, True)
txtFile.Write strData
txtFile.Close
Set fso = Nothing

Exit Sub

ExitThisSub:
Exit Sub

ErrHandler:
Application.Cursor = xlDefault

MsgBox "Error: " & Err.Number & vbNewLine & "Description: " & _
Err.Description & vbNewLine & "Source: ExportToFile()"
Err.Clear
Resume ExitThisSub

End Sub