Solved

Create Folder

Posted on 2004-04-29
6
168 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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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…

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now