Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts

    Lightbulb Compile error Expected Array

    Option Explicit
    HI All,

    What iam doing wrong compile error expected array?

    Sub test()
    Dim dir As String

    If dir("D:\path\test.xls") <> "" Or dir("D:\path\test.xlsx") Then
    'ActiveWorkbook.ChangeLink
    ActiveWorkbook.ChangeLink "D:\path\test.xlsx", "D:\path\test.xls", xlExcelLinks
    Else
    Return
    End If

    End Sub

    Thanks
    farrukh

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Code:
    Sub test()
    Dim dir As String
    
    If dir("D:\path\test.xls") <> "" Or dir("D:\path\test.xlsx") <> "" Then
    'ActiveWorkbook.ChangeLink
    ActiveWorkbook.ChangeLink "D:\path\test.xlsx", "D:\path\test.xls", xlExcelLinks
    End If
    
    End Sub
    One possible problem is the missing test {shown in red} on the second Dir command. The other possible problem is the OR test. Can you tell us in words what you are trying to test and the actions to be taken?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    farrukh (2011-06-22)

  4. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You created a variable named dir which is also a built-in function name. Since you created the variable, when you tried to use it as a function in the code, VB thinks you mean the variable dir and not the funtion.

    It is never a good idea to have a variable as a keyword. It is prone to some confusion somewhere. You don't seem to use the variable dir so either delete the dim line, or if in your full code you use it, call it something like sDir instead of dir

    Steve

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Steve,

    Can I borrow your eyes? These old peepers don't work so well any more. I should have seen that...good catch!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I don't think you want my 53 yr old eyes. I had cataract surgery in both eyes a few years ago, and after a retinal detachment in my right eye a couple years ago, it has never been the same...

    Smile

  7. The Following User Says Thank You to sdckapr For This Useful Post:

    farrukh (2011-06-22)

  8. #6
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts
    Dear Experts.

    What i am trying to do if in folder the file test.xls or test.xlsx then change link in master file.?
    i want to call it in workbook in masterfile when the masterfile open it change the links?
    thanks
    farrukh

  9. #7
    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
    Steve has told you how to fix it - remove the 'Dim Dir as string' line.
    Regards,
    Rory

    Microsoft MVP - Excel

  10. The Following User Says Thank You to rory For This Useful Post:

    farrukh (2011-06-22)

  11. #8
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts
    Dear Steve and rory retiredgeek,
    Thank you so much for your kind help and attitude thank you both

    Regards
    farrukh
    Last edited by farrukh; 2011-06-22 at 15:11.

Posting Permissions

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