Remove Inverted Commas In PIPE Delimited File Created From VBA

Jun 3, 2014

i have a Macro, its creating unwanted inverted commas at the start and end of the File.

File:

"SunilManual|123456790|DrivingLicence|908567543|Sunil|Manchan
|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w
|w|w|w|w|w|w|w|w|w|w|w|ww|w|w|w|w|w|w|w|w|w|w
|w|w|w|w|w|w|w|w|w|w|55|55||4|SR-1161||1234567890"

Expected File:

|SunilManual|123456790|DrivingLicence|908567543|Sunil|Manchan
|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w
|w|w|w|w|w|w|w|w|w|w|w|ww|w|w|w|w|w|w|w|w|w|w
|w|w|w|w|w|w|w|w|w|w|55|55||4|SR-1161||1234567890|

VBA:

Option Explicit
Sub txtt_export()
Dim buf As String, col As Long, r As Long, ws As Worksheet

[Code]....

View 2 Replies

Convert XLS To Pipe-delimited TXT File

Jul 15, 2009

I have the following code (borrowed) which converts the current .xls worksheet to a tab-delimited .txt file. The problem is that i need to add a PIPE to the end of each row/record as well, so that the records would look something like this:

A|123|
B|456|

currently there is no PIPE following the last character (3 or 6) and i am getting this:

A|123
B|456

I was hoping there would be a way to revise the VBA to add a PIPE at the end of each row/record.

Here's the code:

[Code] ......

View 10 Replies View Related

Convert .xls To Pipe-delimited .txt File

Jul 15, 2009

View 4 Replies View Related

Macro To Copy Data From Excel And Creates Pipe Delimited Text File

May 27, 2014

Macro to copy data from Excel sheet and creates a Pipe delimited text file.

View 10 Replies View Related

Tab Delimited File Saves Commas As Dot

Jan 30, 2008

I'm using Excel 2000 swedish version, that is we use comma to seperate decimals not dot. I have a macro that saves a sheet as .txt file (tab delimited). However in the sheet (journal voucher) I need to enter a couple of numbers all with 2 decimal, that is 5 is 5,00 and so on. But then, when I save the sheet to a .txt file using a macro all the commas are saved as dots and since I'm importing the file to SAP it will not accept the dots. Is there some way ( changes in macro) I can prevent the commas from becoming dots when I save the .txt file?

The save macro is the commomly used

ActiveWorkbook.SaveAs Filename:= _
"C:Documents and Settings& strText & ".txt", _
FileFormat:=xlText, CreateBackup:=False
ActiveWorkbook.Close
ThisWorkbook.Activate

View 4 Replies View Related

Replace Or Substitute Inverted Commas With Nothing

Jun 17, 2006

I have a problem to achieve the above subject, as per the attached picture.

View 2 Replies View Related

Inverted Commas & Range References-vba

Apr 30, 2007

I'm trying to combine 2 variable column refs and 2 fixed numbers to select a range in VBA, but I can't get the correct syntax re the number of inverted commas.

Range("" & strFrom & "4"":" & strTo & "7""").Select

(strFrom and strTo are the column variables, already defined)

I've tried every combination (except the right one!) for over an hour and just cannot get this to work.

If someone can also explain the logic around the number/placement of inverted commas it would be much appreciated - since they're all straight, I'm never sure whether I'm closing a set or opening a new set.

View 3 Replies View Related

Exporting To Pipe Delimited

Dec 27, 2007

Figured out how to export to Pipe Delimited through coding but now I am faced with 2 complications.

1) The column order in the EXCEL file is not the Order I want it to Export in. (i.e. COB Date maybe in Column 'I' in the Excel File but when I export it into .txt I need it to be in the First Column)

2) I need to have the header name Changed in the .txt file (i.e. COB Date is the name in the EXCEL file but I need it to show as cob_date in the .txt file)

View 9 Replies View Related

Converting Pipe Delimited Data String Into Separate Columns Using VBA Macro

Mar 6, 2014

I have a string of data coming from a SQL Server data connection into my workbook. The value in the column is a text string that is pipe delimited. I need a macro to parse the data from that column into applicable separate columns. I would typically use text to columns for this and parse it out manually... but the tool I'm creating is one of the automated variety, so that will not suffice for this application. I need it to do this automatically when the data connection refreshes.

U:U AQ AR AS AT AU AV
to
Produce|Fruit|apple|banana|cherry|date Produce Fruit apple banana cherry date

View 2 Replies View Related

