Update links/save = false

[Andy]

Greetings.

I'm trying to put together an Excel workbook that takes data from
every workbook in a certain folder. So far I have the code below which
works perfectly.

However, I have been unable to find a way to automatically choose
"Don't Update" to the update links box, or "No" to the "Do you want to
save?" box.

I've found a few solutions but can't seem to slot them in with the
code properly. Any help would be appreciated.



Sub CopyRangeValues()
Application.DisplayAlerts = False
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim rnum As Long
Dim i As Long
Dim a As Long
Application.ScreenUpdating = False
With Application.FileSearch
.NewSearch
.LookIn = "G:\New Folder"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
Set basebook = ThisWorkbook
rnum = 2
For i = 1 To .FoundFiles.Count

Set mybook = Workbooks.Open(.FoundFiles(i))


Set sourceRange = mybook.Worksheets("Access
Data").Range("a2:k336")
a = sourceRange.Rows.Count
With sourceRange
Set destrange = basebook.Worksheets(1).Cells(rnum,
1). _

Resize(.Rows.Count, .Columns.Count)
End With
destrange.Value = sourceRange.Value
mybook.Close
rnum = i * a + 1
Next i
End If
End With
Application.ScreenUpdating = True
End Sub

 

[Barb Reinhardt]

Are you maybe looking for

mybook.Close SaveChanges: = FALSE

 

[Andy]

Barb,

That's perfect, thank you.

The major issue is the "Update Links" box though, any ideas on how to
make the code chose "Don't update" each time?

 

[Ron de Bruin]

See the tips on this page
http://www.rondebruin.nl/copy3.htm

Add UpdateLinks:=0

 

[Andy]

Ron,

I stumbled upon that link earlier and read through it, although I
couldn't find where to add the code for it to work.

Is there a way to easily slot this in?

 

[Andy]

Nevermind, I found it through VBA help - as suggested on your link.

Thanks for the help!

 

[Ron de Bruin]

Hi Andy

Use
mybook = Workbooks.Open(.FoundFiles(i), UpdateLinks:=0)