Hi,

I have been tasked with creating a folder, a text file and a vba script from
an excel spreadsheet and cannot figure out how to do this. I'm very much a
novice at writing macros in excel and need a lot of help in doing this.

The excel spreadsheet will be set up in such a way that then will be one
column filled with entry numbers (eg. 1, 2, 3,..., n) and the second column
filled by entry names (eg. GHWYX4-RT5, GHVT3-RT94,... or something to that
effect). What I need to do is create individual folders with the name of each
of the entries in the second column (eg. GHWYX4-RT5), then fill each folder
with a text file with some constant name (eg. mytext, every text file must
have the same name) that contains the first entry in the row, followed by the
second entry, separated by a "," (eg. 1, GHWYX4-RT5). There also needs to be
no quotations in the text file, it must be exactly in the format I stated. In
addition to this, I need to create the vbs file that contains a constant
script for all of the vbs files, as well as a constant name for all of the
vbs files.

I've been searching for quite a while and so far have only been able to
create folders from the second column in the list, however with my method I
don't really see how I would even create the text files or vba in the folders
I created, nor do I even really understand how to create text files or vba
from excel.

Any help would be greatly appreciated!

Thanks very much,
Matt

try this code. you need to put it into a standard code module:


Option Explicit
Sub CreateFiles()
Const ROOT As String = "C:\MyRoot\"
Dim rw As Long
Dim sFolder As String
Dim sFileName As String
Dim fn As Long

rw = 1
Do Until Cells(rw, 1) = ""
sFileName = Cells(rw, 2)
sFolder = ROOT & sFileName
MkDir sFolder
fn = FreeFile
Open sFolder & "\" & sFileName & ".txt" For Output As #fn
Print #fn, rw & "," & Cells(rw, 2)
Close

rw = rw + 1
Loop
End Sub

"mjnaiden" wrote:

> Hi,
>
> I have been tasked with creating a folder, a text file and a vba script from
> an excel spreadsheet and cannot figure out how to do this. I'm very much a
> novice at writing macros in excel and need a lot of help in doing this.
>
> The excel spreadsheet will be set up in such a way that then will be one
> column filled with entry numbers (eg. 1, 2, 3,..., n) and the second column
> filled by entry names (eg. GHWYX4-RT5, GHVT3-RT94,... or something to that
> effect). What I need to do is create individual folders with the name of each
> of the entries in the second column (eg. GHWYX4-RT5), then fill each folder
> with a text file with some constant name (eg. mytext, every text file must
> have the same name) that contains the first entry in the row, followed by the
> second entry, separated by a "," (eg. 1, GHWYX4-RT5). There also needs to be
> no quotations in the text file, it must be exactly in the format I stated. In
> addition to this, I need to create the vbs file that contains a constant
> script for all of the vbs files, as well as a constant name for all of the
> vbs files.
>
> I've been searching for quite a while and so far have only been able to
> create folders from the second column in the list, however with my method I
> don't really see how I would even create the text files or vba in the folders
> I created, nor do I even really understand how to create text files or vba
> from excel.
>
> Any help would be greatly appreciated!
>
> Thanks very much,
> Matt

Thanks a lot Patrick!

That worked exactly how I had wanted it to in terms of the text file and
folder creation, thank you very, very much!

I was just wondering though, is there a way to create a .vbs file as well in
the folders, alongside the .txt file with identical contents for each folder?

Again, thanks very much!
Matt


"mjnaiden" wrote:

> Hi,
>
> I have been tasked with creating a folder, a text file and a vba script from
> an excel spreadsheet and cannot figure out how to do this. I'm very much a
> novice at writing macros in excel and need a lot of help in doing this.
>
> The excel spreadsheet will be set up in such a way that then will be one
> column filled with entry numbers (eg. 1, 2, 3,..., n) and the second column
> filled by entry names (eg. GHWYX4-RT5, GHVT3-RT94,... or something to that
> effect). What I need to do is create individual folders with the name of each
> of the entries in the second column (eg. GHWYX4-RT5), then fill each folder
> with a text file with some constant name (eg. mytext, every text file must
> have the same name) that contains the first entry in the row, followed by the
> second entry, separated by a "," (eg. 1, GHWYX4-RT5). There also needs to be
> no quotations in the text file, it must be exactly in the format I stated. In
> addition to this, I need to create the vbs file that contains a constant
> script for all of the vbs files, as well as a constant name for all of the
> vbs files.
>
> I've been searching for quite a while and so far have only been able to
> create folders from the second column in the list, however with my method I
> don't really see how I would even create the text files or vba in the folders
> I created, nor do I even really understand how to create text files or vba
> from excel.
>
> Any help would be greatly appreciated!
>
> Thanks very much,
> Matt

