Solved

Create Folder

Posted on 2004-04-29
6
170 Views
Last Modified: 2010-05-02
Hi All - I have a unique problem and was hoping someone could help.  I have an Excel spreadsheet that has about 600 rows, each row represents a folder that needs to be created on a shared drive.  I know that "MkDir "c:\test\"" will create a directory, but i'm not quite sure how to get it out of Excel and then into a VB application.

Example:
Excel
                COLUMN A
ROW 1      1.0 Project Management
ROW 2      1.1 CSOC
ROW 3      2.0 Project IMP Resources
ROW 4      2.1 Holiday Schedule
ROW 5      2.1.1 2003 Schedule
ROW 6      2.1.2 2004 Schedule

Should create the following:
C:\Folders\1.0 Project Managment
C:\Folders\1.0 Project Managment\1.1 CSOC
C:\Folders\2.0 Project IMP Resources
C:\Folders\2.0 Project IMP Resources\2.1 Holiday Schedule
C:\Folders\2.0 Project IMP Resources\2.1 Holiday Schedule\2.1.1 2003 Schdule
C:\Folders\2.0 Project IMP Resources\2.1 Holiday Schedule\2.1.2 2004 Schdule

Can anyone help?  Thanks
0
Comment
Question by:eciabattari
  • 3
  • 2
6 Comments
 
LVL 76

Expert Comment

by:David Lee
ID: 10954491
Here's one approach.

Private Sub ReadExcelFile()
    Dim adoConn As ADODB.Connection
    Dim adoRS As ADODB.Recordset
    Dim strPath As String
    Set adoConn = New ADODB.Connection
    adoConn.ConnectionString = "DSN=MyExcelFile"
    adoConn.CursorLocation = adUseClient
    adoConn.Open
    Set adoRS = New ADODB.Recordset
    Set adoRS = adoConn.Execute("SELECT * FROM MyTableName")
    While Not adoRS.EOF
        strPath = adoRS.Fields("F1") & "\" & adoRS.Fields("F2") & ...
        MkDir strPath
        adoRS.MoveNext
    Wend
    adoRS.Close
    adoConn.Close
    Set adoRS = Nothing
    Set adoConn = Nothing
End Sub

To use this example you'll need to do the following:

1.  Create a System DSN
2.  For the driver type choose "Microsoft Excel Driver (*.xls)"
3.  In the "Data Source Name" field enter a name of your choice.
4.  Click "Select Workbook" and navigate to your Excel file.
5.  Set the version field appropriately.
6.  Open the spreadsheet.  
7.  Insert a row above your data.  Give each column a unique name.
8.  Create a named range.  Select all the rows containing your data.  The named range will appear to ADO as a table.
9.  Edit the above code and make the following changes.
    A.  DSN=MyExcelFile  Change MyExcelFile to the name you used in step #3.
    B.  SELECT * FROM MyTableName  Change MyTableName to the name you gave the named range in step #8.
    C.  adoRS.Fields("F1")  Change F1, F2, etc. to the column titles you created in step #7.
    D.  strPath = adoRS.Fields("F1") & "\" & adoRS.Fields("F2") & ...  Finish the command by completing construction of the path.

That should do it.  This will only work if the component parts of the path are on the same row.  Your original question said they are but then the example you gave showed the components in column format not row format.  So I'm not sure which format they're actually in.

Let me know if you have questions or run into problems.

BlueDevilFan
0
 

Author Comment

by:eciabattari
ID: 10963783
OK, figured out how to do most of it but I'm still having 1 last problem.  As you can see below, the script opens up excel, counts the number of cells and then creates the folders; however, the folders are all being created within one single folder, no subfolders.  Any suggestions?

'--------------------------------------------------------------------------------
Private Sub cmdCreateFolders_Click()
   
    Dim xl As New Excel.Application
    Dim wb As Excel.Workbook
    Dim ws As Excel.Worksheet
   
    Dim r As Long
    Dim x As Variant
    Dim strSource As String
    Dim strCells As String
   
    Set wb = xl.Workbooks.Open(txtPath.Text)
    Set ws = wb.Worksheets("Sheet1")
   
    ' counts the number of cells within the workbook
    wb.Application.ActiveCell.SpecialCells(xlCellTypeLastCell).Select
    strCells = wb.Application.ActiveCell.Row
   
    'creates first level folder
    MkDir "C:\Folder_Creation"
   
    'loop that gets name & then creates folders
    For r = 1 To strCells
        x = ws.Cells(r, 1)
        MkDir "C:\Folder_Creation\" & x
    Next r

    'cleanup
    wb.Close SaveChanges:=False
    xl.Quit
   
    cmdCreateFolders.Visible = False
    cmdDone.Visible = True
   
    Set wb = Nothing
    Set xl = Nothing
   
End Sub
'--------------------------------------------------------------------------------
0
 

Author Comment

by:eciabattari
ID: 10964105
I have one idea but don't know if this will work?
1.  Read the number part of the cell (i.e. 1.0 Project Management will retrun 1.0 and store as variable "strCount).
2.  Create first folder
3.  Read next cell and return number (i.e. 1.1 is larger than 1.0, thus, put 1.1 within folder 1.0 - see if after the "." is larger than the first.  Otherwise, if you have 2.0, it's larger than 1.0 and should be it's own folder).

I know I should use a If else then statement but need some help.

Thanks
0
 
LVL 76

Expert Comment

by:David Lee
ID: 11001364
Sorry, I missed the notice of a new comment on this item.  That's why I haven't responded until now.

Try this.

----

Private Sub cmdCreateFolders_Click()
   
    Dim xl As New Excel.Application
    Dim wb As Excel.Workbook
    Dim ws As Excel.Worksheet
   
    Dim r As Long
    Dim x As Variant
    Dim strSource As String
    Dim strCells As String
    Dim strPath As String
   
    Set wb = xl.Workbooks.Open(txtPath.Text)
    Set ws = wb.Worksheets("Sheet1")
   
    ' counts the number of cells within the workbook
    wb.Application.ActiveCell.SpecialCells(xlCellTypeLastCell).Select
    strCells = wb.Application.ActiveCell.Row
   
    'creates first level folder
    strPath = "C:\Folder_Creation"
    MkDir strPath
   
    'loop that gets name & then creates folders
    For r = 1 To strCells
        x = ws.Cells(r, 1)
        strPath = strPath & "\" & x
        MkDir strPath
    Next r

    'cleanup
    wb.Close SaveChanges:=False
    xl.Quit
   
    cmdCreateFolders.Visible = False
    cmdDone.Visible = True
   
    Set wb = Nothing
    Set xl = Nothing
   
End Sub

0
 
LVL 76

Accepted Solution

by:
David Lee earned 500 total points
ID: 11263901
I'm the only expert who responded to this question and I believe I answered the question.  I can't be certain of that though since the author never responded to my last post.  If there's a lingering issue with the solution I provided, then I'm willing to work with the author to resolve it.  Otherwise, I think I did provided exactly what was asked for and should receive the points.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question