Solved

Export excel file - Pipe Deliminted

Posted on 2016-10-06
12
21 Views
Last Modified: 2016-10-09
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
Comment
Question by:Nick Martunas
  • 6
  • 5
12 Comments
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
 

Author Comment

by:Nick Martunas
Comment Utility
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
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
Did you try to link the file and then export it manually using the wizard?  That isn't programming.
0
 

Author Comment

by:Nick Martunas
Comment Utility
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
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:Nick Martunas
Comment Utility
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
 

Accepted Solution

by:
Nick Martunas earned 0 total points
Comment Utility
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
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
Yes but the issue was data caused which I pointed out might be the problem in my initial post.
0
 

Author Comment

by:Nick Martunas
Comment Utility
Well I get why we were smashing out heads together - this was in Excel, not Access.
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now