Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Folder If It Doesn't Exist
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Folder If It Doesn't Exist
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Folder If It Doesn't Exist
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Folder If It Doesn't Exist
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Does folder exist problem | Excel Programming | |||
How to: check if folder exist, if not, create | Excel Programming | |||
Create Folder and Text File in folder | Excel Programming | |||
How to check if a folder/directory exist using VBA | Excel Programming | |||
Create Folder / Copy Folder / Replace | Excel Programming |