Update links/save = false
 

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

Update links/save = false
 

Are you maybe looking for

mybook.Close SaveChanges: = FALSE

--
HTH,
Barb Reinhardt




"Andy" wrote:

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

Update links/save = false
 

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?

Update links/save = false
 

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

Add UpdateLinks:=0


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Andy" wrote in message ...
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?

Update links/save = false
 

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?

Update links/save = false
 

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

Thanks for the help!

Update links/save = false
 

Hi Andy

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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Andy" wrote in message ...
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?