Create Folder If It Doesn't Exist

Hello,
I need help with a macro. Here is a background to my macro and what it
does. I have an excel workbook that will have a worksheet with headers in
row 1. The user will enter data below the headers in row 1. A command
button will also be on this worksheet. When the command button is pressed,
it will take all the data and output a .csv file to a particular location.
Here is the code for this:

Public Sub CreateCSV()
Dim iFile As Integer
Dim lRow As Long
Dim iCol As Integer
Dim sDelimiter As String
Dim sSpace As String
Dim sOutput As String

sDelimiter = ","
sSpace = " "

'Open the file for write
iFile = FreeFile
Open "C:\OUTPUT_FILE\" & ActiveSheet.Name & ".csv" For Output As #iFile

'parse the rows and columns
For lRow = 2 To ActiveSheet.UsedRange.Rows.Count
sOutput = ""
For iCol = 1 To ActiveSheet.UsedRange.Columns.Count

'build output string
If Cells(lRow, iCol) = "" Then
sOutput = sOutput & sSpace & sDelimiter
Else
sOutput = sOutput & Cells(lRow, iCol) & sDelimiter
End If
Next iCol

'write the output string to the file
sOutput = Left(sOutput, Len(sOutput) - 1)
Print #iFile, sOutput
Next lRow
Close #iFile

MsgBox "The .csv file is now located in the following folder -
C:\OUTPUT_FILE", vbInformation, "Upload Data"

End Sub

I am an accounting guy and don't have a lot of VB knowledge, most of this
code was not written by me. Here are the two problems I have with the macro:

1. How do I get this macro code to run when the command button on the
worksheet is clicked?

2. This workbook will be used by multiple users and not all users have the
folder "C:\OUTPUT_FILE\" in their C drive yet. How can I check to see if
this folder exists - if it does output the file - if it doesn't, create the
folder and then output the file?

Please help!!! Thanks!!!!

Ryan

A fellow accountant... Good to see. Here is how to add the directory

Shell ("Command.com /c md C:\OUTPUT_FILE")

Just put this line of code at the beginning of the procedure. If the
directory does not exist it will create it. If it does exist then no harm
done...

HTH

"Neutron1871" wrote:

> Hello,
> I need help with a macro. Here is a background to my macro and what it
> does. I have an excel workbook that will have a worksheet with headers in
> row 1. The user will enter data below the headers in row 1. A command
> button will also be on this worksheet. When the command button is pressed,
> it will take all the data and output a .csv file to a particular location.
> Here is the code for this:
>
> Public Sub CreateCSV()
> Dim iFile As Integer
> Dim lRow As Long
> Dim iCol As Integer
> Dim sDelimiter As String
> Dim sSpace As String
> Dim sOutput As String
>
> sDelimiter = ","
> sSpace = " "
>
> 'Open the file for write
> iFile = FreeFile
> Open "C:\OUTPUT_FILE\" & ActiveSheet.Name & ".csv" For Output As #iFile
>
> 'parse the rows and columns
> For lRow = 2 To ActiveSheet.UsedRange.Rows.Count
> sOutput = ""
> For iCol = 1 To ActiveSheet.UsedRange.Columns.Count
>
> 'build output string
> If Cells(lRow, iCol) = "" Then
> sOutput = sOutput & sSpace & sDelimiter
> Else
> sOutput = sOutput & Cells(lRow, iCol) & sDelimiter
> End If
> Next iCol
>
> 'write the output string to the file
> sOutput = Left(sOutput, Len(sOutput) - 1)
> Print #iFile, sOutput
> Next lRow
> Close #iFile
>
> MsgBox "The .csv file is now located in the following folder -
> C:\OUTPUT_FILE", vbInformation, "Upload Data"
>
> End Sub
>
> I am an accounting guy and don't have a lot of VB knowledge, most of this
> code was not written by me. Here are the two problems I have with the macro:
>
> 1. How do I get this macro code to run when the command button on the
> worksheet is clicked?
>
> 2. This workbook will be used by multiple users and not all users have the
> folder "C:\OUTPUT_FILE\" in their C drive yet. How can I check to see if
> this folder exists - if it does output the file - if it doesn't, create the
> folder and then output the file?
>
> Please help!!! Thanks!!!!
>
> Ryan
>

Hi,
1. To create a button and tie your code to it, you need to select the Menu
option View/Toolbars/Control Toolbox. Once you see this toolbar - Click on
the Command Button Icon and then on your sheet where you want to place it.
Then right click on it and select "View Code" option. You will automatically
move to the Excel's code/development environment and will see something like
this