Remove Commas On CSV Through VBA?

Jul 25, 2014

I copied my data and used the paste value function, pasted it onto another excel sheet and then saved as CSV. Ideally, there should be no formatting on the cells. However, column J threw some commas for which i am unable to understand.

All i want to do is put a control in place via VBA to remove all commas from the sheet.

Is there a VBA code that can perform this function?

View 8 Replies View Related

Remove Commas From Number?

Jan 3, 2014

For example, i have some data :

123,123567
12,778
9822,1
8267,222223

then, i want to permanently remove the comma, so the result :
123
12
9822
8267

Because if i use format cells -> use 1k separator, the data actually still contain the comma (even if the looks is 123, but in the formula bar, it stated 123,123567).

View 2 Replies View Related

VBA - Remove Commas From String?

Dec 6, 2011

If someone enters "1,000" in a TextBox, Isnumeric(TextBoxEntry) says True

If I convert it to a value with something like x = TextBoxEntry * 0, the 1,000 becomes 1

I would prefer it to be 1000. Just removing commas would do the trick. How to do it.

View 5 Replies View Related

VBA- Remove Commas From CSV Data

Apr 26, 2009

i have data exported as CSV from in house system, the problem is that data is separted by commas therefore some of cells have split, i need VBA to remove the commas and bring my data back into correct format. Below is a sample of what the data looks like, real data is 5000 rows of data

PFOLIO A/C CODE ACCT NAME CUR CODE DESCRIPTN NARRATIVE DATE ENTRY DATE CASH VALUE De --------------------------------------------------------------------------------------------------------------------------------------------ACA001AUDCUST JP MorganAUDINT0005 SALE 39727.08 INTECH BAL GR FD C29-Dec-082-Jan-09-27690.57N

View 9 Replies View Related

VBA To Remove Characters And Commas

May 26, 2009

i need a macro to do the following,

1. Remove all commas from activeworksheet ( i notice i cant see the commas in excel, but when i open notepad i have commas in empty rows)

2. Remove all characters such as = + # ( ) $ from Column 5

View 9 Replies View Related

Remove Trailing Commas

Mar 15, 2008

Removing Trailing Commas

I have a list (general ledger) GL codes with trailing commas

Example:
123456789,123456722,123789456,,,,,

When I try to use �replace� and replace the multiple commas with nothing it turns my gl codes into a scientific number (1.23456789123456E+26), but my cell is formatted to text.

The other problem is for numbers with a single trailing comma, it would also remove the commas between the GL codes

Is there a function that removes trailing commas, or commas that are not followed by numbers?

View 9 Replies View Related

Char Length And Remove Commas

Dec 1, 2009

is there a way to automate =left(b1,40) and remove commas from the text?

text in the cell would be something like a name for instance john doe, LLC but some names are to long so was using the =left to reduce it to 40 characters and using find/replace for the commas replacing it with a space.

View 7 Replies View Related

Remove Blank Cells From Row - Add Commas Between Data In New Cell

Jan 10, 2014

I have a column of contacts. Each contact has data spread out over many rows, most of which are blank. I would like all of the data spread out through the rows to be in one cell, delineated by commas (so I can import into another program).

Concatenating the cells works except that I end up with 20 commas for two strings of info.

Just so you understand I may have this:
Mary red blue yellow purple
Rob blue purple
Trey yellow

and I want it to look like this

Mary red, blue, yellow, purple
Rob blue, purple
Trey yellow

How should I go about this?

View 14 Replies View Related

Remove Parenthesis From Phone Number And Commas From Figures

May 28, 2014

We have a spreadsheet of our customer's info, to send to our collection agency; when we export it puts parenthesis around the area code and adds a hyphen in the number.

Also it puts commas in the figures. And I need the slashes gone from the date - is there a simple way to do this?

I tried formatting but it doesn't take out the () or ,

View 9 Replies View Related

Remove The Extra White Space Between Each Comma Delimited Digit

Oct 25, 2009

This is a delima I cannot figure out. I had to create passwords for a website we are building. I have 3000 employee numbers has to be used. So what i did was took the first initial and middle initial and last initial and first 5 of the ID number. I did a comma delimiter to obtain all of the letters and numbers. example: ABC12345

My problem is none of the passwords work because when I imported the letters and numbers into the sheet it looks just like the above. However on review I cut and pasted back to notepad and the data looks like this:

"A B C 12345"

So its adding a tab in the password thats thats a problem, How do I remove this extra white space between each comma delimited digit? without having to manually delete it ?

