Annoying dialog box about links

[drdra]

Hello all,

I am trying to complete a custom reporting solution and need to open and extract data from multiple files in a folder. However, when the file is opened, I get a pesky dialog box about the file having links that cannot be updated. These links are leftover from the tool that generated the report.

My question is if there is any way to auto-ignore those pop-ups or if I am going to have to fix the code in the report generator that produces the individual reports? The pop-up says "This Workbook contains one or more links that cannot be updated" and then prompts for "Continue" or "Edit Links".

It would ruin my automation if I had to manually click each workbook as it opens to resolve this issue.

Thanks for any tips or suggestions.

Regards;

.drdra

 

[n0gel]

This bit of code will prevent many boxes from popping up. I believe it uses the default highlighted choice in the dialog box as its action.

Application.DisplayAlerts = False
and
Application.DisplayAlerts = True

I haven't tried to set up a situation like yours so I don't know if this will help or not.

 

[Firefly2012]

One of the arguments to the Workbooks.Open method is UpdateLinks - set this to False so the workbook opens without updating or True so it updates (I almost always use False):

Workbooks.Open Filename:="C:\Somefolder\Somefile.xls",UpdateLinks:=False

 

[drdra]

Sweet. Thank you. I didn't realize that option to the workbooks.open existed. I used the following to test with Excel 2007 on the PC:

Sub openOne()
Dim fN, fP As String
fP = GetDirectory() & "\"  'This is a function that prompts for a directory
fN = "testfile.xls"
Workbooks.Open FileName:=fP & fN, UpdateLinks:=False
End Sub

Now, my goal is to read all files in the chosen directory into an array and then run a loop to open each file and extract data from a table into a "roll-up" report. I am sure I will have more questions and I really appreciate this forum. I have learned many things from previous posts and those that contribute answers are to be commended for assisting with the community.

Thanks again;

.drdra

 

[drdra]

I thought I might share some of my code for others that might need to do a similar function. First, I used a function I found on this forum.
GetDirectory() can be found here:

http://www.mrexcel.com/archive/VBA/13508.html

. This provides a pop-up dialogue for the user to select a folder.

GetFileList() can be found here:

http://spreadsheetpage.com/index.php/tip/getting_a_list_of_file_names_using_vba/

. This will read each file in a directory and store the filenames in an array.

The code below allows the user to pick a folder followed by a loop that opens each file, displays the active sheet name and then closes the file. I plan on extracting the data I need from each file inside this loop and formatting it into a report that includes activity information from all my direct reports. I hope this helps someone else.

Sub buildIt()
    Dim p As String, x As Variant

    p = GetDirectory() & "\"  'this functionj prompts the user for the directory
    x = GetFileList(p)          'this function reads the files into an array
    Select Case IsArray(x)
        Case True 'files found
            MsgBox (" Found " & UBound(x) & " Files.  Click OK to Continue")
            Application.DisplayAlerts = False
            For i = LBound(x) To UBound(x)
                Workbooks.Open FileName:=p & x(i), UpdateLinks:=False
                sN = ActiveSheet.Name
                MsgBox ("The current sheet is named: " & sN)
                Workbooks(x(i)).Close SaveChanges = False
            Next i
            Application.DisplayAlerts = True
        Case False 'no files found
            MsgBox "No Files found"
    End Select
End Sub

During the workbooks.close command, I was getting another annoying pop-up about fidelity. so, I used the recommendation within this thread to turn them off. Thanks for the good tips for getting me beyond my problems.

.drdra