Private Sub CommandButton1_Click()

End Sub

Just type the name of your routine between those two lines.

2. As far as the code to create the folder is concerned, it is as below.

Sub CreateFolderIfNeeded()

ChDrive "C:"
On Error Resume Next
MkDir "c:\OUTPUT_FILE"
On Error Resume Next

End Sub
Just copy and paste the code in the same module where your existing code is.
Then from within your code call this piece of code before doing anything -
for instance in the line just above the line reading as 'Open the file for
write. Hence that part of the code will become

========
CreateFolderIfNeeded
'Open the file for write
=========

HTH
Alok Joshi



"Neutron1871" wrote:

> Hello,
> I need help with a macro. Here is a background to my macro and what it
> does. I have an excel workbook that will have a worksheet with headers in
> row 1. The user will enter data below the headers in row 1. A command
> button will also be on this worksheet. When the command button is pressed,
> it will take all the data and output a .csv file to a particular location.
> Here is the code for this:
>
> Public Sub CreateCSV()
> Dim iFile As Integer
> Dim lRow As Long
> Dim iCol As Integer
> Dim sDelimiter As String
> Dim sSpace As String
> Dim sOutput As String
>
> sDelimiter = ","
> sSpace = " "
>
> 'Open the file for write
> iFile = FreeFile
> Open "C:\OUTPUT_FILE\" & ActiveSheet.Name & ".csv" For Output As #iFile
>
> 'parse the rows and columns
> For lRow = 2 To ActiveSheet.UsedRange.Rows.Count
> sOutput = ""
> For iCol = 1 To ActiveSheet.UsedRange.Columns.Count
>
> 'build output string
> If Cells(lRow, iCol) = "" Then
> sOutput = sOutput & sSpace & sDelimiter
> Else
> sOutput = sOutput & Cells(lRow, iCol) & sDelimiter
> End If
> Next iCol
>
> 'write the output string to the file
> sOutput = Left(sOutput, Len(sOutput) - 1)
> Print #iFile, sOutput
> Next lRow
> Close #iFile
>
> MsgBox "The .csv file is now located in the following folder -
> C:\OUTPUT_FILE", vbInformation, "Upload Data"
>
> End Sub
>
> I am an accounting guy and don't have a lot of VB knowledge, most of this
> code was not written by me. Here are the two problems I have with the macro:
>
> 1. How do I get this macro code to run when the command button on the
> worksheet is clicked?
>
> 2. This workbook will be used by multiple users and not all users have the
> folder "C:\OUTPUT_FILE\" in their C drive yet. How can I check to see if
> this folder exists - if it does output the file - if it doesn't, create the
> folder and then output the file?
>
> Please help!!! Thanks!!!!
>
> Ryan
>

I'm still having an issue....

I've placed your suggested line of code right after the Dim statements.
What it is doing is saying "Path Not Found", then creating the folder with no
..csv file inside.

"Jim Thomlinson" wrote:

> A fellow accountant... Good to see. Here is how to add the directory
>
> Shell ("Command.com /c md C:\OUTPUT_FILE")
>
> Just put this line of code at the beginning of the procedure. If the
> directory does not exist it will create it. If it does exist then no harm
> done...
>
> HTH
>
> "Neutron1871" wrote:
>
> > Hello,
> > I need help with a macro. Here is a background to my macro and what it
> > does. I have an excel workbook that will have a worksheet with headers in
> > row 1. The user will enter data below the headers in row 1. A command
> > button will also be on this worksheet. When the command button is pressed,
> > it will take all the data and output a .csv file to a particular location.
> > Here is the code for this:
> >
> > Public Sub CreateCSV()
> > Dim iFile As Integer
> > Dim lRow As Long
> > Dim iCol As Integer
> > Dim sDelimiter As String
> > Dim sSpace As String
> > Dim sOutput As String
> >
> > sDelimiter = ","
> > sSpace = " "
> >
> > 'Open the file for write
> > iFile = FreeFile
> > Open "C:\OUTPUT_FILE\" & ActiveSheet.Name & ".csv" For Output As #iFile
> >
> > 'parse the rows and columns
> > For lRow = 2 To ActiveSheet.UsedRange.Rows.Count
> > sOutput = ""
> > For iCol = 1 To ActiveSheet.UsedRange.Columns.Count
> >
> > 'build output string
> > If Cells(lRow, iCol) = "" Then
> > sOutput = sOutput & sSpace & sDelimiter
> > Else
> > sOutput = sOutput & Cells(lRow, iCol) & sDelimiter
> > End If
> > Next iCol
> >
> > 'write the output string to the file
> > sOutput = Left(sOutput, Len(sOutput) - 1)
> > Print #iFile, sOutput
> > Next lRow
> > Close #iFile
> >
> > MsgBox "The .csv file is now located in the following folder -
> > C:\OUTPUT_FILE", vbInformation, "Upload Data"
> >
> > End Sub
> >
> > I am an accounting guy and don't have a lot of VB knowledge, most of this
> > code was not written by me. Here are the two problems I have with the macro:
> >
> > 1. How do I get this macro code to run when the command button on the
> > worksheet is clicked?
> >
> > 2. This workbook will be used by multiple users and not all users have the
> > folder "C:\OUTPUT_FILE\" in their C drive yet. How can I check to see if
> > this folder exists - if it does output the file - if it doesn't, create the
> > folder and then output the file?
> >
> > Please help!!! Thanks!!!!
> >
> > Ryan
> >