View 3 Replies View Related

Remove Commas From All Cells, Search & Replace Error: Formula Is Too Long

May 15, 2007

I have a large spreadsheet, within which i am trying to remove commas from all cells. I get the error 'formula is too long' when I carry out the search. Some of the cells are >1024 characters in length and contain dates, text etc.

View 5 Replies View Related

Convert Text Delimited File To .xls File Using Vba

Nov 28, 2008

how to word it but if someone understands then please help. I have two excel data files namely Book1.xls & Book2.xls. Both files have different data in it. Both files contain macros. When these macros run the files become **FINALIZED** version.

Originally, I get the above files in my email as txt. attachments. I then move these two txt files to my desktop in a folder called Folder-1. Then I open these files as an Excel and save them.

Basically, I need to know if two txt files are sitting in a folder-1 on my desktop. What can I do or what can I clik that....those two text files get converted into excel automatically, including running that macro I talked about in the above paragrah.

To put it differently, if I have two txt files Book1.txt, Book2.txt in a folder, how can I automatically create an excel **FINALIZED**version which sits right next to their txt version.

View 9 Replies View Related

Save AS Tab Delimited File For SAP

Feb 4, 2008

I'm trying to create a excel sheet with macros that will generate a tab delimitd file which is a journal voucher and I want to upload that .txt file to our financial system (SAP). When I create a tab delimited file manually, that is I enter all the values in excel and save as .txt it works great. But I have created a spreadsheet where the useres (all employees on the financial departement) enter the info required. I have a macro validating that all fields are correct and so on.. When they are finished they sedt the spreadsheet to me (an excelfile) via email, this is done by a submit buttom(macro) and the I have a hidden sheet in the worksheet which gets all the values that are necessery and then I create my tab delimited file using the well known save as fileformat=xltext macro. But SAP will not accept this file. I get error messages as convertion erros in line 4 column 6 and so on. The sheet which is saved as .txt is 100% with formulas, it gets all the value from another sheet(sheet1). I use the formula =IF(Sheet1!$E17=0;"";Sheet1!E17) in the cells with E17 changing to whatever cell is required. What can be wrong here? May it be that all the formulas (columns A to H and row 2 to 1001) somehow mix it all up and even to the cell is empty is the formula is somehow hidden in the .txt file and that messes it all up??

View 2 Replies View Related

Saving A File To Asterisk Delimited?

Aug 30, 2013

I am trying to open up an asterisk delimited file then shrink the data, then after I want to save it back as an asterisk delimited text file. How would this be done in vba, as well as manually?

View 2 Replies View Related

VBA To Read In Delimited Text File

May 21, 2014

I've a word form that collects free text and tick boxes. I'm reading these into separate rows on an excel sheets. So far what works...

I've got excel vba converting the word to a delimited text file
I've got excel vba to remove the erroneous rtns that people have entered into the free text that was things up.

In the free text there are commas entered as well as the tick boxes and this is a csv. Example
"how do I do this, I don't know",1,1,0,1,"really seems to be a challenge!"

If I just use excel to open the text file then the columns work out ok and on a single row - I think the 'text qualifier' is playing a role here.

But I can't replicate this in VBA. If I record a macro it's a query table and I don't know how to amend the code to read into rows.

View 2 Replies View Related

Saving To The CSV (comma Delimited) File

Dec 1, 2006

I have a work sheet with some names address, and phone number in it... I need to save it as a CVS file (comma delimited) easy right??? WRONG!!! everytime I save it, it takes the phone number column and shortens it AND turn the phone numbers into a mess

What it looks like NOW
9057926500

What is looks like after save

9.06E+09

If is make the column bigger it goes back to the "good" numbers, but when I save to a CVS it goes to the "bad" number.

View 9 Replies View Related

Text File But Comma Delimited

Sep 28, 2007

In my workbook I have 10 columns with data, starting at A12 and down. This could be several hundred rows.
I would like to export the first three columns only (Column A, B and C). The TXT file should have the following:
First line: "This file was exported from Excel"
Second line: The value of cell B4 of the worksheet
Third line: The value of cell B5 of the worksheet
Fourth line: Today's date ( as 27/Sep/2007)
Fifth line: No entries (Empty row)
Sixth line will be the first numeric transfer. This is cell A12 value, cell B12 value and cell C12 value.
Seventh line: Cell A13 value, cell B13 value and cell c13 value.
etc, until the end or selected amount of rows.
The values of the different columns should be separated by commas
Column A values could be 9 characters (5 numerics with 4 decimals - 12345.6789)
Column B values could be 10 characters (6 numerics with 4 decimals - 123456.7890)
Column C values could be 10 characters (6 numerics with 4 decimals - 123456.7890)

