Save file as .csv or .txt file??

[largeselection]

Hello,

I was wondering if you knew a way I could save an excel file as a .csv or .txt file? Unfortunately, my excel file is on multiple tabs (125,000 lines).

I'm ultimately trying to save this multiple tab file as a .csv or .txt so I can create an OLAP cube so that I can create a pivot table with all 125,000 lines.

Any help would be appreciated.

 

[BrianB]

Try my code here. Might be easier to use comma as delimiter.
http://www.mrexcel.com/forum/showthread.php?t=271856

If you use the same file name each time .. if the file does not exist it will be made when the code is first run. If you run the code on another sheet the contents will be appended to the bottom of the existing file.

You may need to deal with worksheet header rows which will be duplicated.
might as well delete the top row and put it back later ?

 

[alansidman]

As an alterntive, you could export or link the file to Access and create a Pivot table in Access. If you link it, then any changes to the excel spreadsheet would update in access.

Alan

 

[jindon]

try

Sub test()
Dim ws As Worksheet, i As Long, txt As String, delim As String
delim = ","   '<- delimitter
For Each ws In Sheets
    With ws.Range("a1").CurrentRegion
        If .Columns.Count > 1 Then
            For i = 1 To .Rows.Count
                txt = txt & vbCrLf & _
                Join$(Evaluate("tranapose(transpose(" & .Rows(i).Address & "))"), delim)
            Next
        Else
            txt = txt & vbCrLf & Join$(Evaluate("transpose(" & .address & ")"), vbCrLf)
        End If
    End With
Next
Open Replace(ThisWorkbook.FullName, "xls", "csv") For Output As #1
    Print #1, Mid$(txt, Len(vbCrLf) + 1)
Close #1
End Sub