Creating a macro to transpose columns in to row to export a

[chrisscotty]

Hello All,

I have this list that is in notepad where all the items are vertical. I want to paste in excel so I can transpose the items . I will then export the items to a tab delimited file.

I know you can transpose one at at time by copy and special paste but when you have a few hundred, well, very time consuming.

I was hoping to create a macro. Each entry has one or two spaces before the next entry.

Thanks

They are all like this


Barbara Moon
PHONE: (905) 724-7756
FAX:
EMAIL: barbaro001@hotmail.com
ADDRESS:
APT. #:
CITY: Hamilton
PROVINCE: Ontario
POSTAL/ZIP: L4C 4S2
COUNTRY: CANADA
SALARY RANGE:
MOST RECENT JOB TITLE: Accounts/Finance, Office Manager
TOP 3 SKILLS: AccPac. Simply Accounting, Excel


Bill Kerry
PHONE: (416) 8432 4053
FAX:
EMAIL: bkerry99@sympatico.ca
ADDRESS: 102 Wilson Drive
APT. #:
CITY: Markham
PROVINCE: Ontario
POSTAL/ZIP: L3P 6C3
COUNTRY: CANADA
SALARY RANGE: $75000-99999
MOST RECENT JOB TITLE: Manager
TOP 3 SKILLS: 1) sales & relationship management, 2) team leadership/mentorship, 3) sales strategy development, tactics & process

 

[NateO]

Howdy Chris, Assuming that your data ends up being in Column A, beginning row 1, the following performed well under limited testing:

<pre>
Sub Stuff()
Dim cl As Range, myPath As String, targetFile As String, DestFile As String
myPath = "C:Temp" 'Change Filepath
targetFile = "Stuff.txt" 'Change Target File Name
DestFile = "Stuff2.txt" 'Change Destination File Name
Application.ScreenUpdating = False
Workbooks.OpenText Filename:=myPath & targetFile, Origin:=437, StartRow:=1, _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=True, Semicolon:=False, Comma:=False, Space:=False, Other:=False, _
FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
Range("a1:" & [a1].End(xlDown).Address).Copy
[b1].PasteSpecial Transpose:=True
Application.CutCopyMode = False
For Each cl In [a:a].SpecialCells(xlCellTypeBlanks)
If cl(2) <> "" Then
Range(cl(2), cl(2).End(xlDown)).Copy
cl(2, 2).PasteSpecial Transpose:=True
Application.CutCopyMode = False
End If
Next
[a1].EntireColumn.Delete
[b:b].SpecialCells(xlCellTypeBlanks).EntireRow.Delete
ActiveWorkbook.SaveAs Filename:=myPath & DestFile, FileFormat:=xlText, CreateBackup:=False
Application.ScreenUpdating = True
End Sub</pre>

Hope this helps.

 

[chrisscotty]

Hi Nate,

I tried and I got the following error:

Compile argument:

Named argument not found

the term

TrailingMinusNumbers:=


was highlighted

 

[NateO]

Howdy Chris, my goof, looks like XP isn't working backwards here (should've tested in '97). The following was tested in '00 (Hope you're using 2000):

<pre>
Sub Stuff()
Dim cl As Range, myPath As String, targetFile As String, DestFile As String
myPath = "C:Temp" 'Change Filepath
targetFile = "Stuff.txt" 'Change Target File Name
DestFile = "Stuff2.txt" 'Change Destination File Name
Application.ScreenUpdating = False
Workbooks.OpenText Filename:=myPath & targetFile, Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, _
Space:=False, Other:=False, FieldInfo:=Array(1, 1)
Range("a1:" & [a1].End(xlDown).Address).Copy
[b1].PasteSpecial Transpose:=True
Application.CutCopyMode = False
For Each cl In [a:a].SpecialCells(xlCellTypeBlanks)
If cl(2) <> "" Then
Range(cl(2), cl(2).End(xlDown)).Copy
cl(2, 2).PasteSpecial Transpose:=True
Application.CutCopyMode = False
End If
Next
[a1].EntireColumn.Delete
[b:b].SpecialCells(xlCellTypeBlanks).EntireRow.Delete
ActiveWorkbook.SaveAs Filename:=myPath & DestFile, FileFormat:=xlText, CreateBackup:=False
Application.ScreenUpdating = True
End Sub</pre>

If you're using '97 and the delimited opening doesn't fire, you could record this and make the appropriate substitution.

 

[chrisscotty]

Hi Nate,

I got another error. Thing is, I dont have to output to a text file all in one step.

I really just want to transpose so I can then output to a text file.

Note that some times the last line goes over to the next line and that some times there are 1,2, or 3 lines between each entry.

I only tried the following three entries and they only had one space and I still got the following error.

Run time error 1004
tempstuff.txt could not be found

 

[NateO]

I got another error. Thing is, I dont have to output to a text file all in one step.

Where does the debugger strike?

I really just want to transpose so I can then output to a text file.

No problem. Delete the following line:<pre>
ActiveWorkbook.SaveAs Filename:=myPath & DestFile, FileFormat:=xlText, CreateBackup:=False</pre><pre></pre>

Note that some times the last line goes over to the next line and that some times there are 1,2, or 3 lines between each entry.

Shouldn't have any effect on this particular code.

I only tried the following three entries and they only had one space and I still got the following error.

Run time error 1004
tempstuff.txt could not be found

This has nothing to do with spacing. Double-check the file path that I hard coded in (c:temp). Make sure you have the exact right path and name.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by NateO on 2002-11-14 22:14

 

[chrisscotty]

Nate,

I got the same error.

I do have office 2000 and I am running XP.

Thanks !

 

[NateO]

On 2002-11-15 09:43, chrisscotty wrote:
Nate,

I got the same error.

I do have office 2000 and I am running XP.

Thanks !

Hmmm, I did test the above in xl 2000. Same error, can't find the file?

Please respond with the full dos path of the file and your current code. I haven't seen your edits...

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by NateO on 2002-11-15 16:31

 

[chrisscotty]

The file path of which file ?. I paste the data from notepad in to excel and then run the macro at which time I get the error.So, at that point I had not even saved the file.

I have tried highlighting the row A and not having it highlighted and I still get the error.

As long as the macro transposes the data I can then save as a tab delimited file.

When you used the sample that I sent you it actually worked ?.

The full error is:
Runtime error 1004
tempstuff.txt could not be found check the spelling of the file name and verify that the file location is correct.

If you are trying to open the file from your list of most recently used files on the file menu make sure that the file has not been renamed, moved or deleted

I did a search for tempstuff.txt and nothing came up.

Thanks for your diligence ?.

 

[NateO]

On 2002-11-15 21:18, chrisscotty wrote:
The file path of which file ?.

The text file.

I paste the data from notepad in to excel and then run the macro at which time I get the error.

You don't have to do this, the procedure in question will do this for you. If you're gonna automate, might as well automate (eh)?

I have tried highlighting the row A and not having it highlighted and I still get the error.

This has no effect what-so-ever. The error is that the filepath for your text file, which is hard-coded in the procedure, is incorrect.

When you used the sample that I sent you it actually worked ?.

You sent me a sample? How did you find me?

I did a search for tempstuff.txt and nothing came up.

You can't find the file yourself? How can Excel find it? ROFL! How do you work with it if it doesn't exist? Quite a paradox... Seriously, how do you open this file?

Thanks for your diligence ?.

You're welcome, I'm interested in resolving this.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by NateO on 2002-11-16 16:33