Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel - Tab delimited with Quote identifier

Posted on 2012-08-24
3
Medium Priority
?
536 Views
Last Modified: 2012-09-04
I have an excel that I need help with converting to a tab delimited with quote identifier.  When I tried to save the excel file, it is saving it text delimited, but having issue with not being able to use quote around each cell as identifier.

I know that if there's comma, then it'll put quote around it.  However, that also create another issue due to some of my cells having comma in there for like description.

Anyone has a script or something that can do similar that they can share with me?

Example:

ID          Description       Qty        Amount
122        XLS 5', 3x4         15           $2,000.00

I need it as followed:
"ID"         "Description"       "Qty"        "Amount"
"122"        "XLS 5', 3x4"         "15"           "$2,000.00"

Right now it is showing like this:
ID          Description       Qty        Amount
122        "XLS 5', 3x4"         15           "$2,000.00"
0
Comment
Question by:holemania
  • 2
3 Comments
 
LVL 39

Accepted Solution

by:
nutsch earned 2000 total points
ID: 38331562
Try this code, taken from here. Select your data, then run ExportText

Thomas

Sub ExportText()
'
' ExportText Macro
'
Dim delimiter As String
   Dim quotes As Integer
   Dim Returned As String


  delimiter = vbTab

  quotes = MsgBox("Surround Cell Information with Quotes?", vbYesNo)


' Call the WriteFile function passing the delimiter and quotes options.
      Returned = WriteFile(delimiter, quotes)

   ' Print a message box indicating if the process was completed.
      Select Case Returned
         Case "Canceled"
            MsgBox "The export operation was canceled."
         Case "Exported"
            MsgBox "The information was exported."
      End Select

   End Sub

   '-------------------------------------------------------------------

   Function WriteFile(delimiter As String, quotes As Integer) As String

   ' Dimension variables to be used in this function.
   Dim CurFile As String
   Dim SaveFileName
   Dim CellText As String
   Dim RowNum As Integer
   Dim ColNum As Integer
   Dim FNum As Integer
   Dim TotalRows As Double
   Dim TotalCols As Double


   ' Show Save As dialog box with the .TXT file name as the default.
   ' Test to see what kind of system this macro is being run on.
   If Left(Application.OperatingSystem, 3) = "Win" Then
      SaveFileName = Application.GetSaveAsFilename(CurFile, _
      "Text Delimited (*.txt), *.txt", , "Text Delimited Exporter")
   Else
       SaveFileName = Application.GetSaveAsFilename(CurFile, _
      "TEXT", , "Text Delimited Exporter")
   End If

   ' Check to see if Cancel was clicked.
      If SaveFileName = False Then
         WriteFile = "Canceled"
         Exit Function
      End If
   ' Obtain the next free file number.
      FNum = FreeFile()

   ' Open the selected file name for data output.
      Open SaveFileName For Output As #FNum

   ' Store the total number of rows and columns to variables.
      TotalRows = Selection.Rows.Count
      TotalCols = Selection.Columns.Count

   ' Loop through every cell, from left to right and top to bottom.
      For RowNum = 1 To TotalRows
         For ColNum = 1 To TotalCols
            With Selection.Cells(RowNum, ColNum)
            Dim ColWidth As Integer
            ColWidth = Application.RoundUp(.ColumnWidth, 0)
            ' Store the current cells contents to a variable.
            Select Case .HorizontalAlignment
               Case xlRight
                  CellText = Space(Abs(ColWidth - Len(.Text))) & .Text
               Case xlCenter
                  CellText = Space(Abs(ColWidth - Len(.Text)) / 2) & .Text & _
                             Space(Abs(ColWidth - Len(.Text)) / 2)
               Case Else
                  CellText = .Text & Space(Abs(ColWidth - Len(.Text)))
            End Select
            End With
   ' Write the contents to the file.
   ' With or without quotation marks around the cell information.
            Select Case quotes
               Case vbYes
                  CellText = Chr(34) & CellText & Chr(34) & delimiter
               Case vbNo
                  CellText = CellText & delimiter
            End Select
            Print #FNum, CellText;

   ' Update the status bar with the progress.
            Application.StatusBar = Format((((RowNum - 1) * TotalCols) _
               + ColNum) / (TotalRows * TotalCols), "0%") & " Completed."

   ' Loop to the next column.
         Next ColNum
   ' Add a linefeed character at the end of each row.
         If RowNum <> TotalRows Then Print #FNum, ""
   ' Loop to the next row.
      Next RowNum

   ' Close the .prn file.
      Close #FNum

   ' Reset the status bar.
      Application.StatusBar = False
      WriteFile = "Exported"
   End Function

Open in new window

0
 

Author Comment

by:holemania
ID: 38338349
Thanks.  That'll work.
0
 

Author Closing Comment

by:holemania
ID: 38365186
Thanks.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

810 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