Full Version: Export From Excel To Cvs Or Txt Flat File
UtterAccess Discussion Forums > Microsoft® Office > Microsoft Excel
lrundell
May 24 2012, 04:37 PM
Hello All,

I have a piece of VBA that I wrote that does some calculations and filtering and dumps the results onto a separate worksheet in the proper format that I need it in. Works fine. Then I added some code that should copy that worksheet to a specific location on the network, so that the data can be picked up my another application. Here's the code:

Dim Export As Worksheet
Set Export = Worksheets("To Proman")
Dim PN As Range
Set PN = Export.Range("A1")

Export.SaveAs "K:\" & PN & ".txt", xlCSV, , , , False

The code works fine for exporting the the data in the correct format and correct file name, ext.

HOWEVER: The code automatically renames both the original workbook and the worksheet to my filename, which I use for many other things. I don't want this to happen, as it screws up the other VB I've written.

So, how can I export this worksheet as a CSV and not rename my current file or any of my current worksheets?


Thank you,
Levi

ipisors
May 24 2012, 04:43 PM
would something like this work for you?

Here is the function I have used in the past:
CODE
Public Sub ExportToTextFile(FName As String, _

    Sep As String, SelectionOnly As Boolean, _
    AppendData As Boolean)
Dim WholeLine As String
Dim FNum As Integer
Dim RowNdx As Long
Dim ColNdx As Integer
Dim StartRow As Long
Dim EndRow As Long
Dim StartCol As Integer
Dim EndCol As Integer
Dim CellValue As String
Application.ScreenUpdating = False
On Error GoTo EndMacro:
FNum = FreeFile
If SelectionOnly = True Then
    With Selection
        StartRow = .Cells(1).Row
        StartCol = .Cells(1).Column
        EndRow = .Cells(.Cells.Count).Row
        EndCol = .Cells(.Cells.Count).Column
    End With
Else
    With ActiveSheet.UsedRange
        StartRow = .Cells(1).Row
        StartCol = .Cells(1).Column
        EndRow = .Cells(.Cells.Count).Row
        EndCol = .Cells(.Cells.Count).Column
    End With
End If

If AppendData = True Then
    Open FName For Append Access Write As #FNum
Else
    Open FName For Output Access Write As #FNum
End If
For RowNdx = StartRow To EndRow
    WholeLine = ""
    For ColNdx = StartCol To EndCol
        If Cells(RowNdx, ColNdx).Value = "" Then
            CellValue = Chr(34) & Chr(34)
        Else
           CellValue = Cells(RowNdx, ColNdx).Text
        End If
        WholeLine = WholeLine & CellValue & Sep
    Next ColNdx
    WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep))
    Print #FNum, WholeLine
Next RowNdx

EndMacro:

On Error GoTo 0
Application.ScreenUpdating = True
Close #FNum
End Sub


I use it like this:
CODE
ExportToTextFile  "\\server\folder\filename.txt", ",", True, False

If I'm using with only the Selection (which I have to admit, it's not good practice to depend on having selected something, but it does usually work), then I Select the range first. Otherwise, don't need to.

You can play with some parameters in the function to change the delimiter to Comma if you want. I'm not keen on commas as a delimiter in general (I feel it's best practice to get in the habit of using a delimiter that's unlikely to ever occur naturally in data, like pipe symbol or tilde ~).
ipisors
May 24 2012, 04:49 PM
I just thought of something simpler, too. Why not just put the sheet in a NEW book and then SaveAs , that book (not your original source one) ?

CODE


Dim wb As Workbook
Dim Export As Worksheet
Set Export = Worksheets("To Proman")
Dim PN As Range
Set PN = Export.Range("A1")

Set wb = Application.Workbooks.Add
Export.Copy after:=wb.Sheets(wb.Sheets.Count)
wb.Worksheets("To Proman").SaveAs "K:\" & PN & ".txt", xlCSV, , , , False


lrundell
May 30 2012, 09:51 AM
Thank you, that second piece of code worked great. I appreciate your help.

ipisors
May 30 2012, 09:52 AM
Glad it worked out!
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.