Woooot!!! It works. Thanks Alok and Jim for your quick response!!!!

"Alok" wrote:

> Hi,
> 1. To create a button and tie your code to it, you need to select the Menu
> option View/Toolbars/Control Toolbox. Once you see this toolbar - Click on
> the Command Button Icon and then on your sheet where you want to place it.
> Then right click on it and select "View Code" option. You will automatically
> move to the Excel's code/development environment and will see something like
> this
>
> Private Sub CommandButton1_Click()
>
> End Sub
>
> Just type the name of your routine between those two lines.
>
> 2. As far as the code to create the folder is concerned, it is as below.
>
> Sub CreateFolderIfNeeded()
>
> ChDrive "C:"
> On Error Resume Next
> MkDir "c:\OUTPUT_FILE"
> On Error Resume Next
>
> End Sub
> Just copy and paste the code in the same module where your existing code is.
> Then from within your code call this piece of code before doing anything -
> for instance in the line just above the line reading as 'Open the file for
> write. Hence that part of the code will become
>
> ========
> CreateFolderIfNeeded
> 'Open the file for write
> =========
>
> HTH
> Alok Joshi
>
>
>
> "Neutron1871" wrote:
>
> > Hello,
> > I need help with a macro. Here is a background to my macro and what it
> > does. I have an excel workbook that will have a worksheet with headers in
> > row 1. The user will enter data below the headers in row 1. A command
> > button will also be on this worksheet. When the command button is pressed,
> > it will take all the data and output a .csv file to a particular location.
> > Here is the code for this:
> >
> > Public Sub CreateCSV()
> > Dim iFile As Integer
> > Dim lRow As Long
> > Dim iCol As Integer
> > Dim sDelimiter As String
> > Dim sSpace As String
> > Dim sOutput As String
> >
> > sDelimiter = ","
> > sSpace = " "
> >
> > 'Open the file for write
> > iFile = FreeFile
> > Open "C:\OUTPUT_FILE\" & ActiveSheet.Name & ".csv" For Output As #iFile
> >
> > 'parse the rows and columns
> > For lRow = 2 To ActiveSheet.UsedRange.Rows.Count
> > sOutput = ""
> > For iCol = 1 To ActiveSheet.UsedRange.Columns.Count
> >
> > 'build output string
> > If Cells(lRow, iCol) = "" Then
> > sOutput = sOutput & sSpace & sDelimiter
> > Else
> > sOutput = sOutput & Cells(lRow, iCol) & sDelimiter
> > End If
> > Next iCol
> >
> > 'write the output string to the file
> > sOutput = Left(sOutput, Len(sOutput) - 1)
> > Print #iFile, sOutput
> > Next lRow
> > Close #iFile
> >
> > MsgBox "The .csv file is now located in the following folder -
> > C:\OUTPUT_FILE", vbInformation, "Upload Data"
> >
> > End Sub
> >
> > I am an accounting guy and don't have a lot of VB knowledge, most of this
> > code was not written by me. Here are the two problems I have with the macro:
> >
> > 1. How do I get this macro code to run when the command button on the
> > worksheet is clicked?
> >
> > 2. This workbook will be used by multiple users and not all users have the
> > folder "C:\OUTPUT_FILE\" in their C drive yet. How can I check to see if
> > this folder exists - if it does output the file - if it doesn't, create the
> > folder and then output the file?
> >
> > Please help!!! Thanks!!!!
> >
> > Ryan
> >