Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Flat File with @ as delimiter
I need to make a flat file to send data to another organisation.
I am aware of saving Excel sheets as txt files however my problem is that the other organisation require the @ symbol to be the delimiter and ASCII 13 carriage return to show the End of Line Marker. Can anyone give me guidance on how to set the delimiter to be the @ symbol instead of a comma or space? Using Excel to do this would be ideal as I can make one sheet user friendly for our users and a hidden sheet linked to user friendly page to be used to be converted to the flat file (we currently use this method to transfer data to our bespoke databases). Any advice or guidance appreciated. Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Flat File with @ as delimiter
Hi
Chip has great VBA code for that at http://www.cpearson.com/excel/imptext.htm halfway down the page. HTH. Best wishes Harald "Natalie" skrev i melding ups.com... I need to make a flat file to send data to another organisation. I am aware of saving Excel sheets as txt files however my problem is that the other organisation require the @ symbol to be the delimiter and ASCII 13 carriage return to show the End of Line Marker. Can anyone give me guidance on how to set the delimiter to be the @ symbol instead of a comma or space? Using Excel to do this would be ideal as I can make one sheet user friendly for our users and a hidden sheet linked to user friendly page to be used to be converted to the flat file (we currently use this method to transfer data to our bespoke databases). Any advice or guidance appreciated. Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Flat File with @ as delimiter
Thanks Harald,
But the link you gave doesn't work. I will try it again in case the site is just offline for a while - but if you have another link or guidance would be great. Thanks Natalie Harald Staff wrote: Hi Chip has great VBA code for that at http://www.cpearson.com/excel/imptext.htm halfway down the page. HTH. Best wishes Harald "Natalie" skrev i melding ups.com... I need to make a flat file to send data to another organisation. I am aware of saving Excel sheets as txt files however my problem is that the other organisation require the @ symbol to be the delimiter and ASCII 13 carriage return to show the End of Line Marker. Can anyone give me guidance on how to set the delimiter to be the @ symbol instead of a comma or space? Using Excel to do this would be ideal as I can make one sheet user friendly for our users and a hidden sheet linked to user friendly page to be used to be converted to the flat file (we currently use this method to transfer data to our bespoke databases). Any advice or guidance appreciated. Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Flat File with @ as delimiter
Hi Natalie
The site seem to have some problems. Here is copy-paste of its text, it may be hard to read without the formatting: Exporting To Text Files You can also use VBA to write a procedure to export the active worksheet, or just the selected cells, to a text file. The following VBA procedure will do this. The parameters to this function are as follows: FName The file name to write the data to Sep The character with which cells will be delimited SelectionOnly If TRUE, export only the selected cells. If FALSE, export the entire worksheet. Public Sub ExportToTextFile(FName As String, _ Sep As String, SelectionOnly 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 Open FName For Output Access Write As #FNum For RowNdx = StartRow To EndRow WholeLine = "" For ColNdx = StartCol To EndCol If Cells(RowNdx, ColNdx).Value = "" Then CellValue = Chr(34) & Chr(34) Else CellValue = _ Application.WorksheetFunction.Text _ (Cells(RowNdx, ColNdx).Value, _ Cells(RowNdx, ColNdx).NumberFormat) 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 If there is a blank cell, an empty string is put in the text file. For example, if you are exporting the row with a delimiter of |, this will be in the text file as one|two|""|three|four|""|""|five|six If you want consecutive delimiters, without the empty string, change the code. Change If Cells(RowNdx, ColNdx).Value = "" Then CellValue = Chr(34) & Chr(34) Else to If Cells(RowNdx, ColNdx).Value = "" Then CellValue = "" Else With this change, the text file will contain one|two||three|four|||five|six You can call this macro from another VBA procedure as follows: ExportToTextFile "c:\temp\test.txt", ";" , FALSE Since this code has parameters, it will not appear in the standard "Macros" dialog list (ALT+F8). The following procedure will prompt you for the filename and delimiter character, and then run the ExportToTextFile procedure. Public Sub DoTheExport() Dim FName As Variant Dim Sep As String FName = Application.GetSaveAsFileName() If FName = False Then MsgBox "You didn't select a file" Exit Sub End If Sep = InputBox("Enter a single delimiter character (e.g., comma or semi-colon)", _ "Export To Text File") ExportToTextFile CStr(FName), Sep, _ MsgBox("Do You Want To Export The Entire Worksheet?", _ vbYesNo, "Export To Text File") = vbNo End Sub |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Flat File with @ as delimiter
Many thanks for your help Harald,
All sorted... thanks again :-) Natalie Harald Staff wrote: Hi Natalie The site seem to have some problems. Here is copy-paste of its text, it may be hard to read without the formatting: Exporting To Text Files You can also use VBA to write a procedure to export the active worksheet, or just the selected cells, to a text file. The following VBA procedure will do this. The parameters to this function are as follows: FName The file name to write the data to Sep The character with which cells will be delimited SelectionOnly If TRUE, export only the selected cells. If FALSE, export the entire worksheet. Public Sub ExportToTextFile(FName As String, _ Sep As String, SelectionOnly 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 Open FName For Output Access Write As #FNum For RowNdx = StartRow To EndRow WholeLine = "" For ColNdx = StartCol To EndCol If Cells(RowNdx, ColNdx).Value = "" Then CellValue = Chr(34) & Chr(34) Else CellValue = _ Application.WorksheetFunction.Text _ (Cells(RowNdx, ColNdx).Value, _ Cells(RowNdx, ColNdx).NumberFormat) 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 If there is a blank cell, an empty string is put in the text file. For example, if you are exporting the row with a delimiter of |, this will be in the text file as one|two|""|three|four|""|""|five|six If you want consecutive delimiters, without the empty string, change the code. Change If Cells(RowNdx, ColNdx).Value = "" Then CellValue = Chr(34) & Chr(34) Else to If Cells(RowNdx, ColNdx).Value = "" Then CellValue = "" Else With this change, the text file will contain one|two||three|four|||five|six You can call this macro from another VBA procedure as follows: ExportToTextFile "c:\temp\test.txt", ";" , FALSE Since this code has parameters, it will not appear in the standard "Macros" dialog list (ALT+F8). The following procedure will prompt you for the filename and delimiter character, and then run the ExportToTextFile procedure. Public Sub DoTheExport() Dim FName As Variant Dim Sep As String FName = Application.GetSaveAsFileName() If FName = False Then MsgBox "You didn't select a file" Exit Sub End If Sep = InputBox("Enter a single delimiter character (e.g., comma or semi-colon)", _ "Export To Text File") ExportToTextFile CStr(FName), Sep, _ MsgBox("Do You Want To Export The Entire Worksheet?", _ vbYesNo, "Export To Text File") = vbNo End Sub |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Flat File with @ as delimiter
Good. Thanks for the feedback.
Best wishes Harald "Natalie" skrev i melding oups.com... Many thanks for your help Harald, All sorted... thanks again :-) Natalie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Tab Delimiter File | Excel Discussion (Misc queries) | |||
Converting pivot to flat file | Excel Discussion (Misc queries) | |||
Define Excel delimiter by file extension | Excel Discussion (Misc queries) | |||
copy excell file to a CSV file and than to Notepad | Excel Discussion (Misc queries) | |||
export excel file as csv with text delimiter of " | Excel Discussion (Misc queries) |