Re: $$Excel-Macros$$ Unexpected "Open File" Dialog

[ashish koul]

can you share the excel file


I am not sure whether this will help you or not ,can you run the below macro
once in ur workbook

Sub BreakLinks()
'Author   : Ken Puls (www.excelguru.ca)
'Macro Purpose: Break all links in the active workbook
Dim vLinks As Variant
Dim lLink As Long
' Define variable as an Excel link type.
vLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
If vLinks = vbNullString Then Exit Sub
' Break all links in the active workbook.
For lLink = LBound(vLinks) To UBound(vLinks)
ActiveWorkbook.BreakLink _
Name:=vLinks(lLink), _
Type:=xlLinkTypeExcelLinks
Next lLink
End Sub



On Wed, Jun 8, 2011 at 12:53 AM, Evan Smith  wrote:

> I have a macro that builds a dynamic list of names, then references values
> in another spreadsheet through VLOOKUP. I have a worksheet I use as a
> template that only has the VLOOKUP formulas in the first row. I use the
> AutoFill method to copy the formula to the rest of the lines depending on
> how many rows there are from a dynamic list. The VLOOKUP formula originally
> used a relative name for the table from the reference workbook
> "[RefData]Data1!A:B". This file was opened as part of the macro before the
> AutoFill occurred and all the VLOOKUP references happily populated
> themselves.
>
> Somewhere over time, something's changed that I can't find. Now every time
> the AutoFill gets executed, it prompts me for the file, even though it's
> open. After I choose the file, it goes on its merry way. Since I actually
> use 4 files for external lookup data, I now get prompted 4 times.  I have
> also tried prepending the complete file path to the workbook name, but still
> manage to invoke the "open file" dialog. Since this macro is run many times
> a day, it gets old really quickly.
>
>  I've asked all my local gurus, and searched this group, and can't seem to
> find anyone who's having this same issue. Anyone here ever seen this or know
> how to revert to the non-prompting behavior?
>
> Thanks in advance,
> Evan
>
> Platform = Windows XP + Excel 2003
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>



-- 
*Regards*
* *
*Ashish Koul*
*akoul*.*blogspot*.com 
http://akoul.posterous.com/
*akoul*.wordpress.com 
My Linkedin Profile 


P Before printing, think about the environment.

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel

$$Excel-Macros$$ Unexpected "Open File" Dialog

[Evan Smith]

I have a macro that builds a dynamic list of names, then references values 
in another spreadsheet through VLOOKUP. I have a worksheet I use as a 
template that only has the VLOOKUP formulas in the first row. I use the 
AutoFill method to copy the formula to the rest of the lines depending on 
how many rows there are from a dynamic list. The VLOOKUP formula originally 
used a relative name for the table from the reference workbook 
"[RefData]Data1!A:B". This file was opened as part of the macro before the 
AutoFill occurred and all the VLOOKUP references happily populated 
themselves. 

Somewhere over time, something's changed that I can't find. Now every time 
the AutoFill gets executed, it prompts me for the file, even though it's 
open. After I choose the file, it goes on its merry way. Since I actually 
use 4 files for external lookup data, I now get prompted 4 times.  I have 
also tried prepending the complete file path to the workbook name, but still 
manage to invoke the "open file" dialog. Since this macro is run many times 
a day, it gets old really quickly.

 I've asked all my local gurus, and searched this group, and can't seem to 
find anyone who's having this same issue. Anyone here ever seen this or know 
how to revert to the non-prompting behavior?

Thanks in advance,
Evan

Platform = Windows XP + Excel 2003

-- 
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links : 
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 
To post to this group, send email to excel-macros@googlegroups.com

<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel