Copy From One Column to Text File

[the_golden_gunman]

Hi I have a problem with Excel that I would like to use a Macro to do.
I am unfamilar with the syntax used in Excel so this task is proving
to be somewhat tricky.

I would like to copy all entries from one colum into a text file.
Additionally, I would like to copy these files in such a way that I
can use those files as directory names. For example, if the value in
one of the column cells was "example", I would like to copy that value
as "/value".

I would also like the macro to automatically detect how long the
column I am copying from is, so that instead of having to specify a
range, the macro can be used in any file and it will automatically
detect from what range the columns must be copied from. The only thing
I would specify would be the column (which I would change int eh
macro).

How would I go about writng a Macro to perform such a task?

Thanks

 

[Rick Rothstein \(MVP - VB\)]

Hi I have a problem with Excel that I would like to use a Macro to do.

I am unfamilar with the syntax used in Excel so this task is proving
to be somewhat tricky.

I would like to copy all entries from one colum into a text file.
Additionally, I would like to copy these files in such a way that I
can use those files as directory names. For example, if the value in
one of the column cells was "example", I would like to copy that value
as "/value".

I would also like the macro to automatically detect how long the
column I am copying from is, so that instead of having to specify a
range, the macro can be used in any file and it will automatically
detect from what range the columns must be copied from. The only thing
I would specify would be the column (which I would change int eh
macro).

How would I go about writng a Macro to perform such a task?

Some questions first... How do you want the cell values placed in the
file... each on their own line or all on one line with comma, tab, something
else delimited? Where does your data start in its row... row 1 (no header),
2 (with header), or some other row? Could there be any blank cells within a
column (other than at the end of the data) and, if so, how did you want them
treated? Did you really mean a forward slash in your example (you said
directory, which would be a back slash)? I'm guessing you said the value was
"example" so you meant to write "\example" and not "/value", right?

Rick

 

[the_golden_gunman]

Thanks for your response. Let be more specific.

- I would like to display the data as a series of new lines e.g.
\example1
\example2
\example3

and so forth. So far I can only display as comma sperated values, butI
require new lines.

- Blank cells should be ignored
- The data should start from Row 2 (IE miss the header).

Finally, one other piece of functionality I wold like to incoporate
(if possible) is to then use that list of directory names and create
seperate sub directories in the folder that the text file has been
saved in (but also retaining the saved text file).

Anil

 

[the_golden_gunman]

Incidentally, an example of the Macro I am tryign to wruite so far is
as follows:

Sub ExportToPRN()
Dim FName As String
Dim WholeLine As String
Dim FNum As Integer
Dim myRange As Range
Dim myCell As Range
Dim myName As String

myName = ActiveWorkbook.FullName

FName = Application.GetSaveAsFilename( _
Replace(myName, ".xls", ".prn"))

On Error GoTo EndMacro:
FNum = FreeFile

Open FName For Output Access Write As #FNum
Set myRange = Range("C1:C99")
WholeLine = ""
For Each myCell In myRange
WholeLine = WholeLine & myCell.Text & ", "
Next myCell

WholeLine = Left(WholeLine, Len(WholeLine) - 1)

Print #FNum, Trim(WholeLine)

EndMacro:
On Error GoTo 0
Close #FNum
End Sub

I would like to add line breaks instead of commas at the line "
WholeLine = WholeLine & myCell.Text & ", " ", but I am unsure of the
syntax to use. "\r\n" and "\n" do not appear to work.

 

[Rick Rothstein \(MVP - VB\)]

I would like to add line breaks instead of commas at the line "

WholeLine = WholeLine & myCell.Text & ", " ", but I am unsure of the
syntax to use. "\r\n" and "\n" do not appear to work.

Use the built-in newline character sequence constant... vbNewLine.

WholeLine = WholeLine & myCell.Text & vbNewLine

Rick

 

[Rick Rothstein \(MVP - VB\)]

Finally, one other piece of functionality I wold like to incoporate

(if possible) is to then use that list of directory names and create
seperate sub directories in the folder that the text file has been
saved in (but also retaining the saved text file).

You can use the MkDir Statement for that.

MkDir PathForTextFile & "\" & CellValue

Do the above within your For Each loop (posted in your other message).

Rick

 

[the_golden_gunman]

Thank you very much Rick. Using that line and working out the rest, I
have a partial solution:

Sub ExportToPRN()
Dim FName As String
Dim WholeLine As String
Dim FNum As Integer
Dim myRange As Range
Dim myCell As Range
Dim myName As String

myName = ActiveWorkbook.FullName

FName = Application.GetSaveAsFilename( _
Replace(myName, ".xls", ".prn"))

On Error GoTo EndMacro:
FNum = FreeFile

Open FName For Output Access Write As #FNum
Set myRange = Range("C1:C99")
WholeLine = ""
For Each myCell In myRange
WholeLine = WholeLine & "\" & myCell.Text & vbNewLine
Next myCell

Is it now possible with the generated text file to create directories
from that file? I can always use a PHP script or similar to do it, but
it would be nice if it could be done automatically by the macro.

 

[the_golden_gunman]

Thanks Rick, you're a super star.

 

[the_golden_gunman]

One final problem when trying to use the make directory command. This
macro doesn't seem to generate anything. Any noticable problems with
this macro?

Sub ExportToPRN()
Dim FName As String
Dim WholeLine As String
Dim FNum As Integer
Dim myRange As Range
Dim myCell As Range
Dim myName As String

myName = ActiveWorkbook.FullName

FName = Application.GetSaveAsFilename( _
Replace(myName, ".xls", ".prn"))

On Error GoTo EndMacro:
FNum = FreeFile

Open FName For Output Access Write As #FNum
Set myRange = Range("C1:C99")
WholeLine = ""
For Each myCell In myRange
WholeLine = WholeLine & "\" & myCell.Text & vbNewLine
MkDir PathForTextFile & "\" & myCell.Text
Next myCell

WholeLine = Left(WholeLine, Len(WholeLine) - 1)

Print #FNum, Trim(WholeLine)

EndMacro:
On Error GoTo 0
Close #FNum
End Sub

I have tried removing the "WholeLine = WholeLine & "\" & myCell.Text &
vbNewLine " line, but without success.

 

[the_golden_gunman]

One final problem when trying to use the make directory command. This
macro creates all of the sub directories in the C:\ folder. I would
like the directories to be made in the same directory as the saved
text file.

Also, the text file now no longer contains anything in it. Is it
possible to make the directories AND save the text file?

Sub ExportToPRN()
Dim FName As String
Dim WholeLine As String
Dim FNum As Integer
Dim myRange As Range
Dim myCell As Range
Dim myName As String

myName = ActiveWorkbook.FullName

FName = Application.GetSaveAsFilename( _
Replace(myName, ".xls", ".prn"))

On Error GoTo EndMacro:
FNum = FreeFile

Open FName For Output Access Write As #FNum
Set myRange = Range("C1:C99")
WholeLine = ""
For Each myCell In myRange
MkDir PathForTextFile & "\" & CellValue
WholeLine = WholeLine & "\" & myCell.Text & vbNewLine
Next myCell

WholeLine = Left(WholeLine, Len(WholeLine) - 1)

Print #FNum, Trim(WholeLine)

EndMacro:
On Error GoTo 0
Close #FNum
End Sub

I have tried removing the "WholeLine = WholeLine & "\" & myCell.Text &
vbNewLine " line, but without success.

 

[the_golden_gunman]

Can anyone else share some help?