creating txt files using adjacent cell values 2

[gmoorthy]

I have an excel speradsheet having two columns and i would need to create txt files from these wherein the text file names should come from cell A1 and the data within that text file should come from cell b1. These files should be created in C:\jobs ( local directory)

Example

Column A Column B

City NewYork
Country USA


I should get two text files ( City.txt and Country.txt having data as Newyork and USA repectively.

The excel file has about 100 rows.

 

[SkipVought]

Hi,

Here's a start assuming your data starts in A1

Dim r as range, sFile1 as string, sFile2 as string

For each r in range([A1], [A1].end(xldown))
   sFile1 = r.value
   sFile2 = r.offset(0,1).value

'Search VBA help on open, close, etc and give the next part a shot
'Post back with your questions
Next

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[gmoorthy]

Ok i troed something as the code below and what is happening is that i am getting an error on the bold lilne run time error :52 bad file name or number

My value in cell b1 is something like this </XML.....>

This is working if i give plain text in cell A1 and B1. Any help is appreciated.



Sub Test1()
Dim strPath As String
Dim objCell As Range
Dim FS As Object
Dim TS As Object

strPath = "C:\test\"
Set FS = CreateObject("Scripting.FileSystemObject")


For Each objCell In Selection
Set TS = FS.CreateTextFile(strPath _
& objCell.Offset(0, 1).Value & ".txt", True)
TS.writeline objCell.Value
TS.Close
Next objCell

Set TS = Nothing
Set FS = Nothing


End Sub

 

[SkipVought]

What does your string resolve to?

Didn't you parse the XML code on your sheet as suggested to leave the text you want?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[gmoorthy]

No the XML string is part of the data that needs to be in the text file.

 

[Andrzejek]

You know, you would get a lot better help, and a lot faster, if you would state your problem clearly, so people who are willing to help you would not have to guess, read your mind, or assume.

(Your) Example

Column A Column B

City NewYork
Country USA

Is not true, because “My value in cell b1 is something like this </XML.....>”, so it is NOT just ‘NewYork’, and we can only guess what this ‘something’ is...

“i am getting an error on the bold lilne” there is no bold line in your post.
:-(

Have fun.

---- Andy

 

[gmoorthy]

My apologies if i caused any grief . I have bolded the area where i got the error an dthe column B1 has values like </XML/.......>


Sub Test1()
Dim strPath As String
Dim objCell As Range
Dim FS As Object
Dim TS As Object

strPath = "C:\test\"
Set FS = CreateObject("Scripting.FileSystemObject")


For Each objCell In Selection
Set TS = FS.CreateTextFile(strPath _
& objCell.Offset(0, 1).Value & ".txt", True)TS.writeline objCell.Value
TS.Close
Next objCell

Set TS = Nothing
Set FS = Nothing


End Sub

 

[Andrzejek]

How about:

Dim r as range [green]  ', sFile1 as string, sFile2 as string[/green]
Dim strPath As String

strPath = "C:\test\"

For each r in range([A1], [A1].end(xldown))[green]
   'sFile1 = r.value
   'sFile2 = r.offset(0,1).value[/green]
   
   Open strPath & "\" & r.value & ".txt" For Output As #1
   Print #1, r.offset(0,1).value
   Close #1
Next

Baaed on Skip's post

Have fun.

---- Andy

 

[gmoorthy]

Thanks for the code but it is failing with the following error

Compile error invalid outside procedure for

strpath ="C:\test\"

 

[SkipVought]

All your code must reside in a procedure. You can have many procedures in a module.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}