Sorry, I just noticed something wrong with my last post...

In the .vbs file they are not all consistent. The bulk of the script is
consistent, however one line involving copying of files is different, in
which the folder from which the file is being copied will change (eg.
cop1.CopyFile "c:\data\test_DBs\querytxt\AAA\text.txt",
"c:\data\test_DBs\arcquery.txt" vs cop1.CopyFile
"c:\data\test_DBs\querytxt\BBB\text.txt", "c:\data\test_DBs\arcquery.txt",
where AAA and BBB were the folders created from the excel file in the
previous solution, and text.txt is the corresponding text file created for
each folder).

Is it possible to do this? If so, also how would I input the constant text
that would go in with the script?

Thanks again,
Matt



"mjnaiden" wrote:

> Hi,
>
> I have been tasked with creating a folder, a text file and a vba script from
> an excel spreadsheet and cannot figure out how to do this. I'm very much a
> novice at writing macros in excel and need a lot of help in doing this.
>
> The excel spreadsheet will be set up in such a way that then will be one
> column filled with entry numbers (eg. 1, 2, 3,..., n) and the second column
> filled by entry names (eg. GHWYX4-RT5, GHVT3-RT94,... or something to that
> effect). What I need to do is create individual folders with the name of each
> of the entries in the second column (eg. GHWYX4-RT5), then fill each folder
> with a text file with some constant name (eg. mytext, every text file must
> have the same name) that contains the first entry in the row, followed by the
> second entry, separated by a "," (eg. 1, GHWYX4-RT5). There also needs to be
> no quotations in the text file, it must be exactly in the format I stated. In
> addition to this, I need to create the vbs file that contains a constant
> script for all of the vbs files, as well as a constant name for all of the
> vbs files.
>
> I've been searching for quite a while and so far have only been able to
> create folders from the second column in the list, however with my method I
> don't really see how I would even create the text files or vba in the folders
> I created, nor do I even really understand how to create text files or vba
> from excel.
>
> Any help would be greatly appreciated!
>
> Thanks very much,
> Matt

OOPS

Print #fn, rw & "," & Cells(rw, 2)


should be

Print #fn, Cells(r,1) & "," & Cells(rw, 2)

so that the number in column 1 gets sent into the text file.

updated code:

Option Explicit
Sub CreateFiles()
Const ROOT As String = "C:\MyRoot\"
Dim rw As Long
Dim sFolder As String
Dim sFileName As String
Dim fn As Long

rw = 1
Do Until Cells(rw, 1) = ""
sFileName = Cells(rw, 2)
sFolder = ROOT & sFileName
MkDir sFolder

'text file
fn = FreeFile
Open sFolder & "\" & sFileName & ".txt" For Output As #fn
Print #fn, Cells(r,1) & "," & Cells(rw, 2)
Close

'vbs file
fn = FreeFile
Open sFolder & "\" & sFileName & ".vbs" For Output As #fn
Print #fn, Cells(r,1) & "," & Cells(rw, 2)
Close

rw = rw + 1
Loop
End Sub




"Patrick Molloy" <(E-Mail Removed)> wrote in message
news:B91FE192-4483-46FA-AFAD-(E-Mail Removed)...
> try this code. you need to put it into a standard code module:
>
>
> Option Explicit
> Sub CreateFiles()
> Const ROOT As String = "C:\MyRoot\"
> Dim rw As Long
> Dim sFolder As String
> Dim sFileName As String
> Dim fn As Long
>
> rw = 1
> Do Until Cells(rw, 1) = ""
> sFileName = Cells(rw, 2)
> sFolder = ROOT & sFileName
> MkDir sFolder
> fn = FreeFile
> Open sFolder & "\" & sFileName & ".txt" For Output As #fn
> Print #fn, rw & "," & Cells(rw, 2)
> Close
>
> rw = rw + 1
> Loop
> End Sub
>
> "mjnaiden" wrote:
>
>> Hi,
>>
>> I have been tasked with creating a folder, a text file and a vba script
>> from
>> an excel spreadsheet and cannot figure out how to do this. I'm very much
>> a
>> novice at writing macros in excel and need a lot of help in doing this.
>>
>> The excel spreadsheet will be set up in such a way that then will be one
>> column filled with entry numbers (eg. 1, 2, 3,..., n) and the second
>> column
>> filled by entry names (eg. GHWYX4-RT5, GHVT3-RT94,... or something to
>> that
>> effect). What I need to do is create individual folders with the name of
>> each
>> of the entries in the second column (eg. GHWYX4-RT5), then fill each
>> folder
>> with a text file with some constant name (eg. mytext, every text file
>> must
>> have the same name) that contains the first entry in the row, followed by
>> the
>> second entry, separated by a "," (eg. 1, GHWYX4-RT5). There also needs to
>> be
>> no quotations in the text file, it must be exactly in the format I
>> stated. In
>> addition to this, I need to create the vbs file that contains a constant
>> script for all of the vbs files, as well as a constant name for all of
>> the
>> vbs files.
>>
>> I've been searching for quite a while and so far have only been able to
>> create folders from the second column in the list, however with my method
>> I
>> don't really see how I would even create the text files or vba in the
>> folders
>> I created, nor do I even really understand how to create text files or
>> vba
>> from excel.
>>
>> Any help would be greatly appreciated!
>>
>> Thanks very much,
>> Matt

