none
Type Mismatch With My Amateur Effort to Write a Loop RRS feed

  • Question

  • I'm trying to develop a loop that will open a sequence of .csv files, from each of which I'll then extract data to copy to the worksheet. The names of the .csv files are being taken from a range on the active sheet.

    As the first step I can open a single .csv file OK by using:

    Sub UpdateData()
    
        Dim rRng as Range
        Dim Path as String
        Dim Cell as Range
    
        Set rRng = Sheet1.Range("C8")
        Path = "F:\DataFiles\Current" & rRng & ".csv"
    
        Workbooks.Open Path
    
    End Sub
    However when I tried to create a loop I get an Error pop-up - Type Mismatch.:
    Sub UpdateData()
    
        Dim rRng as Range
        Dim Path as String
        Dim Cell as Range
    
        Set rRng = Sheet1.Range("C8:C10")
        Path = "F:\DataFiles\Current" & rRng & ".csv"
    
        For each Cell in rRng
             Workbooks.Open Path
        Next Cell
    
    End Sub

    I thought that Cell may have been the problem but renaming it to rCell made no difference.  What have I done wrong with my simple loop, and how should I have written it?

    Saturday, October 6, 2018 8:07 AM

Answers

  • Hi DaviDWF,

    Please try the following code:

    Sub UpdateData()
    
        Dim rRng As Range
        Dim Path As String
        Dim Cell As Range
    
        Set rRng = Sheet1.Range("C8:C10")
    
        For Each Cell In rRng
             Workbooks.Open "F:\DataFiles\Current\" & Cell & ".csv"
        Next Cell
    
    End Sub

    Hopefully it helps you.

    Best Regards,

    Bruce


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.


    Monday, October 8, 2018 10:58 AM
    Moderator

All replies

  • Sorry - a correction to the Path. That should have read:

    Path = "F:\DataFiles\Current\" & rRng & ".csv"

    Saturday, October 6, 2018 8:09 AM
  • Hi DaviDWF,

    Please try the following code:

    Sub UpdateData()
    
        Dim rRng As Range
        Dim Path As String
        Dim Cell As Range
    
        Set rRng = Sheet1.Range("C8:C10")
    
        For Each Cell In rRng
             Workbooks.Open "F:\DataFiles\Current\" & Cell & ".csv"
        Next Cell
    
    End Sub

    Hopefully it helps you.

    Best Regards,

    Bruce


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.


    Monday, October 8, 2018 10:58 AM
    Moderator
  • Thanks Bruce - that solved it perfectly.

    Now I'll move on to the next step of the process and see how far I can get.

    Much appreciated.

    Monday, October 8, 2018 11:41 AM