Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello again,

    I have an additional question: as You all can see in the attached workbook as an add-on I should be able to set up the import
    process with two cases

    1. normal case (field could be blank or as a flag - "A") - all files in the folder have to be imported
    2. exception (flag "B" has to be set) - only the files with a flag x in column E of the parameter sheet have to be imported.

    well, i only came to this point to know what has to happen now. I tried some things, but without coming to an end for some days.
    I only know, that I have two cases, but the second case seems to be tricky....
    I know, in the second case the progresscounter has to be set up as well, so I took the countif function for the basenumber of percentage progress,
    but thats the point I am here now....

    does anyone know how to go on with the second case to import only the files in the folder with a flag???

    Stef

    ps: I took an old module where i could change some or all filenames in a folder
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Currently, you're looping through the files in the folder:

    Code:
      strFile = Dir(strPath & "*.xls")
      Do While Not strFile = ""
    	...
    	strFile = Dir
      Loop
    Instead of that, you'll have to loop through the list of file names in the Parameter sheet:

    Code:
      Dim wks As Worksheet
      Dim r As Long
      Set wks = Worksheets("Parameter")
      r = 15
      strFile = wks.Range("C" & r)
      Do While Not strFile = ""
    	If wks.Range("E" & r) = "x" Then
    	  ...
    	End If
    	r = r + 1
    	strFile = wks.Range("C" & r)
      Loop

  3. #3
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Hans,

    first of all Thank You for Your support.
    Maybe I misunderstand Your intention but I tried to put Your Code into the workbook, but iI guess
    it now doesn´t work any more...
    see attached wbk..

    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    In what sense doesn't it work?

    Do you get an error message? If so, what does it say?
    Do you get a different result than intended? If so, in what way?

  5. #5
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts
    well, i divide the code into the two cases - > A to import everything and B to do the second case, as You explained.
    I tried to mix the code like in the attached workbook but nothing happens, if i start teh macro.....
    in any case

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    There are several errors:

    1. Both occurrences of Cells(10.4) should be Cells(10, 4)

    2. The line

    If ThisWorkbook.Worksheets("parameter").Cells(10.4) = "B" Then

    should be

    ElseIf ThisWorkbook.Worksheets("parameter").Cells(10, 4) = "B" Then

    3. The last

    End If

    in the AddDataToWKS procedure should be removed (because of the Else If)

    4. You should calculate the file count differently in case "B".

  7. #7
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cool

    Hi Hans,

    You are right, as all the times You replied to me.
    The only thing i did not have to change was the second calculation for the progressbar, as i did through one cell in the
    parameter sheet (cell(9, 4)) -> for dblStep2. I can substitute it with the countif function, but it still works fine right now.
    Still maybe a problem was that the setting of the manual calculation for functions too. Have a nice weekend with a pint of Guiness!

    Chapeau,
    Stefan


    ps: working solution in the attached workbook.
    Attached Files Attached Files

Posting Permissions

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