Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Sep 2010
    Location
    Cheltenham, Gloucestershire, UK
    Posts
    12
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Hi

    I have interited a workbook with a vb script which after downloading an FTP file which it writes to the various sheets in the workbook (this bit works a dream). It is then supposed to add a vlookup to each of the rows. Unfortuatnely it seems to add an extra bit every time - which gives me an #N/A result see below

    Should write in the cell for example:

    =VLOOKUP(B412,'C:\Documents and Settings\roberta.walker\Desktop\[FAN 13 Codes.xlsx]Sheet2'!$C:$E,2)

    What Turns up!

    =VLOOKUP(B412,'[C:\Documents and Settings\roberta.walker\Desktop\[FAN 13 Codes.xlsx]Sheet2]FAN 13 Codes.xlsx]Sheet2'!$C:$E,2)

    linkfile is referenced as shown

    Dim lfile As Workbook
    linkFile = "C:\Documents and Settings\roberta.walker\Desktop\FAN13 Codes.xlsx"
    Set lfile = Excel.Workbooks.Open(linkFile)

    The bit if the VB which produces the above result is shown below

    Formula = "=VLOOKUP(B" & LastRow & ",'[" & linkFile & "]Sheet2'!$C:$E,2)"

    Any assistance to stop the repeat of the FAN 13 Codes.xlsx would be greatly appreciated. Thanks

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    *Personally, I would use:
    Code:
    Dim lfile As Workbook
    *linkFile = "C:\Documents and Settings\roberta.walker\Desktop\FAN13 Codes.xlsx"
    Set lfile = Excel.Workbooks.Open(linkFile)
    then:

    Code:
    *Formula = "=VLOOKUP(B" & LastRow & ",'[" & lfile.name & "]Sheet2'!$C:$E,2)"
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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