Output To CSV Text File Produces Extra Spaces
May 21, 2008
I'm outputting some cells to a CSV. At first I tried using the SaveAs function but that renamed my worksheet and didn't work for ranges (as far as I know)
Sub Export()
'Set myADPFile = "C:ADPPCPWADPDATAPRSHWEPI.CSV"
Dim myADPFile As String
myADPFile = "C:PRSHWEPI.CSV"
Dim x As Byte
x = 5
Open myADPFile For Output As 1
While (Not (Sheet3.Range("A" & x).Value = NA))
Print #1, Trim(Sheet3.Range("B" & x).Value), ",", _
Trim(Sheet3.Range("C" & x).Value), ",", _
Sheet3.Range("D" & x).Value, ",", _ ..........................
The values are correct. However, the excess amount of spaces is not. Using the Write function just encases everything in quotation marks. Replacing spaces isn't a good solution because the first row MUST be in that format, and removing all spaces would require more code to put spaces back in. Is there a way to get rid of all the excess spaces surrounding each value?
View 2 Replies
ADVERTISEMENT
Jun 20, 2014
Please refer to attached file.
Column A have data and i need VB Code (Not formula) to check each cell in column A and delete any spaces and make sure that there is no spaces after end of the text.
As an example you can see Cell cell A15 thru A22 have spaces after the text so need to delete those spaces
Book1.xlsx‎
View 3 Replies
View Related
Jan 19, 2010
After doing some searching I found a macro here which imports multiple text files into one workbook, keeping each text file as a seperate worksheet, each worksheet named the same as the original file name (minus the .txt extension).
However, it adds a blank line between each line of text. So I get this:
Pinging 172.27.133.29 with 32 bytes of data:
Reply from 172.27.133.29: bytes=32 time=232ms TTL=62
Reply from 172.27.133.29: bytes=32 time=62ms TTL=62
Reply from 172.27.133.29: bytes=32 time=65ms TTL=62
instead of this:........
Pinging 172.27.133.29 with 32 bytes of data:
Reply from 172.27.133.29: bytes=32 time=232ms TTL=62
Reply from 172.27.133.29: bytes=32 time=62ms TTL=62
Reply from 172.27.133.29: bytes=32 time=65ms TTL=62
This doesn't happen when I use the import wizard, or when I just open the file from the File -> Open menu.
My text files are always the same format (number of lines, etc.), so it's easy to delimit and insert forumlae as needed, but those extra lines are breaking a macro I've previously written to parse the data.
View 9 Replies
View Related
Apr 23, 2009
I am calling a procedure from a commandbar button using on action.
With oButton
.Caption = "&Progress Report"
.OnAction = 'this bit please
.BeginGroup = True
.FaceId = 576
End With
Private Sub ProcessingRequest(ByVal shtRequest As String)
End Sub
View 9 Replies
View Related
Sep 2, 2009
I've created spreadsheet to look after scores & handicaps in a golf society. I'm trying to add extra spaces so that if new members join they can easily be added without me having to add all the extra rows,change formulas at the time.
However I'm getting stuck when trying to sort the table in 'Championship Leaderboard' sheet, basically I only want to sort actual playing members, not the ones marked 'Test'. I've only marked them as 'Test' whilst trying to do this, but they will be blank eventually.
Error values keep popping up, and as a result it won't sort. Trying to get rid of error values to equal 'zero' create problems elsewhere and I just seem to be going round in circles and getting nowhere.
Basically unless a new member is added i don't want anything to appear in the spreadsheet. But i don't want the formulas etc which are there in readiness to affect existing player entries and data
Is VBA the answer or can it be done with formulae etc,
View 6 Replies
View Related
May 18, 2011
I have in a range several numbers that contain extra spaces and therefore these are recognized as text.
This is how the numbers would look : " 123.234.567"
How to remove the extra spaces and the points (as otherwise this will also be recognized as text)?
View 9 Replies
View Related
Sep 30, 2012
I am trying to get rid of some extra spaces at the end of a word in a cell.
As shown in the following code I have tried different functions to get remove them, but they are not working. Are those not spaces?
The cell has this word : "LPPJ4K2**" ' in excel the * appear as a white space
Code:
Filler = "LPPJ4K2**" ' this word is actually is in a cell
Filler = Trim(Filler) ' Not working
Filler = Application.Trim(Filler) ' Not working
Filler = Replace(Filler, " ", "") ' Not working
View 2 Replies
View Related
Mar 22, 2007
I have a macro which opens one excel file, then copies the data into another, dead easy. However the first file is 'downloaded' from a bespoke package, where (for whatever reason) the package appends a number of spaces (" ") after data in one of the columns,
So sometimes the data will contain one, ten or more extra spaces (no telling how many) ie, it could look like "AB ", "AB ", or "AB " etc
Ideally What i need is a small bit of code that once the data has been imported to my sheet it can run and 'strip' extra spaces from the column, lets say column f, to leave all the data in this column to look like:
"AB"
"AF"
"CD1"
"VFE"
I am drawing a blank, any simple lines of code?
View 9 Replies
View Related
Mar 2, 2009
I have a table of postcodes and I need to import them into a bespoke application. The aaplication requires them to have extra spaces depending on the postcode i.e.
BS35 2JW - this is fine because it has the maximum amount of characters 8
BS1 2JW - I need to add 1 space
B1 2JW - I need to add 2 spaces
View 9 Replies
View Related
Sep 25, 2007
I'm in need of some VBA code (to be included in a Macro) that will automatically use the TRIM function for all cells in a whole column and that will then replace the values in that column with the resulting TRIM values. I have a LOT of individual files with varying #'s of records in them, so a way that will address all of them (all the cells in the specified column due to varying #'s of records) would be best.
View 3 Replies
View Related
Nov 3, 2011
I'm using =CONCATENATE() to create Html code.
HTML Code:
=CONCATENATE("<img class=",""""&"alignleft size-medium"&""""," ","title=",""""&D6&""""," ","src=",""""&
"http://www."&E6&"/images/"&C6&""""," ","alt=",""""&F6&""""," />")
The code looks good in the cell:
HTML Code:
<img class="alignleft size-medium" title="Image title here" src=
"http://www.mywebsite.com/images/image_name.jpg" alt="Image alt here" />
But when I copy/paste onto a txt file, its adding an extra (unnecessary) Quotation mark (see code below)
HTML Code:
<img class=""alignleft size-medium"" title=""Image title here"" src=
""http://www.mywebsite.com/images/image_name.jpg"" alt=""Image alt here"" />
View 8 Replies
View Related
Jul 22, 2014
I have an Excel 2013 address book that has extra spaces between first names, last names and middle initials all in 1 cell.
Is there an easy way to remove all the spaces between these components?
View 3 Replies
View Related
Jun 26, 2014
I am working on a VBA routine that calc and verifies hash files for a series of folders and sub directories, using a command line prog called exactfile.exe (exactfile.com). It is very similar to M$'s FCIV utility, but it is actually supported on Win7/Win8. The issue I am having is that I cannot get the standard output/error/console stream to redirect to a text file. I've written the routine multiple ways, trying to figure out redirection. I was using this site
HTML Code:Â [URL]....
as a reference.
Here is my VBS approach. This code is dependent on another sub passing folder_name as a string
[Code] .....
The VBS then executes using wscript, however, since exf.exe does not return an exit code the same way FCIV.exe does, I need the results redirected to a text file. The results vary from a single line saying "No errors." or multiple lines indicating which files do not match their original hash.
Here are a couple iterations of my BAT approach, where i use the BAT to call the VBS. I really thought I would be able to use the standard redirect > here, since the executable is launched using cmd.exe, but I've not gotten the results I want.
[Code] .....
I've scoured more pages than I can count, but no approach seems to work, so far.
View 4 Replies
View Related
Oct 9, 2007
I would like to output the text I perpared in " sheet 1" into a text file, "output.txt"
The imformation I like to output start from coulmn C, and column D then column E.....
The numbers of rows in each column could be different, and also there may be some single empty column in between. The totals numbers of coulmn is unknow.
the text in the output.txt should contant all the imformation in the sheet started from column C, coulmn D, column F.... (if coulmn E is empty). and the text in the output.txt file is continues with no gap in it.
To make it even more powerful for later use, The column that begin with NOT should not be output.
Example:
Excel Data-
A____B____C____D____E____F____G____H____I____J
1a....1b.....1c.....1d............Not.....1g
................2c.....2d............2f......2g
........................3d............3f......3g
........................4d
output.txt-
1c
2c
1d
2d
3d
4d
1g
2g
3g
View 4 Replies
View Related
Feb 7, 2009
I use excel to read in an ASCII data file in which every line beginning with H is a header line and lines beginning with S are data line.
I want excel to filter out the header lines and output the file with the data lines only then outputs these in a new file.
I also want it to read the values from column 72 to 75 and subtract 6 from them then output them again to the same new file.
Sub read()
Open "d:/intouch/0502SPSS.LOG" For Input As #1
Dim i As Integer
Dim lines(9999) As String
i = 0
Do While Not EOF(1)
Line Input #1, z
i = i + 1
If Mid(z, 1, 1) = "H" Then
i = i - 1
ElseIf Mid(z, 1, 1) = "S" Then.................................
the problem is that the new file is generated but it is always empty
View 9 Replies
View Related
May 19, 2014
See attached image.
So what I am looking for is to put a macro behind button 1 which will do the following
Enter A Default Value Of 123 In The Yellow Boxes (B7:K7)
If They Are Blank When The User Clicks The Button
Performs A Search And Replace To Replace The Values 01-01-1990 With B8, 02-01-1990 With C8 Etc Until K8
Output The Contents Of Sheet 2 To A Text File (In XAI Format) In The Following Order A1:A100, B1:B100, C1:C100
[Code] .........
View 1 Replies
View Related
Nov 6, 2009
I was wondering it were possible to reference an error. I'd like a cell to have white text if $O19 produces a #N/A error. I tried formula
=($O19=#N/A)
and the formula
=($O19="#N/A")
View 2 Replies
View Related
Feb 18, 2014
This is my formula with concatenation: =K3/K1*100 & "c" & " Each"
This is the result of the values in K3 divided by K1: 6.41666666666667c Each
I would like to limit the decimal places to two: 6.41c Each.
I have tried to format the cell and none of the formatting options have any effect.
If possible I would like to use the rounding function but I do not know how to combine the formula + text + rounding.
View 9 Replies
View Related
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
Jan 20, 2013
I have some excel files that contain data when I'VE finished entering data >> I saved it when I open the file >>> I FIND THE DATA INTACT .. NO PROBLEM BUT I FIND ALOT OF EMPTY CELLS THAT ARE USELESS I WANT TO DELETE THESE EXTRA EMPTY CELLS... SO WHEN I OPEN THE FILE I CAN FIND ONLY the data .
I have attached a file as an example
View 14 Replies
View Related
Dec 4, 2009
I have got a excel file which has housenumbers. I would like to use the file for importing in a program.
Unfortunatly the program wants housenumbers en extra's split.
The file I have looks like this:
Ordernummer Geadresseerde Straat Huisnummer Toevoeging Postcode Woonplaats
The file I want looks like this:
Ordernummer Geadresseerde Straat Huisnummer Toevoeging Postcode Woonplaats
View 9 Replies
View Related
Aug 12, 2008
I am making a report that puts out a CSV file my trouble is that some of the numbers are converted to date when they are opened in excel. This report is sent out company wide and I need a way to keep the numbers from changing to date. I have added a tic(') mark to the front of the number but it comes over as '12308. What can I do.
View 9 Replies
View Related
Jul 1, 2006
I'm wondering if it's possible to use a button in excel where I can output a cvs file (from another sheet in the file) with just a click on that button.
View 3 Replies
View Related
Apr 23, 2006
I have a text file output from a program that is named “ABC123.exp”. I open up the file with Excel and after manipulating the data with VBA code which includes identifying the Source Path directory(strSourcePath As String ) and the File name (strFileName As String) I want to be able to open up a 'Save As' Window and save the manipulated data into the same Source Path directory(strSourcePath) with the File Name being the prefix of the original with a suffix .txt extension. Ex: “ABC123.txt” instead of “ABC123.exp” and the Save as type: Text (Tab delimited)(*.txt). This way my original data won’t be overwritten.
View 5 Replies
View Related
Dec 3, 2009
Table 1:
I like to go to Berlin.
You like to move to Amsterdam.
They want to see Chicago.
When will the reach San Francisco.
Table 2:
Amsterdam
Chicago
San Francisco
Berlin
What I want to do:
I want my function to see if the text in Table 1 contains a value in Table 2, and if yes, output that value.
Desired Result:
Row A1:
I like to go to Berlin.
Row A2:
Berlin
Row B1:
You like to move to Amsterdam.
Row B2:
Amsterdam
View 9 Replies
View Related
May 21, 2009
I have a problem which may seem simple but is daunting for me.
I have a file in xls format that i need to import data from.
The problem is with column A, (e.g. 0233 45) if I get rid of the spaces I loose leading zero, if I keep the zero I cannot get rid of spaces. The thing is I need both conditions satisfied.
To summarize:
1)Import file and delete columns B and C
2)In column A get rid of spaces but keep leading zero (it's important to keep the zero where it exists, not add one). The length is different for each string in column A.
Here is a small sample of the file that is imported
import and convert.xls
View 2 Replies
View Related
Jun 5, 2006
I'm trying to display some result in my sheet and I don't go further of the MsgBOx use. see my vba code
Dim xslt As New MSXML2.XSLTemplate40
Dim xslDoc As New MSXML2.FreeThreadedDOMDocument40
Dim xmlDoc As New MSXML2.DOMDocument40
Dim xslProc As IXSLProcessor
Dim paramxml As MSXML2.DOMDocument40
xslDoc.async = False
xslDoc.Load "c:compara.xsl"
If (xslDoc.parseError.errorCode <> 0) Then
Dim myErr
Set myErr = xslDoc.parseError
MsgBox ("Ha um erro " & myErr.reason)
Else
Set xslt.stylesheet = xslDoc
xmlDoc.async = False
xmlDoc.Load "C:Instructional_program.xml".................
View 2 Replies
View Related
Jul 10, 2006
I'm trying to parse an output file from x-12-arima using vba. The file is in this format
.....
invalidate the results of this test For the last three year period.
ICMETI, Total Inventories Communications Equipment PAGE 9, SERIES foo
D 12 Final trend cycle
From 2002.Jan To 2005.Dec
Observations 48
Trend filter 13-term Henderson moving average
I/C ratio 1.08 .............
View 5 Replies
View Related
Oct 11, 2008
I realise this is not strictly an excel question but it forms part of my VBA code within excel
I have been writing a VBA program (with some fantastic help from you guys) part of which writes to a batch file to rename files stored in a given folder. Unfortunately this fails if the original files have spaces in the filenames. The batch file contains command lines such as: ...
View 6 Replies
View Related
Jun 24, 2013
I am using Excel 2007. I have pasted my code below. It works fine unless it has a space, in which case it inserts %20. In this case, sRange2 = ActiveSheet.Range("E11") will always have a space in it since this is where i store a job's name.
Sub Macro1()
Dim sRange1 As String
Dim sRange2 As String
Dim sRange3 As String
Dim sRange4 As String
Dim sFullPath As String
[code]....
View 4 Replies
View Related