Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    copy cut and move folder (excel)

    I have a question what does option explicit () do if you dont add it to your macro?

    I have attached a macro which looks in a folder opens excel workbooks and finds a cell, if that cell has a certain value then it closes the workbook, copys the folder and pastes it to the target folder. I want to move it so that in the other folder there is no copies so I can figure out which folders are not matching the specified value I need. I have tried .move and .cut but it seems this is too simple to do what I want it to do. If any one has a suggestion

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

    Re: copy cut and move folder (excel)

    If you do not have it in a module, VBA doesn't check if you use variables in your code that you haven't declared; this can lead to unexpected errors. See for example <post#=314748>post 314748</post#> for a short explanation of what Option Explicit does and why it is useful.

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

    Re: copy cut and move folder (excel)

    Replace the line

    sfl.Copy strTarget

    with

    sfl.Move strTarget & sfl.Name

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copy cut and move folder (excel)

    First, "Option Explicit", with no () after it, will require that all variables used in a VBA procedure be defined with a DIM statement. If you don't have the "Option Explicit", then you can use variables without defining them. Those variables will be given the default definition of Variant. Having "Option Explicit" will make catching errors like the following easier:

    Dim strFileName As String
    Dim oWB As Workbook
    strFileName = "C:TestMyFile.xls"
    Set oWB = Workbooks.Open Filename:=strFiIeName

    If you have "Option Explicit", then the above will give an error hiliteing the last strFiIeName and saying that it must be defined. If you don't have the "Option Explicit", the code will execute, but it will not do what is expected. If you haven't figured it out yet, the last strFiIeName has a capital i where the as the 6th letter of strFiIeName.

    I am not 100% sure what you want that code to do from your description, but it looks like this would be closer to what you want:

    Option Explicit

    Sub Part1()
    Dim fso As Object
    Dim fld As Object
    Dim sfl As Object
    Dim fil As Object
    Dim strSource As String
    Dim strFile As String
    Dim strTarget As String
    Dim wbk As Workbook
    Dim wsh As Worksheet

    On Error GoTo ErrHandler
    Application.ScreenUpdating = False

    Set wsh = ThisWorkbook.Worksheets(1)
    'the main customer folder to look for NGC jobs
    strSource = wsh.Range("F1")
    If Not Right(strSource, 1) = "" Then strSource = strSource & ""
    'Where the copyed subfolders will be pasted
    strTarget = wsh.Range("F2")
    If Not Right(strTarget, 1) = "" Then strTarget = strTarget & ""
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fld = fso.GetFolder(strSource)
    For Each sfl In fld.SubFolders
    For Each fil In sfl.Files
    If Right(fil.Name, 4) = ".xls" Then
    Set wbk = Workbooks.Open(Filename:=strSource & sfl.Name & "" & fil.Name, AddToMRU:=False)
    Select Case wbk.Worksheets(1).Range("C14")
    'the job names which will determine if we should copy subfolder
    Case "High Rate Nitrogen", "High Rate Nitrogen Frac", "N2 High Rate Frac", _
    "N2 Energized High Rate Nitrogen", "Nitrogen Frac", "CBM High Rate Nitrogen", _
    "N2 Frac", "CBM"
    sfl.Copy strTarget
    Exit For
    End Select
    wbk.Close SaveChanges:=False
    End If
    Next fil
    Next sfl

    ExitHandler:
    Set wbk = Nothing
    Set fil = Nothing
    Set sfl = Nothing
    Set fld = Nothing
    Set fso = Nothing
    Set wsh = Nothing
    Application.ScreenUpdating = True
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub
    Legare Coleman

  5. #5
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copy cut and move folder (excel)

    Thanks, at this line I seem to be stoping the code when I run it, with a message code execution has been interupted, I click continue and it wants to do this for every workbook

    Select Case wbk.Worksheets(1).Range("C14")

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copy cut and move folder (excel)

    I think that is probably because you don't have a workbook assigned to wbk. Try changing:

    Set wbk = Workbooks.Open(Filename:=strSource & sfl.Name & "" & fil.Name, AddToMRU:=False)

    to

    Set wbk = Workbooks.Open(Filename:=strSource & sfl.Name, AddToMRU:=False)


    There are also some other changes in my first response that I think will get you closer to what you want.
    Legare Coleman

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copy cut and move folder (excel)

    Sorry, I misread that statement. I think that your original was OK.

    What error are you getting that stops the code at the Case statement?
    Legare Coleman

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copy cut and move folder (excel)

    Another possibility, try changing that statement to:

    Set wbk = Workbooks.Open(Filename:=fil.Path, AddToMRU:=False)
    Legare Coleman

  9. #9
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copy cut and move folder (excel)

    I dont know what I did but all the files are read only now? I change what you mentioned on the last post, it seems to not open the folder because they are read only. So I changed it back. It is quite odd though because when I run the code in microsoft visual basic editor, and use F8 to run the code there is no message

    "code execution has been interupted" error

    Legare and Hans I appreciate you help

  10. #10
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copy cut and move folder (excel)

    No it still bring up the error message, I dont understand why it works in the visual basic editor by running it by F8 but in the macro work book or running it automatically it brings up a error.

    What does AddToMRU:=False do?

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copy cut and move folder (excel)

    You have never really told us what error you are getting. Therefore, we are guessing at what the problem might be. First you said that the macro was stopping on the Select Case statement, but never told us what the error was (you might need to remove your On Error to see the message). So, I have been guessing at what might cause an error on the Select Case statement. Now in this message you are saying "Anyone know how to get rid of updata messages if the wookbook i am opening has links in it?". That looks like something completely different. I you are now asking how to eliminate the message asking if the links should be updated when you open the file, then the answer to that question is:

    Application.DisplayAlerts = False
    Set wbk = Workbooks.Open(Filename:=fil.Path, AddToMRU:=False)
    Application.DisplayAlerts = True

    However, I am getting very confused about exactly what you are asking.
    Legare Coleman

  12. #12
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copy cut and move folder (excel)

    I have figured out that macros written in 2000 seem to not work to great in 2003 enviroment. I change this part of the code

    Workbooks.Open Filename:=fil.Path, AddToMRU:=False
    Set wbk = Nothing
    Set wbk = ActiveWorkbook
    Select Case wbk.Worksheets(1).Range("C14")

    to break it up, and still I get the message. So I just moved to my old computer and it works fine.

    Anyone know how to get rid of update messages if the workbook i am opening has links in it?

  13. #13
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copy cut and move folder (excel)

    Sorry, yes, I have changed the topic. I fixed the spelling in my post

    The error message is not showing up. I beleive it has to do with excel 2003, because it works fine on excel 2000
    .

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copy cut and move folder (excel)

    Try commenting the line:

    On Error GoTo ErrHandler
    Legare Coleman

  15. #15
    2 Star Lounger
    Join Date
    May 2005
    Location
    Wilcox, Saskatchewan
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copy cut and move folder (excel)

    I noticed that you were wondering what MRU means

    well from my understanding it means MOST RECENTLY USED (MRU) Excel spreadsheets. This file list provides a quick way for you to access your files. Some people don't like the file list. This list can record the names of up to the last nine workbooks opened and edited within Excel. You can see the MRU list by looking at the bottom of the File menu

    If you wanna see this list do the following
    1. Choose Options from the Tools menu. This displays the Options dialog box.
    2. Make sure the General tab is selected. (Click here to see a related figure.)
    3. Make sure the Recently Used File List check box is selected.
    5. Click on OK.

    pc OUT
    sEANER

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
  •