Good catch, I hadn't noticed that it wasn't working properly.

Looks good now, thanks again!
Matt


"Patrick Molloy" wrote:

> OOPS
>
> Print #fn, rw & "," & Cells(rw, 2)
>
>
> should be
>
> Print #fn, Cells(r,1) & "," & Cells(rw, 2)
>
> so that the number in column 1 gets sent into the text file.
>
> updated code:
>
> Option Explicit
> Sub CreateFiles()
> Const ROOT As String = "C:\MyRoot\"
> Dim rw As Long
> Dim sFolder As String
> Dim sFileName As String
> Dim fn As Long
>
> rw = 1
> Do Until Cells(rw, 1) = ""
> sFileName = Cells(rw, 2)
> sFolder = ROOT & sFileName
> MkDir sFolder
>
> 'text file
> fn = FreeFile
> Open sFolder & "\" & sFileName & ".txt" For Output As #fn
> Print #fn, Cells(r,1) & "," & Cells(rw, 2)
> Close
>
> 'vbs file
> fn = FreeFile
> Open sFolder & "\" & sFileName & ".vbs" For Output As #fn
> Print #fn, Cells(r,1) & "," & Cells(rw, 2)
> Close
>
> rw = rw + 1
> Loop
> End Sub
>
>
>
>
> "Patrick Molloy" <(E-Mail Removed)> wrote in message
> news:B91FE192-4483-46FA-AFAD-(E-Mail Removed)...
> > try this code. you need to put it into a standard code module:
> >
> >
> > Option Explicit
> > Sub CreateFiles()
> > Const ROOT As String = "C:\MyRoot\"
> > Dim rw As Long
> > Dim sFolder As String
> > Dim sFileName As String
> > Dim fn As Long
> >
> > rw = 1
> > Do Until Cells(rw, 1) = ""
> > sFileName = Cells(rw, 2)
> > sFolder = ROOT & sFileName
> > MkDir sFolder
> > fn = FreeFile
> > Open sFolder & "\" & sFileName & ".txt" For Output As #fn
> > Print #fn, rw & "," & Cells(rw, 2)
> > Close
> >
> > rw = rw + 1
> > Loop
> > End Sub
> >
> > "mjnaiden" wrote:
> >
> >> Hi,
> >>
> >> I have been tasked with creating a folder, a text file and a vba script
> >> from
> >> an excel spreadsheet and cannot figure out how to do this. I'm very much
> >> a
> >> novice at writing macros in excel and need a lot of help in doing this.
> >>
> >> The excel spreadsheet will be set up in such a way that then will be one
> >> column filled with entry numbers (eg. 1, 2, 3,..., n) and the second
> >> column
> >> filled by entry names (eg. GHWYX4-RT5, GHVT3-RT94,... or something to
> >> that
> >> effect). What I need to do is create individual folders with the name of
> >> each
> >> of the entries in the second column (eg. GHWYX4-RT5), then fill each
> >> folder
> >> with a text file with some constant name (eg. mytext, every text file
> >> must
> >> have the same name) that contains the first entry in the row, followed by
> >> the
> >> second entry, separated by a "," (eg. 1, GHWYX4-RT5). There also needs to
> >> be
> >> no quotations in the text file, it must be exactly in the format I
> >> stated. In
> >> addition to this, I need to create the vbs file that contains a constant
> >> script for all of the vbs files, as well as a constant name for all of
> >> the
> >> vbs files.
> >>
> >> I've been searching for quite a while and so far have only been able to
> >> create folders from the second column in the list, however with my method
> >> I
> >> don't really see how I would even create the text files or vba in the
> >> folders
> >> I created, nor do I even really understand how to create text files or
> >> vba
> >> from excel.
> >>
> >> Any help would be greatly appreciated!
> >>
> >> Thanks very much,
> >> Matt
>