Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Code that does folder file organizing and making (excel)

    hi again,

    I had developed some code and i almost have it doing what I want but need some help

    i have commented with ' what i am trying to do

    'vlocation = 1/2-3-4
    'Look at vlocation and if there is any "" replace them with " ", and
    'vlocation =1 2-3-4

    'if there is any "-" replace them with ""
    'vloaction =1 234
    '
    'look to see if the directory
    'if
    ' ChDir ("G:Customer 2006" & vcShort & "" & vLocation & "" exists
    ' if not then make it
    '
    'Then
    ' ChDir ("G:Customer 2006" & vcShort & "")
    '
    ' MkDir (vLocation)
    '
    'end if

    attached is the whole code piece

  2. #2
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Code that does folder file organizing and making (excel)

    For checking to see the directory i have tried this:

    If Dir("G:Customer 2006" & vcShort & "" & vLocation & "", vbDirectory) = "" Then

    ChDir ("G:Customer 2006" & vcShort & "")
    MkDir (vLocation)

    Else

    ChDir ("G:Customer 2006" & vcShort & "" & vLocation & "")

    End If

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code that does folder file organizing and making (excel)

    Try
    <code>
    vLocation = Replace(vLocation, "/", " ")
    vLocation = Replace(vLocation, "-", "")
    </code>
    This replaces "/" with a space " " and removes "-" (replace with a blank string "")

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code that does folder file organizing and making (excel)

    Sorry, you've lost me. Can you try to explain in simple terms what you want?

  5. #5
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Code that does folder file organizing and making (excel)

    Thanks Hans, I never knew that it was that easy,
    i have another question though:

    at this part of the code

    MkDir (vServiceOrderNumber)

    ChDir ("G:SalesCustomer 2006" & vcShort & "" & vLocation & "" & vServiceOrderNumber)

    instead of
    ChDir ("G:SalesCustomer 2006" & vcShort & "" & vLocation & "" & vServiceOrderNumber)
    is it possible to just open this directory aswell, so if i have nothing open but excel and run the code at the end i will have a open folder declared above??

  6. #6
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Code that does folder file organizing and making (excel)

    Sure, instead of changing to
    ChDir ("G:SalesCustomer 2006" & vcShort & "" & vLocation & "" & vServiceOrderNumber)

    Id like to open the folder with a vbcommand, which would fill the shoes of going through the directory manually and finding the created folder and opening it

    hope thats better?

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Code that does folder file organizing and making (excel)

    <P ID="edit" class=small>(Edited by sdckapr on 10-Jan-06 18:11. added PS)</P>Why not something like this:

    on error resume next
    mkdir ("G:Customer 2006" & vcShort & "" & vLocation)
    mkdir ("G:Customer 2006" & vcShort & "" & vLocation & "" & vServiceOrderNumber)
    on error goto 0
    ChDir ("G:SalesCustomer 2006" & vcShort & "" & vLocation & "" & vServiceOrderNumber)

    It will make them if they do not exist, if they do it will cause an error which the on error resume next will ignore

    Steve

    PS. This assumes that "G:Customer 2006" & vcShort" already exists. If it may not, you should create these folders also...

  8. #8
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Code that does folder file organizing and making (excel)

    Thanks Steve,

    Do you know how i can open a folder hidden in the directory path G:Customer 2006" & vcshort & "" & vLocation & "" & vServiceOrderNumber opening the serviceorderNumber folder?

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Code that does folder file organizing and making (excel)

    I am a little confused. What do you mean by "open a folder"?

    Do you want to "Unhide" the folder?

    <pre> Dim FSO, Folder
    Dim s As String
    s = "G:Customer 2006" & vcshort & "" & vLocation & "" & vServiceOrderNumber
    Set FSO = CreateObject("Scripting.FileSystemObject")
    With FSO.GetFolder(s)
    .Attributes = .Attributes Xor 2
    End With</pre>


    [Note:
    <pre> .Attributes = .Attributes Or 2</pre>

    will HIDE the folder]

    Steve

  10. #10
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Code that does folder file organizing and making (excel)

    what I am trying to do is open up a folder in my g: directory so basically if i did it manually i would go
    1. My computer
    2. g: drive
    3.to the file path folder1folder2folder3

    folder3 is open and showing its contents on my desktop

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Code that does folder file organizing and making (excel)

    <pre> Dim a
    Dim s As String
    s = "G:Customer 2006" & vcshort & "" & vLocation & "" & vServiceOrderNumber
    a = Shell("C:WINDOWSEXPLORER.EXE " & Chr(34) & s & chr(34), vbNormalFocus)</pre>


    Change the path to explorer as desired

    Steve

  12. #12
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Code that does folder file organizing and making (excel)

    a = Shell("C:WINDOWSEXPLORER.EXE " & Chr(34) & s & chr(34), vbNormalFocus)

    What does this do??

    thanks

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code that does folder file organizing and making (excel)

    It opens the folder whose path is in the variable s in Windows Explorer - that's what you wanted, isn't it?

  14. #14
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Code that does folder file organizing and making (excel)

    yes that is what i wanted, I am just trying to figure out why i am getting a run time error,
    to run this function do i need to set anything in tools references??

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code that does folder file organizing and making (excel)

    No. What is the error you get?

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •