Excel 2003 macro code converting comma delimiters

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Gram123

Thread Starter
Joined
Mar 15, 2001
Messages
1,829
Hallo again! How are you keeping? :)

I have an Excel 2003 spreadsheet with data in columns A thru J and no header row. This will always be the same.
At present, I perform a Find & Replace on the data, to convert any commas into tildes. One of the columns sometimes contains address info, and as such can contain commas that need to be retained.

I then save the file as a *.csv (comma delimited) file.
Then I drag the csv file into another program (PSPad) which allows me to Find & Replace the commas (created by saving as a csv file) with little sqaures (by pressing Alt + 030).
I then convert any tildes back to commas and save the result as a text file which we can drop into our system to update a massive database.

I'm wondering if I really need this stage of converting to a csv in the middle - I'm sure the find & replace stuff can be done in VB code, and I can export directly from Excel into .txt format. I could do record a macro of myself doing it manually, but I'm not sure how to refer to the wee squares or export a .txt version of the file - i.e. would I just save as a *.txt (Text tab delimited) file or something?

Any help with such code would be greatly appreciated!
 
Joined
Sep 4, 2003
Messages
4,912
The code below should do the trick. It will process each row and automatically and create the text file you want on the fly without having to export or perform any find / replaces. Just change the path in RED to reflect the correct name and path of the processed file to be created.

Code:
Sub ProcessFile()

Dim dest As Integer

dest = FreeFile

Open "[COLOR="Red"]C:\Processed\Output.txt[/COLOR]" For Output As #dest

For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row

For Each vCell In Range("A" & i & ":" & "J" & i).Cells

vLine = vLine & vCell & Chr(30)

Next vCell

Print #dest, Mid(vLine, 1, Len(vLine) - 1)

vLine = ""

Next i

Close #dest

End Sub
Rollin
 

Gram123

Thread Starter
Joined
Mar 15, 2001
Messages
1,829
Yay! That's perfect, cheers Rollin!

One extra question regarding the output file - would it be possible to automatically append the date (in ddmmyy format) and the number of rows (in parenthesis) to the name of the resulting text file?

As it stands, I could use the site name as the filename, for example:
NEWCASTLE.txt
That's fine, I'll just need to rename the file each time its run to include the other info.

However, it would be great if it could automatically name it, for example:
NEWCASTLE 260911 (15).txt

Don't worry if this is too much of a hassle, it's tons better now than the old procedure!

Cheers,

Gram
 

Gram123

Thread Starter
Joined
Mar 15, 2001
Messages
1,829
In fact, the number of rows is really the only essential bit, and we could even do away with the brackets, if that makes it easier? So it could just be, e.g.:
NEWCASTLE 15.txt

When I drop the file into the database for processing, it gets a date stamp anyway - that's a "date received" rather than the date the file was created, but 99.9% of the time they'll be the same anyway, so I could live without the date being added.
Of course, if I need to manually rename the file, then I'll continue to add it.
 
Joined
Sep 4, 2003
Messages
4,912
Here is the updated code. I always replace spaces in filenames with underscores ( _ ) since spaces tend to cause problems in some aspects of automation.


Code:
Sub ProcessFile()

Dim dest As Integer

dest = FreeFile

vPath = "C:\Processed\Output_" & Format(Date, "DDMMYY") & ".txt"

Open vPath For Output As #dest

For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row

For Each vCell In Range("A" & i & ":" & "J" & i).Cells

vLine = vLine & vCell & Chr(30)

Next vCell

Print #dest, Mid(vLine, 1, Len(vLine) - 1)

vLine = ""

Next i

Debug.Print vPath

Close #dest

Name vPath As Replace(vPath, ".txt", "_(" & i - 1 & ").txt")


End Sub
Rollin
 

Gram123

Thread Starter
Joined
Mar 15, 2001
Messages
1,829
Ah, very nice!

I've added a MsgBox to the end just to alert the user that the text file has been created (as the macro is very "quiet" !).

Thanks muchly for your help, Rollin!
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top