Please help me
Here is what my data looks like in excel:
NAME--ADDRLINE1--ADDRLINE2--CITY--STATE--ZIP--COUNTRY
DJ----12 APT 2---EAST LANE--NEW YORK--NY--45658--USA
MJ----13 PARK MAR--NORTH LANE--NEW YORK--NY--45658--USA
I want to export this in a text/csv file so that it looks like:
DJ
12 APT 2
EAST LANE
NEW YORK
NY
45658
USA
Any suggestions..!!
4 7000
You may have to perform this operation by Code since it is a non-standard Output Format, but it is a very simple matter as long as you can define the Data Range. I may be wrong on this assumption, so something better may come along. Any questions please feel free to ask. - Code Definition:
- Dim rng1 As Range
-
Dim rng2 As Range
-
-
Set rng1 = Range("A2:G3") 'Exclude Field Header
-
-
Open ActiveWorkbook.Path & "\Export.txt" For Output As #1
-
-
For Each rng2 In rng1
-
Print #1, rng2.Value
-
Next
-
-
Close #1
- Contents of Export.txt:
- DJ
-
12 APT 2
-
EAST LANE
-
NEW YORK
-
NY
-
45658
-
USA
-
MJ
-
13 PARK MAR
-
NORTH LANE
-
NEW YORK
-
NY
-
45658
-
USA
Holy monkey this worked like magic!!!!
Wow, thank you for helping me!!
OK so I simplified my problem to easily explain here.
And I thought I will get the hint and take it from there.
I am sorry for pushing my luck, but I am clueless in VBA.
Its slightly more complicated than that...
My data range is inconsistent. And I have a file header and a file trailer.
My excel file looks like :
--------------------------------------------------------------------------------------------
FILHDR---OWN---UUPVTLTD---02/06/2014---500
PAYHDR---UPS---PYMT-------02/06/2014---200---PAYN---DJ SMITH---123---PAYADD---12 APT 2---EAST LANE---NEW YORK
PAYHDR---UPS---PYMT-------02/06/2014---300---PAYN---MJ DAVID---456---PAYADD---15 LAKE PT---DRIVE LANE---MIAMI
PAYDESC---PAY MONEY NOW
FILTRL---5
--------------------------------------------------------------------------------------------
I want to export this in a csv file so that it looks like:
FILHDR,OWN, UUPVTLTD,02/06/2014,500
PAYHDR,UPS
PYMT,02/06/2014,200
PAYN,DJ SMITH,123
PAYADD,12 APT 2,EAST LANE,NEW YORK
PAYHDR,UPS,PYMT,02/06/2014,300
PAYN,MJ DAVID,456
PAYADD,15 LAKE PT,DRIVE LANE,MIAMI
PAYDESC,PAY NOW
FILTRL,5
------------------------------------------------------------
So file header, Description and trailer appears only once in the file.
The last number with file trailer has number of lines in the CSV file.
Any help will be greatly appreciated.
OK this I got this far: -
Dim rng1 As Range
-
Dim rng2 As Range
-
-
'Finding last cell
-
-
Dim lRealLastRow As Long
-
Dim lRealLastColumn As Long
-
Range("A1").Select
-
On Error Resume Next
-
lRealLastRow = Cells.Find("*", Range("A1"), xlFormulas, , xlByRows, _
-
xlPrevious).Row
-
lRealLastColumn = Cells.Find("*", Range("A1"), xlFormulas, , _
-
xlByColumns, xlPrevious).Column
-
'Finding last cell
-
-
Set rng1 = Range("A1", Cells(lRealLastRow, lRealLastColumn))
-
-
Open ActiveWorkbook.Path & "\Refund.txt" For Output As #1
-
-
For Each rng2 In rng1
-
Print #1, rng2.Value
-
Next
-
-
Close #1
So far I am able to select entire sheet.And export one cell per line on a text sheet.
I am trying to export multiple cells on a single text line.
I struggled on finding the Logic that would generate the Export File given the overall inconsistencies of the Data. I have arrived at a workable solution, although I feel that it may not be the 'Optimal' one. In any event here it goes along with an explanation as well as the Demo Spreadsheet used with this Thread. - Define the complete Data Range as it exists in the Spreadsheet. You can overextend, but not under.
- Loop through every Cell in the specified Range and search for specific Keywords such as: FLHDR, PAYHDR, PYMT, PAYN, PAYADD, PAYDESC, and FILTRL.
- If a Cell contains one of these Keywords, start building a Comma Delimited String starting with the Keyword itself plus (&) an x amount of successive Cell Values depending on the Keyword.
- Write the built String to the Export File.
- RESET the Build String (not really necessary).
- Close the Export File and inform User of its whereabouts.
- This Logic would handle most of the inconsistencies, but the Values for each of the Keywords must be adjacent to them and consist of the same number of Cells.
- Code definition.
-
Sub Button1_Click()
-
Dim rng1 As Range
-
Dim rng2 As Range
-
Dim strBuild
-
Dim intCtr As Integer
-
-
Set rng1 = Range("A1:L12")
-
-
Open ActiveWorkbook.Path & "\Export.txt" For Output As #1
-
-
For Each rng2 In rng1
-
Select Case rng2.Value
-
Case "FILHDR" 'Header, FILHDR & next 4 Cell Values
-
strBuild = rng2.Value & ","
-
For intCtr = 1 To 4
-
strBuild = strBuild & Cells(rng2.Row, rng2.Column + intCtr) & ","
-
Next
-
Print #1, Left$(strBuild, Len(strBuild) - 1)
-
strBuild = ""
-
Case "PAYHDR", "PAYDESC", "FILTRL" 'rng2 & next Cell Value
-
strBuild = rng2.Value & "," & Cells(rng2.Row, rng2.Column + 1)
-
Print #1, strBuild
-
strBuild = ""
-
Case "PYMT", "PAYN" 'rng2 & next 2 Cell Values
-
strBuild = rng2.Value & "," & Cells(rng2.Row, rng2.Column + 1) & _
-
"," & Cells(rng2.Row, rng2.Column + 2)
-
Print #1, strBuild
-
strBuild = ""
-
Case "PAYADD"
-
strBuild = rng2.Value & "," 'PAYADD & next 3 Cell Values
-
For intCtr = 1 To 3
-
strBuild = strBuild & Cells(rng2.Row, rng2.Column + intCtr) & ","
-
Next
-
Print #1, Left$(strBuild, Len(strBuild) - 1)
-
strBuild = ""
-
Case Else 'do nothing
-
End Select
-
Next
-
-
Close #1
-
-
MsgBox "Export.txt can now be viewed in " & ActiveWorkbook.Path & "\", _
-
vbInformation, "Export Complete"
-
End Sub
-
- Contents of Export.txt with your Original Data plus my own set of Dummy Data.
-
FILHDR,OWN,UUPVTLTD,2/6/2014,500
-
PAYHDR,UPS
-
PYMT,2/6/2014,200
-
PAYN,DJ SMITH,123
-
PAYADD,12 APT 2,EAST LANE,NEW YORK
-
PAYHDR,UPS
-
PYMT,2/6/2014,300
-
PAYN,MJ DAVID,456
-
PAYADD,15 LAKE PT,DRIVE LANE,MIAMI
-
PAYDESC,PAY MONEY NOW
-
PAYHDR,UPS
-
PYMT,2/10/2014,175
-
PAYN,RD BARNES,888
-
PAYADD,#16,TYROS AVE.,PHILADELPHIA
-
PAYHDR,UPS
-
PYMT,2/11/2014,400
-
PAYN,A FLINTSTONE,233
-
PAYADD,3333 - APT 5B,3333 LAKESHORE,HOUSTON
-
PAYDESC,PAY MONEY NOW
-
FILTRL,5
-
- Just view the Attachment! (LOL).
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Raj Mudaliar |
last post by:
Have an xml file with line breaks in the begining, parsed it with
XML::Parser, getting an error like:
****
xml declaration not at start of external entity at line 3, column 0,
byte 4:
<?xml...
|
by: Andre |
last post by:
I'm not sure if this is the best place for this post or not, but I can't
find many asp newsgroups anymore.
I have an app that's 3-4 years old, and has been working just fine. One of
the pages...
|
by: intl04 |
last post by:
I have a memo field that is included in some Access reports I created.
Is there some way for the memo field to display nicely formatted text,
with line breaks between paragraphs? Or is it necessary...
|
by: Robin Cushman |
last post by:
Hi all,
I need some help -- I'm working with an A2K database, using DAO, and
am trying to read records into a Crystal Report and then export it to
a folder on our network as an Excel...
|
by: DC Gringo |
last post by:
I have a simple button that should open another window and export a datagrid
to an Excel file. I'm getting: "Name 'window' is not declared."
What do I need to declare or import?
<INPUT...
|
by: Hemant Sipahimalani |
last post by:
The following piece of code is being used to export HTML to excel.
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"...
|
by: Bryan Ax |
last post by:
I'm trying to fix a bug in someone else's code. He's got a string saved
in a database such as:
<xsl:variable name="Var1">
<xsl:choose>
<xsl:when test="@SomeAttribute > 1'">
<xsl:value-of...
|
by: David |
last post by:
We have VS2005 ASP.NET 2.0 application that needs to have each paragraph
separated by a line break. I have added "<br />" into the text column (SQL
2000) and it works fine when displaying on a web...
|
by: HughManity |
last post by:
I was sent an Excel xls file where the Address column consists of up to 3 address lines stacked on top on each other - all in one cell. If there is more than one line in a cell, then all but the...
|
by: rhonda6373 |
last post by:
I have a problem when I try to import an Excel 2007 spreadsheet in Access. Some cells in the spreadsheet contain text with forced line breaks but when I import the data in Access the line breaks are...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |