code to make a list, anyone see what is wrong?

am6019a · July 12th, 2006, 10:53 AM

I am trying to use data from one workbook to make a list in another workbook. Basically if the file "c:\filepath\DataFromFirstWorkBook.xls" exists, the it should past info into the second workbook. Here is the code

Code:

Sub creatingalist()
    Dim QuoteID As Variant
    Dim i As Integer
    i = 1
    With Application.FileSearch
    Do Until 1 = 1231
    QuoteID = Workbooks("WorkBook1.xls").Worksheets("Fid Data").Cells(i, 9).Value
    .LookIn = Dir("c:\FilePath" & QuoteID)
    If .Execute > 0 Then Workbooks("WorkBook!.xls").Worksheets("Fid Data").Cells(i, 9).Copy Destination:=Workbooks("Fiducuary Submissions.xls").Worksheets("Fid Data").Cells(i, 1)
    i = i + 1
    Loop
    End With

End Sub

BrianWren · July 12th, 2006, 02:03 PM

Indentation, formatting and line continuation sure would make it easier to see what is going on,
and to maintain what you have. For example:

Code:

Sub creatingalist()

    Dim QuoteID As Variant
    Dim i As Integer

    i = 1
    With Application.FileSearch
        Do Until 1 = 1231   ' I presume you mean i = 1231 rather than 1 = 1231 . . .
            QuoteID = Workbooks("WorkBook1.xls").Worksheets("Fid Data").Cells(i, 9).Value
            .LookIn = Dir("c:\FilePath" & QuoteID)
            If .Execute > 0 Then _
                Workbooks("WorkBook1.xls").Worksheets("Fid Data").Cells(i, 9).Copy _
                Destination:=Workbooks("Fiducuary Submissions.xls").Worksheets("Fid Data").Cells(i, 1)
            i = i + 1
        Loop
    End With

End Sub

Also, I think an easier looping construct would be:

Code:

Sub creatingalist()

    Dim QuoteID As Variant
    Dim i As Integer

    With Application.FileSearch
        For i = 1 To 1231
            QuoteID = Workbooks("WorkBook1.xls").Worksheets("Fid Data").Cells(i, 9).Value
            .LookIn = Dir("c:\FilePath" & QuoteID)
            If .Execute > 0 Then _
                Workbooks("WorkBook1.xls").Worksheets("Fid Data").Cells(i, 9).Copy _
                Destination:=Workbooks("Fiducuary Submissions.xls").Worksheets("Fid Data").Cells(i, 1)
        Next i
    End With

End Sub

Did you mean to have

Code:

    ...
    .LookIn = Dir("c:\FilePath\" & QuoteID)
    ...

instead of

Code:

...

Code:

    .LookIn = Dir("c:\FilePath" & QuoteID)
    ...?

What result is it that you are getting?
Does the code raise an error, or just run without generating results?

am6019a · July 13th, 2006, 09:12 AM

Brian,
Thanks for you help so far. Right now it isn't generating results.

am6019a · July 13th, 2006, 10:30 AM

Brian,
got it to work, thanks again