The text file therefor would look as follows:

This file was exported from Excel.
Company ABC
On this continent
27/Sep/2007

123.4567, 23.7654, 123.4567
1234.5678, 123.4567, 987.6543
12345.6789, 6.0000, 2.9876

I tried to adapt jindon's code but no luck.
I am currently doing it the long way.
='[Workbook1.xls]MySheet'!$A12&", "&'[Workbook1.xls]MySheet'!$B12&", "&'[Workbook1.xls]MySheet'!$C12
This works but I really liked what jindon did and wondered if it could be adapted to fit my needs.

View 9 Replies View Related

Importing Tab Delimited Text File

May 4, 2009

am in a time crunch to write VBA code(not use the macro recorder) to do the following:

Input data from a tab delimited Text file into a pre-formatted sheet.

Each column of the text file is as follows (product code, buy/sell, quantity, price, date) Example row below.

YM Buy 50 12800 1/2/2008

Is there code I can use to do this? I need each individual piece of data to import into it's own cell while keeping the Row/column alignment of the original text document.

View 9 Replies View Related

Macro To Save As CSV File With Extra Commas

Mar 6, 2012

I created a macro to save an active sheet as a CSV file. Two issues arise out of that effort. One is that I can't seem to get the environment variable right to allow any user to save the file to the root of their My Documents folder on their home directory in a domain environment. That way the template will work for everyone and requires no user intervention. Tried using the % with HOMEPATH, but I must somehow have the syntax off.

Secondly, the file being saved as a CSV file is creating a huge several megabyte file with empty lines of commas.

Sub Macro2()
Sheets("Results").Select
ActiveWorkbook.SaveAs Filename:= _
"\%HOMEPATH%My DocumentsTesting.csv", FileFormat:=xlCSVMSDOS, _
CreateBackup:=False
Application.WindowState = xlNormal
End Sub

Thought it should be CSV MS DOS file type, but that doesn't seem to work either. I only want to save the rows with data on the active sheet to a CSV file, which will not have all the extra lines of commas.

View 1 Replies View Related

Prevent Multiple Commas In Text File

Nov 30, 2007

I have a spreadsheet that is produced by a macro. This spreadsheet has a table of 30 rows and 24 columns pasted into it from another spreadsheet. The first 6 rows of vary between containing 1 and 6 columns of data. The next 17 rows always have 24 columns of data. The last row has 1 column of data. The macro saves this spreadsheet as a .csv file. The macro works great, but when I open the .csv file in notepad all the rows have 23 commas, even for the rows with only a few columns of data. Here is a shorten example of the file when opened in Notepad....

<GTS-IPL-Customer>,,,,,,,,,,,,,,,,,,,,,,,,
H1,October,2007,,,Normal,,,,,,,,,,,,,,,,,,,
H2,000426,,,,,,,,,,,,,,,,,,,,,,,
H3,2.72283E+12,,,,,,,,,,,,,,,,,,,,,,,
H4,88100031,,,,,,,,,,,,,,,,,,,,,,,
H5,NBPL,,,,,,,,,,,,,,,,,,,,,,,
D,10012007,0,312,2,310,304,1006,306,306,4,4,31,4,0,0,0,,,,,,,5.805,-23.22
D,10022007,0,334,2,332,330,1009,333,639,-1,3,33,1,0,0,0,,,,,,,6.165,6.165
D,10032007,0,316,2,314,302,1045,316,955,-2,1,31,2,0,0,0,,,,,,,6.675,13.35

Is there anyway to have the macro not insert the commas in the first 6 rows for columns on the right side of the last column of data? I need the data to be formatted in the format shown below.

<GTS-IPL-Customer>
H1,October,2007,,,Normal
H2,000426
H3,2.72283E.....................

View 9 Replies View Related

Editing Contents Of A Text File Which Is Delimited

Aug 2, 2009

I have a text file, which has a column of wrong data. I can change it manually by loading it into Excel but it is semi-colon delimited and I can't export to such a file.

I therefore, would like to write a VBA tool, which reads the file and edits the the value between the 5th and 6th semi-colon and will continue do this for each row (the new value is constant across the rows).

View 9 Replies View Related