• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 41
  • Last Modified:

Export excel file - Pipe Deliminted

I've been using some VBA code that I found online to export an excel file to a pipe delimited text file. In one macro in one worksheet it works perfectly. We have another spreadsheet where we need the same exported file - however now, the same code, copied from the other file only exports the first row in excel, with pipes, however not any info below the headings row.

Here is the code:
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' ExportToTextFile
' This exports a sheet or range to a text file, using a
' user-defined separator character.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
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).Value
        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
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' END ExportTextFile
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' DoTheExport
' This prompts the user for the FileName and the separtor
' character and then calls the ExportToTextFile procedure.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub DoTheExport()
    Dim FileName As Variant
    Dim Sep As String
   
    FileName = Application.GetSaveAsFilename(InitialFileName:="J:\Transitional Coaching - Coleman\BCBS\Assessments\Ready for Upload\" & Format(Now(), "YYMMDD") & Range("C2").Text & "Assessment", FileFilter:="Text Files (*.txt),*.txt")
   
   
   
    If FileName = False Then
        ''''''''''''''''''''''''''
        ' user cancelled, get out
        ''''''''''''''''''''''''''
        Exit Sub
    End If
    Sep = "|"
    'Application.InputBox("Enter a separator character.", Type:=2)
    If Sep = vbNullString Then
    Range("B2").Select
    Sep = "|"
    End If
    'Application.InputBox("Enter a separator character.", Type:=2)
    If Sep = vbNullString Then
        ''''''''''''''''''''''''''
        ' user cancelled, get out
        ''''''''''''''''''''''''''
        Exit Sub
    End If
    Debug.Print "FileName: " & FileName, "Separator: " & Sep
    ExportToTextFile FName:=CStr(FileName), Sep:=CStr(Sep), _
       SelectionOnly:=False, AppendData:=True
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' END DoTheExport
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
0
Nick Martunas
Asked:
Nick Martunas
  • 6
  • 5
1 Solution
 
PatHartmanCommented:
I don't do Excel automation enough to be certain but if the code works for one sheet but not another, I would look at the sheet where it fails.  Is there some gap in the data that is causing the export to end prematurely?

The TransferText method will do this with one line of code.
1. Link the spreadsheet
2. Export the spreadsheet ONCE manually.  In the dialog, press the Advanced button.  That allows you to specify the delimiter and to name the export spec.
3. Now that you have an export spec created, you can use TransferText in code and as long as you include the reference to the Export spec, the export will be just ast it was when you did it manually.
0
 
Nick MartunasI.T. ManagerAuthor Commented:
I am not a programmer by any sense, so I find code online and tweak it as needed to make it work. I'm not familiar with your suggestion.
0
 
PatHartmanCommented:
Did you try to link the file and then export it manually using the wizard?  That isn't programming.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Nick MartunasI.T. ManagerAuthor Commented:
I have no export wizard, it's either save as text tab, or comma deliminated. Which is why you need to code the export, or you need to change the regional settings in windows in order to get the pipe for an export option.
0
 
PatHartmanCommented:
You WILL get the dialog.  You just have to choose text as the output.  The wizard starts and you will see a series of forms where you choose options.  Press the ADVANCED button to get to the delimiter option and a place to save the spec.
0
 
Nick MartunasI.T. ManagerAuthor Commented:
When click on file and export - there are several types of text files - NONE of which provide any dialog box. There is no other export option anywhere in Excel 2016 that I can find.
0
 
Nick MartunasI.T. ManagerAuthor Commented:
I'm closing this down - I figured out that the data being exported had #value in one field and it stopped the export for the row.
0
 
PatHartmanCommented:
As I said originally, data will do it to you.

If you don't have any wizards at all, they were probably not installed when Access was installed so to get them, you need to reinstall Access.ExportDialog.JPG
0
 
PatHartmanCommented:
Yes but the issue was data caused which I pointed out might be the problem in my initial post.
0
 
Nick MartunasI.T. ManagerAuthor Commented:
Well I get why we were smashing out heads together - this was in Excel, not Access.
0
 
PatHartmanCommented:
Ah ha.  I could see you were automating Excel but I thought you were doing it from within Access since mostly I answer Access questions.  Too bad the TransferText won't work for you:)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now